Mejore el rendimiento de importación de datos grandes en SQLite con C#

.net system.data.sqlite

Question

Estoy utilizando C # para importar un CSV con 6-8 millones de filas.

Mi mesa se ve así:

CREATE TABLE [Data] ([ID] VARCHAR(100)  NULL,[Raw] VARCHAR(200)  NULL)
CREATE INDEX IDLookup ON Data(ID ASC)

Estoy usando System.Data.SQLite para hacer la importación.

Actualmente, para hacer 6 millones de filas, tomará 2 minutos y 55 segundos en un Windows 7 32bit, Core2Duo 2.8Ghz y 4GB RAM. No está mal, pero me preguntaba si alguien podría ver una manera de importarlo más rápido.

Aquí está mi código:

public class Data
{
  public string IDData { get; set; }
  public string RawData { get; set; }
}   

string connectionString = @"Data Source=" + Path.GetFullPath(AppDomain.CurrentDomain.BaseDirectory + "//dbimport");
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(connectionString);
conn.Open();

//Dropping and recreating the table seems to be the quickest way to get old data removed
System.Data.SQLite.SQLiteCommand command = new System.Data.SQLite.SQLiteCommand(conn);
command.CommandText = "DROP TABLE Data";
command.ExecuteNonQuery();
command.CommandText = @"CREATE TABLE [Data] ([ID] VARCHAR(100)  NULL,[Raw] VARCHAR(200)  NULL)";
command.ExecuteNonQuery();
command.CommandText = "CREATE INDEX IDLookup ON Data(ID ASC)";
command.ExecuteNonQuery();

string insertText = "INSERT INTO Data (ID,RAW) VALUES(@P0,@P1)";

SQLiteTransaction trans = conn.BeginTransaction();
command.Transaction = trans;

command.CommandText = insertText;
Stopwatch sw = new Stopwatch();
sw.Start();
using (CsvReader csv = new CsvReader(new StreamReader(@"C:/Data.txt"), false))
{
   var f = csv.Select(x => new Data() { IDData = x[27], RawData = String.Join(",", x.Take(24)) });

   foreach (var item in f)
   {
      command.Parameters.AddWithValue("@P0", item.IDData);
      command.Parameters.AddWithValue("@P1", item.RawData);
      command.ExecuteNonQuery();
   }
 }
 trans.Commit();
 sw.Stop();
 Debug.WriteLine(sw.Elapsed.Minutes + "Min(s) " + sw.Elapsed.Seconds + "Sec(s)");
 conn.Close();

Answer 1

Esto es bastante rápido para 6 millones de discos.

Parece que lo está haciendo de la manera correcta, hace un tiempo leí en sqlite.org que al insertar registros necesita poner estos insertos dentro de la transacción, si no lo hace, sus insertos se limitarán a solo 60 ¡por segundo! Esto se debe a que cada inserción se tratará como una transacción separada y cada transacción debe esperar a que el disco gire completamente. Puedes leer la explicación completa aquí:

http://www.sqlite.org/faq.html#q19

En realidad, SQLite hará fácilmente 50,000 o más declaraciones INSERT por segundo en una computadora de escritorio promedio. Pero solo hará unas pocas docenas de transacciones por segundo. La velocidad de transacción está limitada por la velocidad de rotación de la unidad de disco. Una transacción normalmente requiere dos rotaciones completas del disco, que en una unidad de disco de 7200 RPM lo limita a aproximadamente 60 transacciones por segundo.

Comparando tu tiempo con el promedio indicado anteriormente: 50,000 por segundo => eso debería tomar 2m 00 seg. Lo cual es solo un poco más rápido que tu tiempo.

La velocidad de transacción está limitada por la velocidad de la unidad de disco porque (de forma predeterminada) SQLite realmente espera hasta que los datos se almacenan de forma segura en la superficie del disco antes de que se complete la transacción. De esa manera, si de repente pierde energía o si su sistema operativo falla, sus datos aún están seguros. Para más detalles, lea acerca de la confirmación atómica en SQLite ..

De forma predeterminada, cada instrucción INSERT es su propia transacción. Pero si rodea varias instrucciones INSERT con BEGIN ... COMMIT, entonces todas las inserciones se agrupan en una sola transacción. El tiempo necesario para confirmar la transacción se amortiza en todas las declaraciones de inserción adjuntas y, por lo tanto, el tiempo por instrucción de inserción se reduce considerablemente.

Hay una sugerencia en el siguiente párrafo que podría intentar acelerar las inserciones:

Otra opción es ejecutar PRAGMA synchronous = OFF. Este comando hará que SQLite no espere a que los datos alcancen la superficie del disco, lo que hará que las operaciones de escritura parezcan mucho más rápidas. Pero si pierde el poder en medio de una transacción, su archivo de base de datos podría dañarse.

Siempre pensé que SQLite fue diseñado para "cosas simples", 6 millones de registros me parece que es un trabajo para un servidor de base de datos real como MySQL.

Contar registros en una tabla en SQLite con tantos registros puede llevar mucho tiempo, solo para su información, en lugar de usar SELECT COUNT (*), siempre puede usar SELECT MAX (rowid) que es muy rápido, pero no es tan preciso si estabas borrando registros en esa tabla

EDITAR.

Como dijo Mike Woodhouse, crear el índice después de insertar los registros debería acelerar todo el proceso, ese es un consejo común en otras bases de datos, pero no puede decir con seguridad cómo funciona en SQLite.

Answer 2

Hice una importación similar, pero dejé que mi código c # simplemente escribiera los datos en un csv primero y luego ejecuté la utilidad de importación de sqlite. Pude importar más de 300 millones de discos en tan solo 10 minutos de esta manera.

No estoy seguro si esto se puede hacer directamente desde c # o no.

Answer 3

Puede ganar bastante tiempo al vincular sus parámetros de la siguiente manera:

...
string insertText = "INSERT INTO Data (ID,RAW) VALUES( ? , ? )";  // (1)

SQLiteTransaction trans = conn.BeginTransaction();
command.Transaction = trans;

command.CommandText = insertText;

//(2)------
   SQLiteParameter p0 = new SQLiteParameter();
   SQLiteParameter p1 = new SQLiteParameter();
   command.Parameters.Add(p0);
   command.Parameters.Add(p1);
//---------

Stopwatch sw = new Stopwatch();
sw.Start();
using (CsvReader csv = new CsvReader(new StreamReader(@"C:/Data.txt"), false))
{
   var f = csv.Select(x => new Data() { IDData = x[27], RawData = String.Join(",", x.Take(24)) });

   foreach (var item in f)
   {
      //(3)--------
         p0.Value = item.IDData;
         p1.Value = item.RawData;
      //-----------
      command.ExecuteNonQuery();
   }
 }
 trans.Commit();
...

Realice los cambios en las secciones 1, 2 y 3. De esta manera, el enlace de parámetros parece ser un poco más rápido. Especialmente cuando tienes muchos parámetros, este método puede ahorrar bastante tiempo.

Answer 4

Una cosa que podría intentar es crear el índice después de que se hayan insertado los datos; por lo general, las bases de datos son mucho más rápidas para crear índices en una sola operación que para actualizarlos después de cada inserción (o transacción).

No puedo decir que definitivamente funcionará con SQLite, pero como solo se necesitan dos líneas para moverse, vale la pena intentarlo.

También me pregunto si una transacción de 6 millones de filas podría estar yendo demasiado lejos. ¿Podría cambiar el código para probar diferentes tamaños de transacción? ¿Diga 100, 1000, 10000, 100000? ¿Hay un "punto dulce"?