优化SQLite是棘手的。C应用程序的大容量插入性能可以从每秒85次插入到每秒96,000次以上插入!
背景:我们正在将SQLite用作桌面应用程序的一部分。我们将大量配置数据存储在XML文件中,这些文件会被解析并加载到SQLite数据库中,以便在初始化应用程序时进行进一步处理。SQLite非常适合这种情况,因为它速度快,不需要专门的配置,并且数据库作为单个文件存储在磁盘上。
基本原理: 最初,我对看到的性能感到失望。事实证明,取决于数据库的配置方式和使用API的方式,SQLite的性能可能会发生很大的变化(对于大容量插入和选择而言)。弄清楚所有选项和技术是什么都不是一件容易的事,因此,我认为创建此社区Wiki条目与读者共享结果以节省其他人的麻烦是审慎的做法。
实验:我认为,最好是编写一些C代码并实际衡量各种选择的影响,而不是简单地谈论一般意义上的性能提示(即“使用事务!”)。我们将从一些简单的数据开始:
28 MB TAB分隔的文本文件(约865,000条记录),用于多伦多市的完整运输时间表 我的测试计算机是运行Windows XP的3.60 GHz P4。 使用Visual C ++ 2005将代码编译为“发布”,“完全优化”(/ Ox)和“最爱快速代码”(/ Ot)。 我正在使用直接编译到测试应用程序中的SQLite“合并”。我刚好拥有的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 <stdio.h> #include <stdlib.h> #include <time.h> #include <string.h> #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条记录
kes!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:
sqlite3_prepare_v2
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次插入。
PRAGMA同步= OFF 默认情况下,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 =OFF 考虑通过评估将回滚日志存储在内存中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次插入的速度比以前的优化稍慢。
PRAGMA synchronous = OFF and 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 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次插入。
重构C代码 尽管没有专门对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次插入!
先创建索引,然后插入VS.插入,然后创建索引 在开始衡量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查询编译类似的数据。
几个技巧: