提升SQLite数据插入效率低、速度慢的方法

转载 2016年08月31日 16:54:35

前言

SQLite数据库由于其简单、灵活、轻量、开源,已经被越来越多的被应用到中小型应用中。甚至有人说,SQLite完全可以用来取代c语言中的文件读写操作。因此我最近编写有关遥感数据处理的程序的时候,也将SQLite引入进来,以提高数据的结构化程度,并且提高大数据的处理能力(SQLite最高支持2PB大小的数据)。但是最开始,我发现,直接使用SQL语句的插入效率简直低的令人发指的。后来不断查文档、查资料,才发现了一条快速的“数据插入”之路。本文就以插入数据为例,整合网上和资料书中的各种提高SQLite效率的方法,给出提高SQLite数据插入效率的完整方法。(大神们勿喷)


1 数据

我使用的电脑是Win7 64位系统,使用VC2010编译,SQLIte版本为3.7.15.2 ,电脑CPU为二代i3处理器,内存6G。
实验之前,先建立要插入数据的表:
  1. create table t1 (id integer , x integer , y integer, weight real)  

慢速——最粗暴的方法

SQLite的API中直接执行SQL的函数是:
  1. int sqlite3_exec(  sqlite3*,    const char *sql,   int (*callback)(void*,int,char**,char**),   void *,   char **errmsg)  
直接使用INSERT语句的字符串进行插入,程序部分代码(完整代码见后文),如下:
  1. for(int i=0;i<nCount;++i)  
  2. {  
  3.     std::stringstream ssm;  
  4.     ssm<<"insert into t1 values("<<i<<","<<i*2<<","<<i/2<<","<<i*i<<")";  
  5.     sqlite3_exec(db,ssm.str().c_str(),0,0,0);  
  6. }  
这个程序运行的太慢了,我已经没时间等待了,估算了一下,基本上是 7.826 条/s

中速——显式开启事务

所谓”事务“就是指一组SQL命令,这些命令要么一起执行,要么都不被执行。在SQLite中,每调用一次sqlite3_exec()函数,就会隐式地开启了一个事务,如果插入一条数据,就调用该函数一次,事务就会被反复地开启、关闭,会增大IO量。如果在插入数据前显式开启事务,插入后再一起提交,则会大大提高IO效率,进而加数据快插入速度。
开启事务只需在上述代码的前后各加一句开启与提交事务的命令即可:

  1. sqlite3_exec(db,"begin;",0,0,0);  
  2. for(int i=0;i<nCount;++i)  
  3. {  
  4.     std::stringstream ssm;  
  5.     ssm<<"insert into t1 values("<<i<<","<<i*2<<","<<i/2<<","<<i*i<<")";  
  6.     sqlite3_exec(db,ssm.str().c_str(),0,0,0);  
  7. }  
  8. sqlite3_exec(db,"commit;",0,0,0);  

显式开启事务后,这个程序运行起来明显快很多,估算效率达到了34095条/s,较原始方法提升约5000倍。

高速——写同步(synchronous)

我要使用一个遥感处理算法处理10000*10000的影像,中间有一步需要插入100000000条数据到数据库中,如果按照开启事务后的速度34095条/s,则需要100000000÷34095 = 2932秒 = 48.9分,仍然不能够接受,所以我接着找提升速度的方法。终于,在有关讲解SQLite配置的资料中,看到了“写同步”选项。

在SQLite中,数据库配置的参数都由编译指示(pragma)来实现的,而其中synchronous选项有三种可选状态,分别是full、normal、off。这篇博客以及官方文档里面有详细讲到这三种参数的设置。简要说来,full写入速度最慢,但保证数据是安全的,不受断电、系统崩溃等影响,而off可以加速数据库的一些操作,但如果系统崩溃或断电,则数据库可能会损毁。

SQLite3中,该选项的默认值就是full,如果我们再插入数据前将其改为off,则会提高效率。如果仅仅将SQLite当做一种临时数据库的话,完全没必要设置为full。在代码中,设置方法就是在打开数据库之后,直接插入以下语句:

  1. sqlite3_exec(db,"PRAGMA synchronous = OFF; ",0,0,0);  
此时,经过测试,插入速度已经变成了41851条/s,也就是说,插入100000000条数据,需要2389秒 = 39.8分。

极速——执行准备

