[SOLUCIONADO] Actualización entre dos tablas grandes.

[ljbautista]

Buenas tardes compañeros, pues vengo buscando su consejo y su experiencia.

Tengo un cliente que tiene un sistema php al que le he hecho modificaciones, y en su BD de MySQL tiene dos tablas : Una temporal donde suben registros de un archivo de excel y otra tabla principal.

La tabla principal tiene aprox 150,000 regs. y la temporal llega a a tener aprox unos 12,000 regs.

Hay un script en PHP, donde ejecutan un proceso de actualización:

• Desde el cliente hacen el submit a un script php
• Se toma cada registro de la temporal y busca si ya existe en la principal
• Si ya existe actualiza ciertos campos
• Si no existe el registro entonces lo inserta como nuevo
• Después borra el reg de la temporal

Como pueden ver me refiero a que si la temporal tiene 12 Mil registros, cada uno lo busca en los 150 mil de la principal, y ejecuta un update o un insert y después un delete.

No se si mis cuentas estén bien pero estoy diciendo que desde el navegador con el srcipt php hace aprox 3 querys (select,update/insert y delete) x 12,000 regs, algo asi como unos 36,000 querys desde el script php.

Cuando se ejecuta el script de actualizacion el sistema php se atura y los demás usuarios del sistema no pueden trabajar durante unos 45 min... durante este tiempo ni siquiera se esta actualizando nada en la bd. Pasado este tiempo el sistema de destraba y se puede volver a trabajar y hasta entonces es cuando en realidad la BD se esta actualizando (refrescando con phpmyadmin veo como va disminuyendo la tabla temporal)

Supongo que esta no ha de ser la manera ideal de actualizar entre dos tablas, me refiero a que es mucha carga para el cliente ejecutar este proceso.


En su experiencia que es lo que me recomendarían para hacer este proceso?

[Libras]

Clasico error cuando un desarrollador quiere hacer ese tipo de operaciones, pensarlo linea por linea, cuando es mas facil hacerlo todo con un dataset

[gnzsoloyo]

En su experiencia que es lo que me recomendarían para hacer este proceso?

Lo que cualquier DBA sensato haría, y que no entiendo por qué no lo hicieron desde el principio:
1) Implementar toda esa lógica dentro de un SP, ya que no tiene ningún sentido hacerlo en la aplicación, en especial si la única intervención del usuario es pulsar un botón en el formulario...
2) Automatizar la ejecución del SP por medio de un EVENT, programado para correr durante la noche, horario donde usualmente el uso de la base desciende.

[ljbautista]

Gracias gnzsoloyo y Libras, ese proceso no lo hice yo y aun así me da pena ajena como programador :P

No se mucho acerca de SP (supongo son los procedimientos almacenados) pero entonces esa si seria una solucion supongo.

Que son los dataset?

[gnzsoloyo]

Que son los dataset?

Algo que no pertenece a MySQL... Es programación.

Los dataset son técnicamente set de datos en memoria. Relacionados con la base corrresponden a las estructuras donde se almacenan las tablas resultado de una consulta.

[Libras]

Iniciado por gnzsoloyo

Algo que no pertenece a MySQL... Es programación.

Los dataset son técnicamente set de datos en memoria. Relacionados con la base corrresponden a las estructuras donde se almacenan las tablas resultado de una consulta.

Tienes razon mi estimado, se me fue el termino, un dataset seria tomar los datos de tus tablas y compararlos usando joins, y este resultado hacerle update a lo q existe o insert a lo nuevo, pero a todos los datos no renglon por renglon, SP(stored procedure)

[ljbautista]

Ok perfecto al menos ya tengo hacia donde dirigir mis esfuerzos.

Estaba probando algo más enredado: crear un cron job que estuviera revisando cada determinado tiempo si hubiera procesos por actualizar, y cuando lo hubiera que hiciera las actualizaciones entre las tablas con joins pero igual sería desde un script php, según yo ya no se atoraria el sistema porque la petición ya no la haría el cliente sino que se ejecutaría en el mismo servidor... Aun e no se si esto atoraria de todas maneras el sistema y si el update con joins se más eficiente.

Pero ya me pongo a buscar como programar sp.

[ljbautista]

Hola compañeros pues ahi la levo con el SP, ya pude hacer uno que me haga las operaciones que necesito, me atore un poco con el asunto de las versiones de mysql :S pero ya pude hacer algo basico como esto:

Código:

DROP PROCEDURE IF EXISTS actualizacion;
DELIMITER //
CREATE PROCEDURE actualizacion()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE vCta CHAR(10);
    DECLARE vSALDO INT;
    DECLARE cur1 CURSOR FOR SELECT cuenta, saldo FROM rcc_db.tmp1;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    UPDATE rcc_db.tmp2 P INNER JOIN rcc_db.tmp1 T ON T.cuenta=P.cuenta SET P.saldo= T.saldo;
    
    DELETE temp FROM rcc_db.tmp1 temp INNER JOIN  rcc_db.tmp2 prin ON temp.cuenta=prin.cuenta;
    
    OPEN cur1;
	read_loop: LOOP
	  FETCH cur1 INTO vCta, vSaldo;
	  IF done THEN
	    LEAVE read_loop;
	  END IF;
      
	  INSERT INTO rcc_db.tmp2 (cuenta,saldo) VALUES (vCta,vSaldo);
	END LOOP;
    CLOSE CUR1;

END //

CALL actualizacion();

Ahora mi duda es, si hay alguna forma de saber y regresar los registros afectados por los querys???

Gracias de antemano, compañeros?

[ljbautista]

Hola compañeros pues voy a dar por solucionado el tema no sin antes dar gracias a gnzsoloyo y Libras por los consejos, pues hice algunas cuestiones como depurar la BD, agregar indices y finalmente mi sp quedo:

Código:

USE db;
DROP PROCEDURE IF EXISTS actualizacion;
DELIMITER //
CREATE PROCEDURE actualizacion()
BEGIN
    /* Actualziacion entre las tablas */
    UPDATE db.tabla1 t1 INNER JOIN db.tabla2 t2 ON t1.cuenta1=t2.cuenta SET t1.saldo = t2.saldo;
	
    /* Eliminamos de la tabla temporal los registros que ya se actualizaron*/
    DELETE t2 FROM db.tabla2 t2 INNER JOIN  db.tabla1 t1 ON t1.cuenta=t2.cuenta;

	/* En este punto en la TEMP solo quedaron los registros que no se encontraron en la tabla principal*/
    INSERT INTO db.tabla1(cuenta,saldo) SELECT cuenta,saldo FROM db.tabla2; 
	
    /* Eliminamos el resto de la tabla */
    DELETE t2 FROM db.tabla2 t2 INNER JOIN  db.tabla1 t1 ON t1.cuenta=t2.cuenta;

END //

CALL actualizacion();

Aun así al ser muchos registros en ambas tablas tarda pero bueno al menos se que ya no estoy haciendo una mala praxis al hacerlo desde el cliente :)