c# - framework - sqlite visual studio 2017

Insertar SQLite muy lento?

Question

Recientemente leí sobre SQLite y pensé que lo probaría. Cuando inserto un registro, funciona bien. Pero cuando inserto cien, demora cinco segundos y, a medida que aumenta el recuento de registros, también aumenta el tiempo. ¿Qué podría estar mal? Estoy usando el Contenedor SQLite (system.data.SQlite) :

dbcon = new SQLiteConnection(connectionString);
dbcon.Open();

//---INSIDE LOOP

 SQLiteCommand sqlComm = new SQLiteCommand(sqlQuery, dbcon);

 nRowUpdatedCount = sqlComm.ExecuteNonQuery(); 

//---END LOOP

dbcon.close();

Answer 1

Consulte "Optimización de consultas SQL" en el archivo de ayuda de ADO.NET SQLite.NET.chm. Código de esa página:

using (SQLiteTransaction mytransaction = myconnection.BeginTransaction())
{
  using (SQLiteCommand mycommand = new SQLiteCommand(myconnection))
  {
    SQLiteParameter myparam = new SQLiteParameter();
    int n;

    mycommand.CommandText = "INSERT INTO [MyTable] ([MyId]) VALUES(?)";
    mycommand.Parameters.Add(myparam);

    for (n = 0; n < 100000; n ++)
    {
      myparam.Value = n + 1;
      mycommand.ExecuteNonQuery();
    }
  }
  mytransaction.Commit();
}

Answer 2

Intente envolver todas sus inserciones (es decir, una inserción masiva) en una sola SAVEPOINTS :

string insertString = "INSERT INTO [TableName] ([ColumnName]) Values (@value)";

SQLiteCommand command = new SQLiteCommand();
command.Parameters.AddWithValue("@value", value);
command.CommandText = insertString;
command.Connection = dbConnection;
SQLiteTransaction transaction = dbConnection.BeginTransaction();
try
{
    //---INSIDE LOOP
    SQLiteCommand sqlComm = new SQLiteCommand(sqlQuery, dbcon);
    nRowUpdatedCount = sqlComm.ExecuteNonQuery(); 
    //---END LOOP

    transaction.Commit();
    return true;
}
catch (SQLiteException ex)
{
    transaction.Rollback();
}

De forma predeterminada, SQLite ajusta cada inserción en una transacción , lo que ralentiza el proceso:

INSERT es muy lento: solo puedo hacer unas pocas docenas de INSERT por segundo

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 la transacción está limitada por la velocidad de la unidad de disco porque (de manera predeterminada) SQLite realmente espera hasta que los datos realmente se almacenen de manera segura en la superficie del disco antes de que se complete la transacción. De esta forma, si de repente se corta la energía o si su sistema operativo falla, sus datos estarán seguros. Para más detalles, lea sobre la confirmación atómica en SQLite.

Por defecto, cada instrucción INSERT es su propia transacción. Pero si rodea varias instrucciones INSERT con BEGIN ... COMMIT, todas las inserciones se agrupan en una sola transacción. El tiempo necesario para comprometer la transacción se amortiza en todas las declaraciones de inserción adjuntas y, por lo tanto, el tiempo por declaración de inserción se reduce en gran medida.

Answer 3

Leí en todas partes que la creación de transacciones es la solución para ralentizar las escrituras de SQLite, pero puede ser largo y doloroso reescribir el código y ajustar todas las escrituras de SQLite en las transacciones.

Encontré un método mucho más simple, seguro y muy eficiente: habilito una optimización de SQLite 3.7.0 (desactivada por defecto): Write-Ahead-Log (WAL) . La documentación dice que funciona en todos los sistemas Unix (es decir, Linux y OSX) y Windows.

Cómo ? Simplemente ejecute los siguientes comandos después de inicializar su conexión SQLite:

PRAGMA journal_mode = WAL
PRAGMA synchronous = NORMAL

Mi código ahora se ejecuta ~ 600% más rápido: mi suite de prueba ahora se ejecuta en 38 segundos en lugar de 4 minutos :)

Answer 4

BEGIN instrucciones BEGIN / END alrededor de sus inserciones en bloque. Sqlite está optimizado para transacciones.

dbcon = new SQLiteConnection(connectionString);
dbcon.Open();

SQLiteCommand sqlComm;
sqlComm = new SQLiteCommand("begin", dbcon);
sqlComm.ExecuteNonQuery(); 
//---INSIDE LOOP

 sqlComm = new SQLiteCommand(sqlQuery, dbcon);

 nRowUpdatedCount = sqlComm.ExecuteNonQuery(); 

//---END LOOP
sqlComm = new SQLiteCommand("end", dbcon);
sqlComm.ExecuteNonQuery(); 
dbcon.close();