虽然写同步设为off后,速度又有小幅提升,但是仍然较慢。我又一次踏上了寻找提高SQLite插入效率方法的道路上。终于,我发现,SQLite执行SQL语句的时候,有两种方式:一种是使用前文提到的函数sqlite3_exec(),该函数直接调用包含SQL语句的字符串;另一种方法就是“执行准备”(类似于存储过程)操作,即先将SQL语句编译好,然后再一步一步(或一行一行)地执行。如果采用前者的话,就算开起了事务,SQLite仍然要对循环中每一句SQL语句进行“词法分析”和“语法分析”,这对于同时插入大量数据的操作来说,简直就是浪费时间。因此,要进一步提高插入效率的话,就应该使用后者。

“执行准备”主要分为三大步骤:

1.调用函数

  1. int sqlite3_prepare_v2( sqlite3 *db,  const char *zSql,  int nByte,  sqlite3_stmt **ppStmt,  const char **pzTail);  
并且声明一个指向sqlite3_stmt对象的指针,该函数对参数化的SQL语句zSql进行编译,将编译后的状态存入ppStmt中。

2.调用函数 sqlite3_step() ,这个函数就是执行一步(本例中就是插入一行),如果函数返回的是SQLite_ROW则说明仍在继续执行,否则则说明已经执行完所有操作;

3.调用函数 sqlite3_finalize(),关闭语句。

关于执行准备的API的具体语法,详见官方文档。本文中执行准备的c++代码如下:

  1. sqlite3_exec(db,"begin;",0,0,0);  
  2.     sqlite3_stmt *stmt;  
  3.     const char* sql = "insert into t1 values(?,?,?,?)";  
  4.     sqlite3_prepare_v2(db,sql,strlen(sql),&stmt,0);  
  5.       
  6.     for(int i=0;i<nCount;++i)  
  7.     {         
  8.         sqlite3_reset(stmt);  
  9.         sqlite3_bind_int(stmt,1,i);  
  10.         sqlite3_bind_int(stmt,1,i*2);  
  11.         sqlite3_bind_int(stmt,1,i/2);  
  12.         sqlite3_bind_double(stmt,1,i*i);  
  13.     }  
  14.     sqlite3_finalize(stmt);  
  15.     sqlite3_exec(db,"commit;",0,0,0);  
此时测试数据插入效率为:265816条/s,也就是说,插入100000000条数据,需要376秒 = 6.27分。这个速度已经很满意了。

5 总结

综上所述啊,SQLite插入数据效率最快的方式就是:事务+关闭写同步+执行准备(存储过程),如果对数据库安全性有要求的话,就开启写同步。

参考资料:
1. SQLite官方文档:http://www.sqlite.org/docs.html
2.《解决sqlite3插入数据很慢的问题》:http://blog.csdn.net/victoryknight/article/details/7461703
3.《The Definitive Guide to SQLite》Apress出版:http://www.apress.com/9781430232254 (这是本好书)

附最终完整代码:

  1. #include <iostream>  
  2. #include <string>  
  3. #include <sstream>  
  4. #include <time.h>  
  5. #include "sqlite3.h"  
  6.   
  7. const int nCount = 500000;  
  8.       
  9. int main (int argc,char** argv)  
  10. {  
  11.     sqlite3* db;  
  12.     sqlite3_open("testdb.db" ,&db);  
  13.     sqlite3_exec(db,"PRAGMA synchronous = OFF; ",0,0,0);  
  14.     sqlite3_exec(db,"drop table if exists t1",0,0,0);  
  15.     sqlite3_exec(db,"create table t1(id integer,x integer,y integer ,weight real)",0,0,0);  
  16.     clock_t t1 = clock();  
  17.       
  18.     sqlite3_exec(db,"begin;",0,0,0);  
  19.     sqlite3_stmt *stmt;  
  20.     const char* sql = "insert into t1 values(?,?,?,?)";  
  21.     sqlite3_prepare_v2(db,sql,strlen(sql),&stmt,0);  
  22.       
  23.     for(int i=0;i<nCount;++i)  
  24.     {  
  25.         // std::stringstream ssm;  
  26.         // ssm<<"insert into t1 values("<<i<<","<<i*2<<","<<i/2<<","<<i*i<<")";  
  27.         // sqlite3_exec(db,ssm.str().c_str(),0,0,0);  
  28.         sqlite3_reset(stmt);  
  29.         sqlite3_bind_int(stmt,1,i);  
  30.         sqlite3_bind_int(stmt,2,i*2);  
  31.         sqlite3_bind_int(stmt,3,i/2);  
  32.         sqlite3_bind_double(stmt,4,i*i);  
  33.         sqlite3_step(stmt);  
  34.     }  
  35.     sqlite3_finalize(stmt);  
  36.     sqlite3_exec(db,"commit;",0,0,0);  
  37.     clock_t t2 = clock();  
  38.       
  39.     sqlite3_close(db);  
  40.       
  41.     std::cout<<"cost tima: "<<(t2-t1)/1000.<<"s"<<std::endl;  
  42.       
  43.     return 0;  
  44. }  

