优化SQLite很棘手.C应用程序的批量插入性能可以从每秒85次插入到每秒超过96,000次插入!
背景:我们使用SQLite作为桌面应用程序的一部分.我们有大量的配置数据存储在XML文件中,这些数据被解析并加载到SQLite数据库中,以便在初始化应用程序时进行进一步处理.SQLite非常适合这种情况,因为它速度快,不需要专门配置,数据库作为单个文件存储在磁盘上.
理由: 最初我对我所看到的表现感到失望.事实证明,SQLite的性能可能会有很大差异(对于批量插入和选择),具体取决于数据库的配置方式以及如何使用API.弄清楚所有选项和技术是什么并不是一件小事,所以我认为创建这个社区wiki条目以与Stack Overflow读者分享结果是谨慎的,以便为其他人节省相同调查的麻烦.
实验:我不是简单地谈论一般意义上的性能提示(即"使用事务!"),而是认为最好编写一些C代码并实际测量各种选项的影响.我们将从一些简单的数据开始:
多伦多市完整交通时间表的28 MB TAB分隔文本文件(约865,000条记录)
我的测试机器是运行Windows XP的3.60 GHz P4.
该代码使用Visual C++ 2005 编译为"Release",带有"Full Optimization"(/ Ox)和Favor Fast Code(/ Ot).
我正在使用SQLite"Amalgamation",直接编译到我的测试应用程序中.我碰巧遇到的SQLite版本有点旧(3.6.7),但我怀疑这些结果与最新版本相当(如果你不这么想请发表评论).
我们来写一些代码吧!
代码:一个简单的C程序,它逐行读取文本文件,将字符串拆分为值,然后将数据插入SQLite数据库.在代码的这个"基线"版本中,创建了数据库,但我们实际上不会插入数据:
/*************************************************************
Baseline code to experiment with SQLite performance.
Input data is a 28 MB TAB-delimited text file of the
complete Toronto Transit System schedule/route info
from http://www.toronto.ca/open/datasets/ttc-routes/
**************************************************************/
#include
#include
#include
#include
#include "sqlite3.h"
#define INPUTDATA "C:\\TTC_schedule_scheduleitem_10-27-2009.txt"
#define DATABASE "c:\\TTC_schedule_scheduleitem_10-27-2009.sqlite"
#define TABLE "CREATE TABLE IF NOT EXISTS TTC (id INTEGER PRIMARY KEY, Route_ID TEXT, Branch_Code TEXT, Version INTEGER, Stop INTEGER, Vehicle_Index INTEGER, Day Integer, Time TEXT)"
#define BUFFER_SIZE 256
int main(int argc, char **argv) {
sqlite3 * db;
sqlite3_stmt * stmt;
char * sErrMsg = 0;
char * tail = 0;
int nRetCode;
int n = 0;
clock_t cStartClock;
FILE * pFile;
char sInputBuf [BUFFER_SIZE] = "\0";
char * sRT = 0; /* Route */
char * sBR = 0; /* Branch */
char * sVR = 0; /* Version */
char * sST = 0; /* Stop Number */
char * sVI = 0; /* Vehicle */
char * sDT = 0; /* Date */
char * sTM = 0; /* Time */
char sSQL [BUFFER_SIZE] = "\0";
/*********************************************/
/* Open the Database and create the Schema */
sqlite3_open(DATABASE, &db);
sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);
/*********************************************/
/* Open input file and import into Database*/
cStartClock = clock();
pFile = fopen (INPUTDATA,"r");
while (!feof(pFile)) {
fgets (sInputBuf, BUFFER_SIZE, pFile);
sRT = strtok (sInputBuf, "\t"); /* Get Route */
sBR = strtok (NULL, "\t"); /* Get Branch */
sVR = strtok (NULL, "\t"); /* Get Version */
sST = strtok (NULL, "\t"); /* Get Stop Number */
sVI = strtok (NULL, "\t"); /* Get Vehicle */
sDT = strtok (NULL, "\t"); /* Get Date */
sTM = strtok (NULL, "\t"); /* Get Time */
/* ACTUAL INSERT WILL GO HERE */
n++;
}
fclose (pFile);
printf("Imported %d records in %4.2f seconds\n", n, (clock() - cStartClock) / (double)CLOCKS_PER_SEC);
sqlite3_close(db);
return 0;
}
按原样运行代码实际上并不执行任何数据库操作,但它会让我们了解原始C文件I/O和字符串处理操作的速度.
在0.94秒内导入864913条记录
大!如果我们实际上没有插入任何插入,我们可以每秒执行920,000次插入:-)
我们将使用从文件中读取的值生成SQL字符串,并使用sqlite3_exec调用该SQL操作:
sprintf(sSQL, "INSERT INTO TTC VALUES (NULL, '%s', '%s', '%s', '%s', '%s', '%s', '%s')", sRT, sBR, sVR, sST, sVI, sDT, sTM); sqlite3_exec(db, sSQL, NULL, NULL, &sErrMsg);
这将是缓慢的,因为SQL将被编译为每个插入的VDBE代码,并且每个插入都将在其自己的事务中发生.多慢？
在9933.61秒内导入864913条记录
哎呀!2小时45分钟!这只是每秒85次插入.
默认情况下,SQLite将评估唯一事务中的每个INSERT/UPDATE语句.如果执行大量插入操作,建议将操作包装在事务中:
sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);
pFile = fopen (INPUTDATA,"r");
while (!feof(pFile)) {
...
}
fclose (pFile);
sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &sErrMsg);
在38.03秒内导入864913条记录
那更好.简单地将所有插件包装在一个事务中,将我们的性能提高到每秒23,000次插入.
使用事务是一个巨大的改进,但是如果我们使用相同的SQL重复使用,则重新编译每个插入的SQL语句是没有意义的.让我们使用sqlite3_prepare_v2一次编译我们的SQL语句,然后使用以下命令将我们的参数绑定到该语句sqlite3_bind_text:
/* Open input file and import into the database */
cStartClock = clock();
sprintf(sSQL, "INSERT INTO TTC VALUES (NULL, @RT, @BR, @VR, @ST, @VI, @DT, @TM)");
sqlite3_prepare_v2(db, sSQL, BUFFER_SIZE, &stmt, &tail);
sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);
pFile = fopen (INPUTDATA,"r");
while (!feof(pFile)) {
fgets (sInputBuf, BUFFER_SIZE, pFile);
sRT = strtok (sInputBuf, "\t"); /* Get Route */
sBR = strtok (NULL, "\t"); /* Get Branch */
sVR = strtok (NULL, "\t"); /* Get Version */
sST = strtok (NULL, "\t"); /* Get Stop Number */
sVI = strtok (NULL, "\t"); /* Get Vehicle */
sDT = strtok (NULL, "\t"); /* Get Date */
sTM = strtok (NULL, "\t"); /* Get Time */
sqlite3_bind_text(stmt, 1, sRT, -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 2, sBR, -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 3, sVR, -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 4, sST, -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 5, sVI, -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 6, sDT, -1, SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 7, sTM, -1, SQLITE_TRANSIENT);
sqlite3_step(stmt);
sqlite3_clear_bindings(stmt);
sqlite3_reset(stmt);
n++;
}
fclose (pFile);
sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &sErrMsg);
printf("Imported %d records in %4.2f seconds\n", n, (clock() - cStartClock) / (double)CLOCKS_PER_SEC);
sqlite3_finalize(stmt);
sqlite3_close(db);
return 0;
在16.27秒内导入864913条记录
太好了!还有一些代码(不要忘记调用sqlite3_clear_bindings和sqlite3_reset),但我们的性能提高了一倍以上,每秒53,000次插入.
默认情况下,SQLite将在发出操作系统级写入命令后暂停.这可以保证将数据写入磁盘.通过设置synchronous = OFF,我们指示SQLite简单地将数据切换到OS进行写入然后继续.如果计算机在将数据写入盘片之前遭受灾难性崩溃(或电源故障),则数据库文件可能会损坏:
/* Open the database and create the schema */ sqlite3_open(DATABASE, &db); sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg); sqlite3_exec(db, "PRAGMA synchronous = OFF", NULL, NULL, &sErrMsg);
在12.41秒内导入864913条记录
这些改进现在变小了,但我们每秒最多可达69,600次插入.
考虑通过评估将回滚日志存储在内存中PRAGMA journal_mode = MEMORY.您的交易速度会更快,但如果您在交易过程中断电或程序崩溃,您的数据库可能会因部分完成的交易而处于损坏状态:
/* Open the database and create the schema */ sqlite3_open(DATABASE, &db); sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg); sqlite3_exec(db, "PRAGMA journal_mode = MEMORY", NULL, NULL, &sErrMsg);
在13.50秒内导入864913条记录
比之前的优化速度慢一点,每秒64,000次插入.
让我们结合前两个优化.这有点风险(如果发生崩溃),但我们只是导入数据(不运行银行):
/* Open the database and create the schema */ sqlite3_open(DATABASE, &db); sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg); sqlite3_exec(db, "PRAGMA synchronous = OFF", NULL, NULL, &sErrMsg); sqlite3_exec(db, "PRAGMA journal_mode = MEMORY", NULL, NULL, &sErrMsg);
在12.00秒内导入864913条记录
太棒了!我们每秒可以进行72,000次插入.
只是为了解决问题,让我们基于所有以前的优化并重新定义数据库文件名,以便我们完全在RAM中工作:
#define DATABASE ":memory:"
在10.94秒内导入864913条记录
将我们的数据库存储在RAM中并不是非常实用,但令人印象深刻的是我们每秒可以执行79,000次插入.
虽然不是特别的SQLite改进,但我不喜欢循环中的额外char*赋值操作while.让我们快速重构该代码以strtok()直接传递输出sqlite3_bind_text(),让编译器尝试为我们加速:
pFile = fopen (INPUTDATA,"r");
while (!feof(pFile)) {
fgets (sInputBuf, BUFFER_SIZE, pFile);
sqlite3_bind_text(stmt, 1, strtok (sInputBuf, "\t"), -1, SQLITE_TRANSIENT); /* Get Route */
sqlite3_bind_text(stmt, 2, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT); /* Get Branch */
sqlite3_bind_text(stmt, 3, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT); /* Get Version */
sqlite3_bind_text(stmt, 4, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT); /* Get Stop Number */
sqlite3_bind_text(stmt, 5, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT); /* Get Vehicle */
sqlite3_bind_text(stmt, 6, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT); /* Get Date */
sqlite3_bind_text(stmt, 7, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT); /* Get Time */
sqlite3_step(stmt); /* Execute the SQL Statement */
sqlite3_clear_bindings(stmt); /* Clear bindings */
sqlite3_reset(stmt); /* Reset VDBE */
n++;
}
fclose (pFile);
注意:我们回到使用真正的数据库文件.内存数据库很快,但不一定实用
在8.94秒内导入864913条记录
对参数绑定中使用的字符串处理代码进行轻微重构,使我们每秒执行96,700次插入.我认为可以说这很快就可以了.当我们开始调整其他变量(即页面大小,索引创建等)时,这将成为我们的基准.
我希望你还在我身边!我们开始走这条道路的原因是,SQLite的批量插入性能变化如此之大,并且并不总是很明显需要做出哪些改变来加速我们的操作.使用相同的编译器(和编译器选项),相同版本的SQLite和相同的数据,我们优化了我们的代码和SQLite的使用,从最坏情况下每秒85次插入到每秒超过96,000次插入!
在我们开始衡量SELECT绩效之前,我们知道我们将创建索引.在下面的一个答案中已经建议,在进行批量插入时,在插入数据之后创建索引会更快(而不是先创建索引然后插入数据).我们试试吧:
创建索引然后插入数据
sqlite3_exec(db, "CREATE INDEX 'TTC_Stop_Index' ON 'TTC' ('Stop')", NULL, NULL, &sErrMsg);
sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);
...
在18.13秒内导入864913条记录
插入数据然后创建索引
...
sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &sErrMsg);
sqlite3_exec(db, "CREATE INDEX 'TTC_Stop_Index' ON 'TTC' ('Stop')", NULL, NULL, &sErrMsg);
在13.66秒内导入864913条记录
正如预期的那样,如果对一列进行索引,批量插入会更慢,但如果在插入数据后创建索引,则确实会产生差异.我们的无指数基线是每秒96,000次插入.首先创建索引然后插入数据每秒给我们47,700个插入,而先插入数据然后创建索引每秒给我们63,300个插入.
我很乐意接受其他方案尝试的建议......并且很快就会为SELECT查询编译类似的数据.
几个提示:
将插入/更新放入事务中.
对于旧版本的SQLite - 考虑一个不太偏执的日志模式(pragma journal_mode).有NORMAL,再有就是OFF,它可以显著提高插入速度,如果你不是太担心数据库可能得到破坏,如果操作系统崩溃.如果您的应用程序崩溃,数据应该没问题.请注意,在较新版本中,OFF/MEMORY设置对于应用程序级别崩溃是不安全的.
使用页面大小也会产生影响(PRAGMA page_size).具有较大的页面大小可以使读取和写入更快,因为较大的页面保存在内存中.请注意,您的数据库将使用更多内存.
如果您有索引,请考虑CREATE INDEX在完成所有插入后调用.这比创建索引然后执行插入要快得多.
如果您具有对SQLite的并发访问权限,则必须非常小心,因为在完成写入时整个数据库都被锁定,尽管可能有多个读取器,但写入将被锁定.通过在较新的SQLite版本中添加WAL,这有所改善.
利用节省空间...较小的数据库更快.例如,如果您有键值对,请尝试在INTEGER PRIMARY KEY可能的情况下创建键,这将替换表中隐含的唯一行号列.
如果您使用多个线程,则可以尝试使用共享页面缓存,这将允许在线程之间共享加载的页面,这可以避免昂贵的I/O调用.
不要用!feof(file)!
我也在这里和这里问过类似的问题.
尝试使用SQLITE_STATIC而不是SQLITE_TRANSIENT那些插入.
SQLITE_TRANSIENT 将导致SQLite在返回之前复制字符串数据.
SQLITE_STATIC告诉它你给它的内存地址将有效,直到执行了查询(在这个循环中总是如此).这将为每个循环节省几次分配,复制和取消分配操作.可能是一个很大的改进.
避免使用sqlite3_clear_bindings(stmt);
测试中的代码每次都应该设置绑定就足够了.
来自SQLite文档的C API简介说
在第一次调用sqlite3_step()之前或在sqlite3_reset()之后立即调用之前,应用程序可以调用其中一个sqlite3_bind()接口来将值附加到参数.每次调用sqlite3_bind()都会覆盖对同一参数的先前绑定
(参见:sqlite.org/cintro.html).该函数的文档中没有任何内容表示除了简单地设置绑定外,还必须调用它.
更多细节:http://www.hoogli.com/blogs/micro/index.html#Avoid_sqlite3_clear_bindings()
灵感来自这篇文章以及引发我的Stack Overflow问题 - 是否可以在SQLite数据库中一次插入多行？- 我发布了我的第一个Git存储库:
https://github.com/rdpoor/CreateOrUpdate
它将一系列ActiveRecords批量加载到MySQL,SQLite或PostgreSQL数据库中.它包括一个忽略现有记录,覆盖它们或引发错误的选项.我的基本测试表明,与顺序写入相比,速度提高了10倍--YMMV.
我在生产代码中使用它,我经常需要导入大型数据集,我对此非常满意.
如果您可以对INSERT/UPDATE语句进行分块,则批量导入似乎表现最佳.在一张只有几行的桌子上,价值10,000左右的我一直很好,YMMV ...
如果你只关心阅读,有点快(但可能会读取过时的数据)版本是从多个线程的多个连接读取(每个线程连接).
首先找到表中的项目:
SELECT COUNT(*) FROM table
然后读入页面(LIMIT/OFFSET)
SELECT * FROM table ORDER BY _ROWID_ LIMITOFFSET
在哪里和每个线程计算,像这样:
int limit = (count + n_threads - 1)/n_threads;
对于每个线程:
int offset = thread_index * limit
对于我们的小型(200mb)数据库,这使得速度提高了50-75%(Windows 7上为3.8.0.2 64位).我们的表非常非标准化(1000-1500列,大约100,000行或更多行).
线程太多或太少都无法做到,您需要自己进行基准测试和分析.
同样对我们来说,SHAREDCACHE使性能变慢,所以我手动放置PRIVATECACHE(因为它是全局启用的)
在我将cache_size提升到更高的值之前,我不能从事务中获得任何收益 PRAGMA cache_size=10000;
阅读本教程后,我尝试将其实现到我的程序中.
我有4-5个包含地址的文件.每个文件有大约3000万条记录.我使用的是你建议的相同配置,但我每秒的INSERT数量很低(每秒约10.000条记录).
这是您的建议失败的地方.您对所有记录使用单个事务,并且没有错误/失败的单个插入.假设您将每个记录拆分为不同表上的多个插入.如果记录被破坏会怎么样？
ON CONFLICT命令不适用,因为如果记录中有10个元素,并且需要将每个元素插入到不同的表中,如果元素5出现CONSTRAINT错误,那么之前的所有4个插入也需要.
所以这里是回滚的来源.回滚的唯一问题是您丢失了所有插入并从顶部开始.你怎么解决这个问题？
我的解决方案是使用多个交易.我每隔10.000条记录开始和结束一次交易(不要问为什么这个数字,这是我测试过的最快的数字).我创建了一个10.000的数组,并在那里插入成功的记录.当错误发生时,我做一个回滚,开始一个事务,从我的数组中插入记录,提交然后在破坏的记录后开始一个新的事务.
这个解决方案帮助我绕过了处理包含错误/重复记录的文件时遇到的问题(我的记录差不多有4%).
我创建的算法帮助我减少了2个小时的过程.文件1小时30米的最终加载过程仍然很慢,但与最初的4小时相比没有.我设法将插入速度从10.000/s加速到~14.000/s
如果有人对如何加快它有任何其他想法,我愿意接受建议.
更新:
除了上面的回答,你应该记住,每秒插入次数取决于你使用的硬盘驱动器.我在具有不同硬盘驱动器的3台不同PC上进行了测试,并且在时间上有很大差异.PC1(1小时30分钟),PC2(6小时)PC3(14小时),所以我开始想知道为什么会这样.
经过两周的研究和检查多个资源:硬盘,Ram,缓存,我发现硬盘上的某些设置会影响I/O速率.通过单击所需输出驱动器上的属性,您可以在常规选项卡中看到两个选项.Opt1:压缩此驱动器,Opt2:允许此驱动器的文件将内容编入索引.
通过禁用这两个选项,现在所有3台PC都需要大约相同的时间才能完成(1小时和20到40分钟).如果遇到慢速插入,请检查您的硬盘驱动器是否配置了这些选项.它将为您节省大量时间和麻烦,试图找到解决方案
您的问题的答案是较新的sqlite3提高了性能,使用它.
这个答案为什么SQLAlchemy插入sqlite比直接使用sqlite3慢25倍？通过SqlAlchemy Orm作者在0.5秒内有100k插入,我在python-sqlite和SqlAlchemy中看到了类似的结果.这让我相信sqlite3的性能有所提升
京公网安备 11010802040832号 | 京ICP备19059560号-6 