QLite大量插入的效率问题
http://www.cnblogs.com/rader/articles/1543760.html
这两天再做一个数据转换的程序,讲原来用二进制形势存储的数据转换到SQLite数据库中去。记录总共有1千万多点。开发使用的是.net framework 3.5 sp1的环境,所以直接使用了EntityFramework方便数据库的操作。EntityFramework倒是省了一些事情,可是效率上让人无法忍受。整个程序运行一次花了40多分钟。利用EntityFramework,首先从数据库生成模型,然后我这样来保存数据:
1
2
3
4
5
6
7
8
|
using
(GameSetEntities gse =
new
GameSetEntities(CreateConnection()))
{
foreach
(
var
ticket
in
tickets)
{
gse.AddToShuffledTicketSet(ticket);
}
gse.SaveChanges(
true
);
}
|
Ticket是我要插入数据库中的记录。一千多万条记录,我是分成5000条一批进行处理的。5000条记录先被添加到GameSetEntities(DataContext)中,然后进行提交。
利用StopWatch,我记录了这5000条记录插入数据库所消耗的时间,平均是1.1秒。(2080 * 1.1)/60 大约是36分钟,也就是说整个程序的运行时间中有80%多的时间是花在了数据库的插入操作上。插入5000条记录平均花费1.1秒,这个速度实在太慢了。我开始怀疑是不是EntityFramework效率问题?既然这样那就比比看吧,看看纯粹的ado.net代码是不是比它快。下面是纯手工的数据库操作代码:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
using
(DbConnection conn = DbProviderFactories.GetFactory(
"System.Data.SQLite"
).CreateConnection())
{
conn.ConnectionString =
"Data Source = "
+ m_datasourceFile +
";Version = 3"
;
using
(SQLiteCommand insertRngCmd = (SQLiteCommand)conn.CreateCommand())
{
insertRngCmd.CommandText =
@"INSERT INTO shuffled_tickets (ticket_idx, seed, win_credits, [timestamp], redeemed, prog_levels)
VALUES (@ticket_idx, @seed, @win_credits, @timestamp, @redeemed, @prog_levels)"
;
conn.Open();
foreach
(
var
ticket
in
tickets)
{
insertRngCmd.Parameters.AddWithValue(
"@ticket_idx"
, ticket.ticket_idx);
insertRngCmd.Parameters.AddWithValue(
"@seed"
, ticket.seed);
insertRngCmd.Parameters.AddWithValue(
"@win_credits"
, ticket.win_credits);
insertRngCmd.Parameters.AddWithValue(
"@timestamp"
, ticket.timestamp);
insertRngCmd.Parameters.AddWithValue(
"@redeemed"
, ticket.redeemed);
insertRngCmd.Parameters.AddWithValue(
"@prog_levels"
, ticket.prog_levels);
insertRngCmd.ExecuteNonQuery();
}
}
}
|
这样的代码一运行,发现速度更慢了,5000记录居然要20秒的时间……看来问题不是在这里。
Google一番之后,在Sqlite.net ADO Provider的论坛里发现这篇文章:Fastest Bulk Inserts.在最后一个例子“Fastest universal way to insert data using standard ADO.NET constructs”中作者提到“100,000 inserts on my machine in 1.4 seconds”--10万条记录插入耗时1.4秒。我才5000条不应该需要20秒那么慢的(用EntityFramework需要1.1秒,后来分析应该是内部使用了批量插入操作),看来是我的代码写的有问题。仔细阅读了作者给的例子之后发现,我们代码的差别主要在于事务的使用上。作者给的例子使用事务一次性提交10万条记录,而我的代码没有使用事务,而是每次提交。于是我改写了一下我的代码,也加上事务进行提交:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
using
(DbConnection conn = DbProviderFactories.GetFactory(
"System.Data.SQLite"
).CreateConnection())
{
conn.ConnectionString =
"Data Source = "
+ m_datasourceFile +
";Version = 3"
;
using
(SQLiteCommand insertRngCmd = (SQLiteCommand)conn.CreateCommand())
{
insertRngCmd.CommandText =
@"INSERT INTO shuffled_tickets (ticket_idx, seed, win_credits, [timestamp], redeemed, prog_levels)
VALUES (@ticket_idx, @seed, @win_credits, @timestamp, @redeemed, @prog_levels)"
;
conn.Open();
var
transaction = conn.BeginTransaction();
foreach
(
var
ticket
in
tickets)
{
insertRngCmd.Parameters.AddWithValue(
"@ticket_idx"
, ticket.ticket_idx);
insertRngCmd.Parameters.AddWithValue(
"@seed"
, ticket.seed);
insertRngCmd.Parameters.AddWithValue(
"@win_credits"
, ticket.win_credits);
insertRngCmd.Parameters.AddWithValue(
"@timestamp"
, ticket.timestamp);
insertRngCmd.Parameters.AddWithValue(
"@redeemed"
, ticket.redeemed);
insertRngCmd.Parameters.AddWithValue(
"@prog_levels"
, ticket.prog_levels);
insertRngCmd.ExecuteNonQuery();
}
transaction.Commit();
}
|
结果5000条记录的插入时间由原来的1.1秒变为0.09秒,这是一个非常大的提升。
但是为什么加上一个事务之后的差别这么大呢?我翻了翻Sqlite的文档,“Database Speed Comparison”里有解释:
在“Test1:1000 INSERTS”下面有一句话:……“In this test, each SQL statement is a separate transaction so the database file must be opened and closed and the cache must be flushed 1000 times”……
在“Test 2:25000 INSERT in a transaction”下面有另外一句话:……“When all the INSERTs are put in a transaction, SQLite no longer has to close and reopen the database or invalidate its cache between each statement. ”……
从上面两句话看来,没有事务的时候,SQLite的插入操作使用了太多的IO操作,而是用事务的话,只需要一次IO。