提高SQLite的每秒插入数性能

优化SQLite很棘手。C应用程序的大容量插入性能可以从每秒85次插入到每秒96000次以上

背景:我们正在使用SQLite作为桌面应用程序的一部分。我们有大量的配置数据存储在XML文件中,这些文件被解析并加载到SQLite数据库中,以便在应用程序初始化时进行进一步处理。SQLite非常适合这种情况,因为它速度快,不需要专门的配置,并且数据库作为单个文件存储在磁盘上

理由:起初,我对我看到的性能感到失望。事实证明,SQLite的性能可能会有很大的差异(对于批量插入和选择),这取决于数据库的配置方式和API的使用方式。弄清楚所有的选项和技术都是什么并不是一件小事,所以我认为创建这个社区wiki条目与堆栈溢出读者共享结果是明智的,这样可以避免其他人在同样的调查中遇到麻烦

实验:与其简单地谈论一般意义上的性能提示(即使用事务!),我认为最好编写一些C代码,并实际测量各种选项的影响。我们将从一些简单的数据开始:

  • 多伦多市完整交通计划的28 MB制表符分隔文本文件(约865000条记录)
  • 我的测试机器是一台运行Windows XP的3.60 GHz P4
  • 代码采用Visual C++ 2005编写;“释放”;加上;“全面优化”;(/Ox)和快速代码(/Ot)
  • 我使用的是SQLite“;“合并”;,直接编译到我的测试应用程序中。我碰巧拥有的SQLite版本有点旧(3.6.7),但我怀疑这些结果将与最新版本相当(如果您不这么认为,请留下评论)

让我们写一些代码吧

代码:一个简单的C程序,它逐行读取文本文件,将字符串拆分为值,然后将数据插入SQLite数据库。在这个”,;“基线”;代码的版本,创建了数据库,但我们不会实际插入数据:

