Error al ejecutar muchas consultas de forma remota

[CBBzun]

Saludos.

El problema es este: el servidor está en un lugar remoto, y desde la aplicación un usuario selecciona un archivo de Excel el cual contiene información de depósitos bancarios, yo leo cada línea de excel, la cual me da monto, identificacion, fecha, etc.. busco la identificacion en la tabla de Deudores y si lo encuentra Inserta el registro, y elimina de la tabla de Gestiones la gestión del mes, si no busca en otra tabla de Personas e indica el nombre sin Insertar.

En modo local funciona muy bien y muy rápido, por cada línea de Excel hago un START TRANSACTION y al final un COMMIT.

Pero en modo remoto se hace un poco más lento, más o menos demora 3 o 4 veces más.. y siempre se cae entre la línea 600 y la 1200.. de un total de 10mil (del archivo de Excel), o sea, casi empezando.. y da el error: [MySQL server has gone away]

Mientras no se haga la migración de datos, la aplicación funciona bien durante todo el día, para todas las consultas que se tenga que hacer.. pero si están trabajando de forma remota no pueden migrar. La solución no es hacerlo en local, es forzoso hacerlo vía remota.

Asumo que puede ser cuestión de algún parámetro del MySQL Server.. ahorita están con la versión 4.1 no se si cuando los migre a las 5.1 trabaje mejor, pero no es tan simple porque debo visitar un montón de equipos para actualizarles el ODBC.

Muchas Gracias!!

[huesos52]

CBBzun..

cuentanos un poco mas como lees el excel. Que lenguaje de programación utilizas?
Nada mas ayer tuve un problema similar leyendo una hoja de excel, insertaba la linea cada que la leía. para pocos registros funcionaba de pelos, pero cuando lo probé con un archivo de 4500 registros se me colgó de inmediato. El problema de rendimiento se debe basicamente a que la aplicación realiza 4500 viajes de la aplicación a la base de datos. Mi solución fue enviar en una sola conexión todo el conjunto de datos y tratarlo con una función desde la base de dats que me gestionara las inserciones. (Lo hizo en 3 segundos)

Si nos das mas detalles del procedimiento que utilizas para lectura del archivo, de la forma como lo ingresas en la base de datos te podemos sugerir una opción mas rapida. Si la solución es de base de datos, aca te ayudamos, si es de aplicación le pedimos a un moderador que mueva el tema a la sección correspondiente.

saludos

[CBBzun]

Gracias Huesos por tu respuesta tan pronta!

La aplicación está en VB6, así cargo el Excel

Código:

Set conexcel = New ADODB.Connection
conexcel.CursorLocation = adUseServer
conexcel.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=" & cd1.FileName & ";" & _
                "Extended Properties=" & Chr(34) & "Excel 8.0;HDR=Yes;IMEX=1" & Chr(34)
...
xls.Open "SELECT * FROM [retencion$] ORDER BY fecha DESC", conexcel
...
tmp.Open "SELECT t1.id, ... WHERE t1.expediente = '" & xls!expediente & "' LIMIT 1", con1
If tmp.RecordCount > 0 Then
...  aqui puede haber un INSERT, un DELETE o un UPDATE
end if

Ahí me voy de a uno por uno.. con forme leo consulto y/o inserto o borro, voy enviando a un Grid los resultados.. el operador los va viendo y al final toma decisiones manuales, como indentificar un depósito de otra persona y desviarlo a quien de verdad corresponde.

La lectura del Excel no me da problemas, incluso en la red local (no en el servidor) he trabajado con archivos de 20mil registros, y sin problemas.. todo viene al tratar de hacerlo vía remota desde una conexión a internet.

Así como dices la aplicación hace miles de consultas y yo no se si eso genere algún motivo para que MySQL te corte la conexion.. si se puede especificar algo a nivel de variables del my.cfg

[huesos52]

Las herramientas que usas están bastante viejas para poder hacer algo mas optimo.
se me ocurre que almacenes en un string las secuencias de inserts.
así

Código SQL:

INSERT INTO tabla VALUES(1,2,3),(4,5,6),(7,8,9);
 

De tal forma que solo envies al servidor dicha variable a ejecutar. El start transaction y el commit solo se realizarían una vez.

Espero te sirva.

[CBBzun]

Muchas gracias por tu idea, eso resuelve el problema para el INSERT, pero el archivo que estaba cargando tiene las primeras 5mil líneas migradas.. me dirás que las excluya, yo lo haría, pero la empresa necesita ver todos los datos para según ellos tomar decisiones.

Y en este momento se me pega con puros SELECT (sin llegar a los Insert, updates, etc).. me toca mínimo hacer 2 select por línea de Excel, una verifica si es deudor y si no revisa si está en personas.. hace un rato corrí la aplicación quitando el START y el COMMIT .. iba muchísimo más rápido, pero falló en el 676.. mismo error.

que se te ocurre??.. nada en variables del MySQL Server?.. algún bloqueo de seguridad.. no se..

[huesos52]

Mirate esto
http://dev.mysql.com/doc/refman/5.0/...variables.html

En especial la variable query_cache_size y empieza a hacer pruebas para ver si mejora tu rendimiento. Te pasé el link de la versión 5.0, mira el mismo apartado para la versión 4.0 y mira cuales son soportadas.

[gildus]

Holas,

Has intentado en paginar o hacer una paginacion del listado que haces desde excel?, esto con la macro claro.

Saludos
Gildus

[CBBzun]

no entiendo.. eso como se haría y en que beneficiaría?

[CBBzun]

Bueno.. al parecer ya no me está dando error, hice varias cosas:

Read Timeout = 60 << estaba en 30
Retry Count = 20 << estaba en 10
Max Conn Errors = 20 << estaba en 10
Max Packet Size = 128M << estaba en 16M
Log Warnings = ON << Para tener más información sobre las fallas

Por otro lado optimicé la consulta,

Yo estaba iniciando la Transacción al inicio del Bucle y al final hacía un Commit, cuando en la mayoría de los casos solo hacía un par de SELECT's que me indicaban que no era necesario hacer algún INSERT / UPDATE / DELETE... De esta forma inicio la transacción solo si tengo que alterar las tablas, si no continúa.. y eso me economiza 2 consultas al servidor por línea de Excel.. y casi triplicó la velocidad. También indexé un campo importante en las búsquedas que no tenía indexado.

Ahí queda la información por si alguien tiene una situación similar.. muchas gracias por las colaboraciones!