提升SQLite数据插入效率低、速度慢的方法

前言 SQLite数据库由于其简单、灵活、轻量、开源,已经被越来越多的被应用到中小型应用中。甚至有人说,SQLite完全可以用来取代c语言中的文件读写操作。因此我最近编写有关遥感数据处理的程序的...
  • majiakun1
  • majiakun1
  • 2015年06月23日 16:29
  • 31524

iOS 提升SQLite数据插入效率低、速度慢的方法

//联系人:石虎  QQ: 1224614774昵称:嗡嘛呢叭咪哄 前言 SQLite数据库由于其简单、灵活、轻量、开源,已经被越来越多的被应用到中小型应用中。甚至有人说,SQLi...
  • shihuboke
  • shihuboke
  • 2017年07月02日 23:04
  • 162

kettle 数据提取效率提升

最近发现KETTLE抽数越来越慢,特别是增量INSERT/UPDATE的时候,速度已经达到了令人发指的地步(从一个400W数据规模的表中每天增量量抽取30W数据的TRASFORMATION 竟然要20...
  • xpliruizhi123
  • xpliruizhi123
  • 2017年01月17日 10:34
  • 4955

[个人收藏]提升SQLite数据插入效率低、速度慢的方法

前言 SQLite数据库由于其简单、灵活、轻量、开源,已经被越来越多的被应用到中小型应用中。甚至有人说,SQLite完全可以用来取代c语言中的文件读写操作。因此我最近编写有关遥感数据处理的程序的...
  • ydb3448
  • ydb3448
  • 2016年07月15日 11:18
  • 132

Sqlite 大数据量删除问题

不知道大家有没有尝试过在Sqlite数据库中一次性删除上千条数据。你会发现删除函数返回后,但是数据并未删除。这是怎么回事喃?正是Sqlite这个轻量级数据库本身的缺陷,无法高速处理大数据量操作。不信引...
  • linjiebelfast
  • linjiebelfast
  • 2013年12月10日 16:31
  • 1326

解决sqlite3插入数据很慢的问题

初用sqlite3插入数据时,插入每条数据大概需要100ms左右。如果是批量导入,可以引进事物提高速度。但是假设你的业务是每间隔几秒插入几条数据,显然100ms是不能容许的。解决办法是,在调用sqli...
  • majiakun1
  • majiakun1
  • 2015年06月23日 16:29
  • 6319

Vector的效率问题

我们再来看看另外一个常用的Java类——java.util.Vector。简单地说,一个Vector就是一个java.lang.Object实例的数组。Vector与数组相似,它的元素可以通过整数形式...
  • carefreefly
  • carefreefly
  • 2007年05月14日 20:38
  • 1954

Android 往Sqlitedatabase中插入大量数据效率问题,40倍效率加速你的操作

情景:我现在要往sqlite database中的表中(R_USER_QUESTION_DO_HISTORY)插入一万条数据1.我的代码: public void setUpDataBaseForU...
  • Gpwner
  • Gpwner
  • 2016年11月27日 14:17
  • 1789

oracle 10046事件,解决dblink跨oracle server复制数据效率慢踩到的坑

最近机器学习很火,公司也想着往这方面做一些试水项目,想着从大量的历史数据中预测用户行为。由于需要用到大量的数据做分析,所以得从生产环境中获取这些数据进行相关的分析。那么问题来了,我们总不可能直接对生产...
  • sinat_19968265
  • sinat_19968265
  • 2017年04月18日 22:28
  • 467

php getimagesize 导致系统变慢

用这个函数获取图片宽高度 function getfilesize($url){     //利用PHP非常强大的一个内容包含协议data wrapper:http://php.net/manua...
  • remotesupport
  • remotesupport
  • 2012年07月03日 16:43
  • 1767
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:提升SQLite数据插入效率低、速度慢的方法
举报原因:
原因补充:

(最多只允许输入30个字)