SQLite 批量insert

升木

　　本人翻译, 原文见:
　　http://tech.vg.no/2011/04/04/speeding-up-sqlite-insert-operations/
　　我正在开发一个Android程序, 它使用SQLite存储大约6000行的数据, 这些数据会定期从网上更新. 在模拟器上, 从网络获取和解析CSV格式的数据所花的时间大概是20秒, 但是把数据插入的数据库的时间是71秒.
　　因为数据更新的操作差不多一个星期才有一次, 因此我认为1分多钟的操作时间是可以接受的. 但当我把程序在真机上跑的时候, 6000行的插入时间让我吓了一跳 -- 478秒, 差不多8分钟. 很奇怪, 一般来说, 真机要比模拟器快, 何况我用的是Sanmsung Galaxy S - 当时最快的Android设备之一. 这是我第一次使用SQLite, 我想我下一步得优化插入操作了.

String sql = "INSERT INTO table (number, nick) VALUES (?, ?)";
SQLiteStatement stmt = db.compileStatement(sql);
for (int i = 0; i < values.size(); i++) {
stmt.bindString(1, values.get(i).number);
stmt.bindString(2, values.get(i).nick);
stmt.execute();
stmt.clearBindings();
}
　　有了上面的改动后, 我在模拟器上测试, 时间从71秒减少到56秒, 但是在真机上测的时候, 时间反而多了几秒, 我反复测了几次, 还是差不多一样的结果.
　　进一步研究, 我从SQLite的一个文档页面看到“PRAGMA synchronous = OFF” 将会告诉SQLite, 当它把数据传入操作系统的时候, 不要立即同步. 这个设置让插入时间从71秒减少的50秒, 在真机上的结果是361秒, 差不多快2分钟.
　　感觉到找到路子之后, 我认为是文件系统减慢了插入操作, 我在网上找到了很多关于Galaxy S I/O 性能问题的参考 - 所有的都与RFS文件系统相关. 对于SQLite而言, 它每插入一次数据, 都会执行一下fsync, 以保证数据写入了磁盘. 再看SQLite文档, 我发现用transactions能够将数据保存在内存中, 只有在commit时候才写入文件系统. 因此我改动如下:

String sql = "INSERT INTO table (number, nick) VALUES (?, ?)";
db.beginTransaction();
SQLiteStatement stmt = db.compileStatement(sql);
for (int i = 0; i < values.size(); i++) {
stmt.bindString(1, values.get(i).number);
stmt.bindString(2, values.get(i).nick);
stmt.execute();
stmt.clearBindings();
}
db.setTransactionSuccessful();
db.endTransaction();
　　 
　　结合transactions和 compiled statements后, 性能有了巨大的提升: 从71秒到不可置信的5秒! 在Galaxy上的结果更是牛逼: 从478秒到1.5秒!
　　结论:
　　- 除非你只执行单次的insert, 或者你需要数据立即写入文件系统, 不然的话就用transactions
　　- 保证你的程序在真机上测试过, 最好是多台机器上测.
　　- 我上面的性能提升只在Samsung Galaxy S上测过, 不同的机器可能还是会有性能问题.