Un Store P es más optimo en este caso?

[neodani]

Buenas,

Estoy leyendo un fichero XML con datos, este fichero XML es bastante grande y si los datos que estoy leyendo no están ya en
la base de datos los inserto. A lo mejor inserto todos los registros si son nuevos. Bueno ya os aseguro que cada día hay nuevos registros, se van renovando.
Estoy hablando de más de 100.000 inserts que realizo uno a uno en un bucle que lee el XML de inicio a fin.

Me pregunto si sería más optimo... en lugar de lanzar este insert, lanzase un Store Procedured que ejecutará el insert.

¿Saldría ganando? vale la pena, o para este caso al ser una sola consulta no notaría diferencia?

Muchas gracias de antemano

[gnzsoloyo]

100.000 registros es tema de cargas masivas, y las cargas masivas se realizan en MySQL con LOAD DATA INFILE...
El problema es que LOAD DATA INFILE no se puede usar en los STORED PROCEDUREs (además MySQL que yo haya visto no puede tomar datos directamente de una fuente XML, pero puede que me equivoque).

Tu solución es programática. Debes hacer una rutina de carga tal que pueda crear un script SQL con los inserts múltiples (multiples grupos de VALUE por cada INSERT), o bien uno que genere un archivo que pueda usar el LOAD DATA.

[neodani]

Iniciado por gnzsoloyo

100.000 registros es tema de cargas masivas, y las cargas masivas se realizan en MySQL con LOAD DATA INFILE...
El problema es que LOAD DATA INFILE no se puede usar en los STORED PROCEDUREs (además MySQL que yo haya visto no puede tomar datos directamente de una fuente XML, pero puede que me equivoque).

Tu solución es programática. Debes hacer una rutina de carga tal que pueda crear un script SQL con los inserts múltiples (multiples grupos de VALUE por cada INSERT), o bien uno que genere un archivo que pueda usar el LOAD DATA.

Si te he entendido bien, crees que sería más optimo en lugar de leer el XML y realizar los inserts o updates en la base de datos, leer el xml y transformarlo en un archivo capaz de hacer el load data en la base de datos?

El load data solo funcionaria con los inserts, para los updates no hay nada que hacer no?

Tampoco entiendo a lo que te refieres con multiples inserts... yo hago un insert por cada dato válido del XML, esto está metido dentro de un bucle.

Muchas gracias de antemano!

[gnzsoloyo]

Si te he entendido bien, crees que sería más optimo en lugar de leer el XML y realizar los inserts o updates en la base de datos, leer el xml y transformarlo en un archivo capaz de hacer el load data en la base de datos?

Mas o menos. La idea es leer el XML, crear con él un script con INSERTs masivos, y luego gestionar con otro los UPDATE.
Por INSERT masivo se entiende esto:

Código MySQL:

INSERT INTO tabla(campo1, campo2, campo3, ... campoN)
VALUES(valor1, campo2, valor3, ... valorN), (valor1, campo2, valor3, ... valorN), (valor1, campo2, valor3, ... valorN), (valor1, campo2, valor3, ... valorN), (valor1, campo2, valor3, ... valorN), (valor1, campo2, valor3, ... valorN)...
 

De este modo se crea un INSERT de múltiples registros (yo tengo algunos que meten 1.000 por bloque), que se envían al buffer rápidamente y luego se insertan rápidamente.

El caso de los updates es mas complejo porque cada registro debe ser enviado específicamente si los datos a actualizar son algunos y no todos. En ese caso es mejor crear un SP que se invoque desde la aplicación con la lectura del XML. De ese modo se pueden realizar en el SP todas las comprobaciones necesarias para poner o no el dato en cada campo.

Es una forma de verlo.

[neodani]

Iniciado por gnzsoloyo

Mas o menos. La idea es leer el XML, crear con él un script con INSERTs masivos, y luego gestionar con otro los UPDATE.
Por INSERT masivo se entiende esto:

Código MySQL:

INSERT INTO tabla(campo1, campo2, campo3, ... campoN)
VALUES(valor1, campo2, valor3, ... valorN), (valor1, campo2, valor3, ... valorN), (valor1, campo2, valor3, ... valorN), (valor1, campo2, valor3, ... valorN), (valor1, campo2, valor3, ... valorN), (valor1, campo2, valor3, ... valorN)...
 

De este modo se crea un INSERT de múltiples registros (yo tengo algunos que meten 1.000 por bloque), que se envían al buffer rápidamente y luego se insertan rápidamente.

El caso de los updates es mas complejo porque cada registro debe ser enviado específicamente si los datos a actualizar son algunos y no todos. En ese caso es mejor crear un SP que se invoque desde la aplicación con la lectura del XML. De ese modo se pueden realizar en el SP todas las comprobaciones necesarias para poner o no el dato en cada campo.

Es una forma de verlo.

Hola gnzsoloyo,

De la forma que dices con el insert masivo, no se usaría el load data? o después de crear ese fichero de inserts masivos después tendría que hacer el

LOAD DATA INFILE 'insertsmasivos.txt' INTO TABLE db2.my_table;

También me llama la curiosidad, lo que comentas que tienes algunos que insertan 1.000 por bloque, te refieres 1.000 registros en el fichero? es perjudicial hacer 100.000 en lugar de 1.000 en 1.000? hay que dividirlos en bloques?

Y cuando dices que es complejo los updates, comentarte que no cambian todos los datos, cada registro conserva sus datos a excepción de dos, que son el "precio" que es lo que puede ir cambiando en cada lectura del XML. Los updates son en campos fijos. En ese caso si que se podría usar el SP de manera sencilla?

Crees posible la utilización de algun tipo de caché? o la caché solo afecta a los selects no a los inserts ni updates, y al estar todo el rato insertando y updatando la tabla la caché no haría nada, correcto?

Muchas gracias de antemano!

[gnzsoloyo]

También me llama la curiosidad, lo que comentas que tienes algunos que insertan 1.000 por bloque, te refieres 1.000 registros en el fichero? es perjudicial hacer 100.000 en lugar de 1.000 en 1.000? hay que dividirlos en bloques?

Es una cuestión eminentemente práctica:
100.000 registros de 300 bytes por registro, por ejemplo, requiere enviar al buffer 30 Mb de un saque... lo que te puede poner en problemas si el buffer no tiene la capaciad necesaria. Te verías obligado a reconfigurarlo, y si es un servidor tercerizado, tu mismo no puedes hacerlo.

Y cuando dices que es complejo los updates, comentarte que no cambian todos los datos, cada registro conserva sus datos a excepción de dos, que son el "precio" que es lo que puede ir cambiando en cada lectura del XML. Los updates son en campos fijos. En ese caso si que se podría usar el SP de manera sencilla?

Si sólo se desea afectar dos campos en la tabla, podría pensarse tanto en usar REPLACE masivamente como implementarlo en el LOAD DATA (LOAD DATA ... REPLACE...), pero el problema que en ambos casos reemplaza los valores completos del registro, con lo que los otros datos se perderían.
En este caso lo que yo recomendaría es usar una tabla temporal donde se carguen los datos nuevos, y luego realizar un UPDATE con JOIN donde se pueda indicar que la tabla destino se actualice en esos dos campos con la tabla temporal.
Algo como:

Código MySQL:

UPDATE tabladestino T1 INNER JOIN tablatemporal T2  ON T1.idtabla = T2.idtabla
SET T1.campo1 = T2.campo1, T1.campo2 = T2.campo2;
 

Podría ser más simple y rápido

Crees posible la utilización de algun tipo de caché? o la caché solo afecta a los selects no a los inserts ni updates, y al estar todo el rato insertando y updatando la tabla la caché no haría nada, correcto?

La query cache de MySQL, según el manual, solamente opera con SELECT (5.12. La caché de consultas de MySQL)

[neodani]

Entiendo que para sacar partido a esta consulta:

Código MySQL:

UPDATE tabladestino T1 INNER JOIN tablatemporal T2  ON T1.idtabla = T2.idtabla
SET T1.campo1 = T2.campo1, T1.campo2 = T2.campo2;
 

Sería necesario que solo únicamente en la tablatemporal estuviesen los registros que hayan cambiado su valor, y no todos los registros. Correcto?

El problema que veo de hacerlo así, es que estas gastando también el tiempo de los inserts para crear la tabla temporal cada vez que una columna cambia, y tienes que hacer un insert en una tabla temporal y luego hacer el update final, no es mas lento que ir haciendo updates individuales?

Muchas gracias

[gnzsoloyo]

Sería necesario que solo únicamente en la tablatemporal estuviesen los registros que hayan cambiado su valor, y no todos los registros. Correcto?

Exacto. Esa es la idea.

El problema que veo de hacerlo así, es que estas gastando también el tiempo de los inserts para crear la tabla temporal cada vez que una columna cambia, y tienes que hacer un insert en una tabla temporal y luego hacer el update final, no es mas lento que ir haciendo updates individuales?

Tu pregunta no apuntaba a INSERTs o UPDATEs individuales. Preguntaste sobre INSERTs y UPDATEs masivos y ese es un modo de manejar UPDATEs masivos.

Esa tabla se debe cargar con un LOAD DATA y la ejecución de un UPDATE de esas características es inmensamente más rápido, en tanto la tabla temporal contenga la primary key de la tabla destino (en tu caso implicaría tres campos, entonces).

[neodani]

Gracias, la pondré en practica y os cuento los resultados