Ni corto ni perezoso, me lancé a la tarea. Mi primera idea fue hacer una consulta a la BD Informix y luego procesar cada registro, componer la correspondiente instrucción SQL INSERT y ejecutarla. Algo así
1 $sql="SELECT * FROM $tabla where " . $where ; 2 $rs=odbc_exec($conn, $sql); if (!$rs) {exit("$modname: Error en SQL [$sql]");} 3 ... 4 while (odbc_fetch_row($rs)) { 5 ... 6 $sql="insert into " . $tabla . "(" ... 7 $res = $db_sqlite3->exec($sql); 8 ... 9 }//while
Pero me decepcioné cuando vi lo que se tardaba en hacer la copia
Como véis, el primer bloque son 2.628 registros, y tarda 34"
El segundo bloque, 50.446 registros, tardó 11' 13", o sea, 673"
Fatal. Y eso que sólo estaba sacando una parte de los registros de la tabla. La tabla original tenía casi 90.000 registros, pero es que además necesitaba copiar otras tablas más grandes, incluso una de ellas con 8 millones de registros. Y a este paso, la copia iba a terminar para mi fecha de jubilación, aproximadamente.
La cosa quedó ahí aparcada un tiempo, y me desvié hacia otras cuestiones. El problema lo resolví de otra manera, pero me quedó la espinita clavada. ¿Por qué se tardaba tanto en hacer una copia, si yo había leído que SQLite3 era rapidísima? Estaba claro que no era cosa del Informix, ya que hacer una copia a un MDB de Access era muchísimo más rápido.
Y ahora llega el momento en el que entra mi gran amigo Miguel en acción. El otro día vino a verme al curro, y, mientras nos tomábamos un café, surgió el tema de SQLite3 y le conté mi problema.
Un par de horas después tenía un correo suyo en mi buzón, con enlaces a estas dos estupendas entradas:
Hola Jose
Tu problema de lentitud no parece que sea SOLO de indices. Como las BD de SQLite son ficheros hay otro problema añadido de bloqueo de ficheros.
Mira estos enlaces:
http://tech.vg.no/2011/04/04/speeding-up-sqlite-insert-operations/
http://blog.quibb.org/2010/08/fast-bulk-inserts-into-sqlite/
Después de leerlos, he comprendido la causa del problema. Básicamente, la cosa es que cada INSERT que se ejecuta dentro del bucle consiste en una transacción. SQLite3 se asegura de que se consolida en disco antes de dar por terminada la operación. Lo que se necesita en casos como este es ejecutar TODAS las instrucciones en una única transacción, y no cada una por separado. Esto es lo principal. Aparte, se pueden parametrizar ciertos valores en la BD para ayudar a mejorar el rendimiento:
1) Desactivar el modo de funcionamiento síncrono de SQLite3
1 $db_sqlite3->exec("PRAGMA synchronous=OFF");
2) Iniciar una transacción justo antes del bucle, y cerrarla al salir
1 $sql="SELECT * FROM $tabla where " . $where ; 2 $rs=odbc_exec($conn, $sql); if (!$rs) {exit("$modname: Error en SQL [$sql]");} 3 ... 4 $db_sqlite3->exec("BEGIN TRANSACTION"); 5 while (odbc_fetch_row($rs)) { 6 ... 7 $sql="insert into " . $tabla . "(" ... 8 $res = $db_sqlite3->exec($sql); 9 ... 10 }//while 11 $db_sqlite3->exec("COMMIT TRANSACTION");3) Otros PRAGMA para mejorar el rendimiento
1 $db_sqlite3->exec("PRAGMA count_changes=OFF"); 2 $db_sqlite3->exec("PRAGMA journal_mode=MEMORY"); 3 $db_sqlite3->exec("PRAGMA temp_store=MEMORY");
4) Utilizar sentencias precompiladas (prepared statements)
Yo empleé en primer lugar la técnica 1) y el resultado en ese caso fue prometedor:
El segundo bloque tardó 5' 41", o sea, 341", frente a los 673" del primer caso. O sea, una mejora del 49%. Bien, muy bien.
Pero la gran mejora vino al hacer lo segundo, y que he mencionado antes: abrir una transacción antes de entrar en el bucle, y cerrarla al salir. Resultados:
Primer bloque: 1" ¡Impresionante! Una mejora del 97%
Segundo bloque: 7". Una mejora del 99%. ¿Es para flipar o no lo es?
La opción 3) (otros PRAGMAS) también la probé, pero no hay variaciones significativas.
Y como no os lo voy a dar todo hecho, el tema de probar con las sentencias preparadas os lo dejo para que lo probéis vosotros.
Por supuesto, me encantaría oír vuestros comentarios.