programing tip

SQLite 삽입이 매우 느립니까?

itbloger 2021. 1. 10. 16:55
반응형

SQLite 삽입이 매우 느립니까?

나는 최근에 SQLite에 대해 읽었고 시도해 볼 것이라고 생각했습니다. 하나의 레코드를 삽입하면 정상적으로 수행됩니다. 하지만 100을 삽입하면 5 초가 걸리고 레코드 수가 증가하면 시간도 늘어납니다. 무엇이 잘못 되었을까요? 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();

랩 BEGIN\ END대량 삽입 주위에 문. Sqlite는 트랜잭션에 최적화되어 있습니다.

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();

모든 삽입 (일명 대량 삽입)을 단일 트랜잭션 으로 래핑 해보세요 .

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();
}

기본적으로 SQLite는 트랜잭션의 모든 삽입을 래핑 하므로 프로세스 속도가 느려집니다.

INSERT는 정말 느립니다. 초당 수십 개의 INSERT 만 할 수 있습니다.

Actually, SQLite will easily do 50,000 or more INSERT statements per second on an average desktop computer. But it will only do a few dozen transactions per second.

Transaction speed is limited by disk drive speed because (by default) SQLite actually waits until the data really is safely stored on the disk surface before the transaction is complete. That way, if you suddenly lose power or if your OS crashes, your data is still safe. For details, read about atomic commit in SQLite..

By default, each INSERT statement is its own transaction. But if you surround multiple INSERT statements with BEGIN...COMMIT then all the inserts are grouped into a single transaction. The time needed to commit the transaction is amortized over all the enclosed insert statements and so the time per insert statement is greatly reduced.

I read everywhere that creating transactions is the solution to slow SQLite writes, but it can be long and painful to rewrite your code and wrap all your SQLite writes in transactions.

I found a much simpler, safe and very efficient method: I enable a (disabled by default) SQLite 3.7.0 optimisation : the Write-Ahead-Log (WAL). The documentation says it works in all unix (i.e. Linux and OSX) and Windows systems.

How ? Just run the following commands after initializing your SQLite connection:

PRAGMA journal_mode = WAL
PRAGMA synchronous = NORMAL

My code now runs ~600% faster : my test suite now runs in 38 seconds instead of 4 minutes :)

See "Optimizing SQL Queries" in the ADO.NET help file SQLite.NET.chm. Code from that page:

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();
}

ReferenceURL : https://stackoverflow.com/questions/3852068/sqlite-insert-very-slow

반응형