/*************************************************************
测试SQLite性能的基线代码。
输入数据是以28 MB制表符分隔的文本文件
填写多伦多交通系统时间表/路线信息
从…起http://www.toronto.ca/open/datasets/ttc-routes/
**************************************************************/
#包括<标准h>
#包括<stdlib.h>
#包括<时间;
#包括<字符串.h>
#包括「;sqlite3.h“;
#定义输入数据“;C:\\TTC\U schedule\U scheduleitem\U 10-27-2009.txt“;
#“定义数据库”;c:\\TTC\U进度计划项目\U 10-27-2009.sqlite“;
#定义表格“;如果不存在TTC(id整数主键、路线id文本、分支代码文本、版本整数、停止整数、车辆索引整数、日期整数、时间文本),则创建表格;
#定义缓冲区大小256
int main(int argc,字符**argv){
sqlite3*db;
sqlite3_stmt*stmt;
char*sErrMsg=0;
char*tail=0;
int nRetCode;
int n=0;
时钟&u t cStartClock;
文件*pFile;
char sInputBuf[缓冲区大小]=“0”;;
char*sRT=0;/*路由*/
char*sBR=0;/*分支*/
char*sVR=0;/*版本*/
字符*sST=0;/*停止编号*/
char*sVI=0;/*车辆*/
char*sDT=0;/*日期*/
char*sTM=0;/*时间*/
char sSQL[BUFFER_SIZE]=“0”;;
/*********************************************/
/*打开数据库并创建模式*/
sqlite3_open(数据库和数据库);
sqlite3_exec(db、TABLE、NULL、NULL和sErrMsg);
/*********************************************/
/*打开输入文件并导入数据库*/
cStartClock=时钟();
pFile=fopen(输入数据,“r”);
而(!feof(pFile)){
fgets(sInputBuf、缓冲区大小、pFile);
sRT=strtok(sInputBuf,“\t”;/*获取路线*/
sBR=strtok(NULL,"\t";/*Get分支*/
sVR=strtok(NULL,“t”;/*获取版本*/
sST=strtok(空,"\t");/*获取站点号*/
sVI=strtok(空,“\t”;/*获取车辆*/
sDT=strtok(NULL,“\t”;/*获取日期*/
sTM=strtok(NULL,“\t”;/*获取时间*/
/*实际插入将在此处进行*/
n++;
}
fclose(pFile);
printf(“导入的%d条记录在%4.2f秒\n”中,n,(时钟()-cStartClock)/(双)时钟/u/秒);
sqlite3_关闭(db);
返回0;
}

“;“控制”

按原样运行代码实际上不会执行任何数据库操作,但它会让我们了解原始C文件I/O和字符串处理操作的速度

在0.94中导入了864913条记录

太好了!我们可以每秒进行920000次插入,前提是我们实际上不进行任何插入:-)


“;“最坏情况”

我们将使用从文件读取的值生成SQL字符串,并使用sqlite3_exec调用该SQL操作:

sprintf(sSQL,“插入到TTC值中(NULL、“%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,“begintransaction”,NULL,NULL,“sErrMsg”);
pFile=fopen(输入数据,“r”);
而(!feof(pFile)){
...
}
fclose(pFile);
sqlite3_exec(db,“结束事务”,NULL,NULL,sErrMsg);

在38.03中导入了864913条记录

那更好。在一个事务中简单地包装我们的所有插入,将我们的性能提高到每秒23000个插入。

使用事先准备好的语句

使用事务是一个巨大的改进,但是如果我们反复使用相同的SQL,那么为每个插入重新编译SQL语句是没有意义的。让我们使用sqlite3\u prepare\u v2编译一次SQL语句,然后使用sqlite3\u bind\u text将参数绑定到该语句:

/*打开输入文件并导入数据库*/
cStartClock=时钟();
sprintf(sSQL,“插入到TTC值中(NULL、@RT、@BR、@VR、@ST、@VI、@DT、@TM)”;
sqlite3\u prepare\u v2(db、sSQL、缓冲区大小、stmt和tail);
sqlite3_exec(db,“begintransaction”,NULL,NULL,sErrMsg);
pFile=fopen(输入数据,“r”);
而(!feof(pFile)){
fgets(sInputBuf、缓冲区大小、pFile);
sRT=strtok(sInputBuf,“\t”;/*获取路线*/
sBR=strtok(NULL,"\t";/*Get分支*/
sVR=strtok(NULL,“t”;/*获取版本*/
sST=strtok(空,"\t");/*获取站点号*/
sVI=strtok(空,“\t”;/*获取车辆*/
sDT=strtok(NULL,“\t”;/*获取日期*/
sTM=strtok(NULL,“\t”;/*获取时间*/
sqlite3_绑定_文本(stmt,1,sRT,-1,SQLITE_瞬态);
sqlite3绑定文本(stmt,2,sBR,-1,SQLITE临时);
sqlite3绑定文本(stmt,3,sVR,-1,sqlite3瞬态);
sqlite3_绑定_文本(stmt,4,sST,-1,SQLITE_瞬态);
sqlite3_绑定_文本(stmt,5,sVI,-1,SQLITE_瞬态);
sqlite3绑定文本(stmt,6,sDT,-1,SQLITE);
sqlite3_绑定_文本(stmt,7,sTM,-1,SQLITE_瞬态);
sqlite3_步骤(stmt);
sqlite3_清除_绑定(stmt);
sqlite3_重置(stmt);
n++;
}
fclose(pFile);
sqlite3_exec(db,“结束事务”,NULL,NULL,sErrMsg);
printf(“导入的%d条记录在%4.2f秒\n”中,n,(时钟()-cStartClock)/(双)时钟/u/秒);
sqlite3_最终确定(stmt);
sqlite3_关闭(db);
返回0;

在16.27中导入864913条记录

很好!还有一些代码(别忘了调用sqlite3\u clear\u绑定sqlite3\u重置),但我们的性能提高了一倍多,达到每秒53000次插入。

PRAGMA同步=关闭

默认情况下,SQLite将在发出操作系统级写入命令后暂停。这保证了数据被写入磁盘。通过设置synchronous=OFF,我们指示SQLite将数据简单地交给操作系统进行写入,然后继续。如果在将数据写入盘片之前计算机发生灾难性崩溃(或电源故障),则数据库文件可能会损坏:

/*打开数据库并创建架构*/
sqlite3_open(数据库和数据库);
sqlite3_exec(db、TABLE、NULL、NULL和sErrMsg);
sqlite3_exec(db,“PRAGMA synchronous=OFF”,NULL,NULL,sErrMsg);

在12.41中导入了864913条记录

现在改进更小了,但每秒插入的次数已达到69600次。

PRAGMA journal_mode=内存

考虑通过计算PRAGMA journal\u mode=memory将回滚日志存储在内存中。您的事务处理速度会更快,但如果断电或程序崩溃

发表评论