SQLite插入非常慢？

Question

我最近阅读了SQLite，并认为我会试试看。当我插入一条记录时，它表现良好。但是当我插入一百个时，它需要五秒钟，并且随着记录数量的增加，时间也会增加。什么可能是错的？我使用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

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

Answer 2

尝试包装你所有的插件（又名，批量插入）到一个单一的transaction：

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 wraps every inserts in a transaction，这将会减慢的过程：

INSERT真的很慢 - 我每秒只能做几十个INSERTs

实际上，SQLite在一台普通台式机上每秒钟很容易做到50,000或更多的INSERT语句。但它每秒只能做几十个事务。

交易速度受磁盘驱动器速度的限制，因为（默认情况下）SQLite实际上会一直等到数据真正安全存储在磁盘表面上，然后交易完成。这样，如果您突然断电或者您的操作系统崩溃，您的数据仍然安全。有关详细信息，请阅读SQLite中的原子提交。

默认情况下，每个INSERT语句都是它自己的事务。但是，如果使用BEGIN ... COMMIT包围多个INSERT语句，则所有插入操作都会分组到一个事务中。提交事务所需的时间将在所有随附的插入语句中摊销，因此每个插入语句的时间会大大减少。

Answer 3

请参阅ADO.NET帮助文件SQLite.NET.chm中的“优化SQL查询”。从该页面代码：

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 4

我阅读无处不在，创造交易是减缓的SQLite写的解决方案，但它可以是漫长而痛苦的重写你的代码和包装所有的SQLite在交易写道。

我发现一个更简单，安全和非常有效的方法：我启用（默认禁用）SQLite 3.7.0优化：Write-Ahead-Log (WAL)。 该文档说，它适用于所有unix（即Linux和OSX）和Windows系统。

怎么样？只要运行以下命令初始化您的SQLite连接后：

PRAGMA journal_mode = WAL 
PRAGMA synchronous = NORMAL 

我的代码现在运行〜600％的速度：我的测试套件现在跑38秒而不是:)