对比sqlite3_exec 和sqlite3_bind 插入100万行数据的速度 with BEGIN TRANSACTION using C++ and SQLite

Sqlite 前端之家
前端之家收集整理的这篇文章主要介绍了对比sqlite3_exec 和sqlite3_bind 插入100万行数据的速度 with BEGIN TRANSACTION using C++ and SQLite，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

使用sqlite3_exec 插入100万行数据需要 27 s，而使用sqlite3_bind_double 插入100万行数据只需要3.7 s。

主要是因为采用sqlite3_exec()，相当于每插入一行数据同时用到sqlite3_prepare_v2(),sqlite3_step() 和sqlite3_finalize()，另外需要把double 强制转换成 string 然后再转换成 const char*，这也需要耗费时间；而如果采用sqlite3_bind_double来加入数据，只要用到sqlite3_prepare_v2()，然后不断地使用sqlite3_step() 和 sqlite3_reset()；并且不需要数据类型的转换。

当然，BEGIN TRANSACTION 的功能居功至伟。如果把sqlite3_exec(database,"BEGIN TRANSACTION;",NULL,&errmsg); 和sqlite3_exec(database,"COMMIT TRANSACTION;",NULL); 这两行注释掉，那么上述两种方法将耗费大量的时间；需要几分钟吧？

关于不同插入方法对插入速度的影响，见http://www.sqlite.org/faq.html#q19 中的“(19) INSERT is really slow - I can only do few dozen INSERTs per second”



下面是两种类型的代码：


使用sqlite3_exec 插入100万行数据

[cpp] view plain copy print ?
  1. #include<iostream>
  2. #include<iostream>
  3. #include"sqlite3.h"
  4. #include<string.h>
  5. #include<stdio.h>
  6. #include<sys/time.h>
  7. #include<boost/lexical_cast.hpp>
  10. usingnamespacestd;
  11. usingnamespaceboost;
  13. intfirst_row;
  14. sqlite3*database;
  16. //callbackfunction;
  17. intselect_callback(void*p_data,intnum_fields,char**p_fields,char**p_col_names)
  18. {
  19. inti;
  20. int*nof_records=(int*)p_data;
  21. (*nof_records)++;
  23. //first_rowwasdefinedin<select_stmt>function;
  24. //iffirst_row==1,printthefirstrow
  25. //andthensetfirst_row=0toavoidthesubsequentexecutionforthefollowingrows.
  26. if(first_row==1)
  27. {
  28. first_row=0;
  29. for(i=0;i<num_fields;i++)
  30. {
  31. //printf("%20s",p_col_names[i]);
  32. }
  33. //printf("\n");
  34. for(i=0;i<num_fields*10;i++)
  35. {
  36. //printf("=");
  37. }
  38. //printf("\n");
  39. }
  41. for(i=0;i<num_fields;i++)
  42. {if(p_fields[i])
  43. {
  44. //printf("%20s",p_fields[i]);
  45. }
  46. else
  47. {
  48. //printf("%20s","");
  49. }
  50. }
  52. //printf("\n");
  53. return0;
  54. }
  57. //Withcallbackfunction;
  58. voidselect_stmt(constchar*stmt)
  59. {char*errmsg;
  60. intret;
  61. intnrecs=0;
  62. first_row=1;
  64. ret=sqlite3_exec(database,stmt,select_callback,&nrecs,&errmsg);
  66. if(ret!=sqlITE_OK)
  67. {printf("Errorinselectstatement%s[%s].\n",errmsg);
  68. }
  69. else
  70. {printf("\n%drecordsreturned.\n",nrecs);
  71. }
  72. }
  75. //timecaculation
  76. longtimecacul(){
  77. structtimevaltv;
  78. structtimezonetz;
  79. gettimeofday(&tv,&tz);
  80. return(tv.tv_sec*1000+tv.tv_usec/1000);
  81. }
  83. intmain()
  84. {longstarttime,endtime,resulttime;
  89. char*errmsg;
  90. sqlite3_open("./Database.db",&database);
  92. //sqlite3_exec(database,"PRAGMAsynchronous=OFF",&errmsg);
  95. sqlite3_stmt*stmt;
  96. strings="createtablewujie(xdecimal(5,2),ydecimal(5,zdecimal(5,2))";
  97. constchar*creatTable=s.c_str();
  98. cout<<"creatTable:"<<creatTable<<endl;
  100. //charcreatTable[]="createtablewujie(a,b,c)";
  103. intresult=sqlite3_exec(database,
  104. creatTable,//stmt
  105. 0,
  106. 0,
  107. &errmsg
  108. );
  109. if(result!=sqlITE_OK)
  110. {cout<<"\nCouldnotpreparestatement:creatTable:"<<result<<endl;
  111. return1;
  112. }
  115. ////////BEGINTRANSACTION
  116. starttime=timecacul();
  117. sqlite3_exec(database,"BEGINTRANSACTION;",&errmsg);
  120. stringinsertDataStr;
  121. doublex,y,z;
  122. doubleyTimes=1.222222222;
  123. intiNum;
  124. for(iNum=1;iNum<=1000000;iNum++)
  125. {x=1*iNum;
  126. y=yTimes*iNum;
  127. z=2*iNum;
  128. insertDataStr="insertintowujieVALUES("
  129. +lexical_cast<string>(x)+","
  130. +lexical_cast<string>(y)+","
  131. +lexical_cast<string>(z)+")";
  132. //cout<<"insertDataStr:"<<insertDataStr<<endl;
  133. constchar*insertDataChar=insertDataStr.c_str();
  135. result=sqlite3_exec
  136. (database,
  137. insertDataChar,//stmt
  138. 0,
  139. 0,
  140. &errmsg
  141. );
  143. if(result!=sqlITE_OK)
  144. {cout<<"\nCouldnotpreparestatement:inserData:"<<result<<endl;
  145. return1;
  146. }
  147. }
  149. sqlite3_exec(database,"COMMITTRANSACTION;",NULL);
  150. endtime=timecacul();
  151. resulttime=endtime-starttime;
  153. printf("NOAUTOCOMMITINSERT：%dms.",resulttime);
  154. cout<<endl;
  157. charselectData[]="Selectx,zfromwujie";
  158. starttime=timecacul();
  159. select_stmt(selectData);
  160. endtime=timecacul();
  161. resulttime=endtime-starttime;
  162. printf("Selectsqltime：%dms.",resulttime);
  164. sqlite3_close(database);
  166. return0;
  167. }


使用sqlite3_bind_double 插入100万行数据

[html] view plain copy print ?
  1. #include<iostream>
  2. #include<iostream>
  3. #include"sqlite3.h"
  4. #include<string.h>
  5. #include<stdio.h>
  6. #include<sys/time.h>
  7. #include<boost/lexical_cast.hpp>
  10. usingnamespacestd;
  11. usingnamespaceboost;
  13. intfirst_row;
  14. sqlite3*database;
  16. //callbackfunction;
  17. intselect_callback(void*p_data,intnum_fields,char**p_fields,char**p_col_names)
  18. {
  19. inti;
  20. int*nof_records=(int*)p_data;
  21. (*nof_records)++;
  23. //first_rowwasdefinedin<select_stmt>function;
  24. //iffirst_row==1,printthefirstrow
  25. //andthensetfirst_row=0toavoidthesubsequentexecutionforthefollowingrows.
  26. if(first_row==1)
  27. {
  28. first_row=0;
  29. for(i=0;i<num_fields;i++)
  30. {
  31. //printf("%20s",p_col_names[i]);
  32. }
  33. printf("\n");
  34. for(i=0;i<num_fields*10;i++)
  35. {
  36. //printf("=");
  37. }
  38. //printf("\n");
  39. }
  41. for(i=0;i<num_fields;i++)
  42. {if(p_fields[i])
  43. {
  44. //printf("%20s",p_fields[i]);
  45. }
  46. else
  47. {
  48. //printf("%20s","");
  49. }
  50. }
  52. //printf("\n");
  53. return0;
  54. }
  56. //Withcallbackfunction;
  57. voidselect_stmt(constchar*stmt)
  58. {char*errmsg;
  59. intret;
  60. intnrecs=0;
  61. first_row=1;
  62. ret=sqlite3_exec(database,&errmsg);
  64. if(ret!=sqlITE_OK)
  65. {printf("Errorinselectstatement%s[%s].\n",errmsg);
  66. }
  67. else
  68. {printf("\n%drecordsreturned.\n",nrecs);
  69. }
  70. }
  73. //timecaculation
  74. longtimecacul(){
  75. structtimevaltv;
  76. structtimezonetz;
  77. gettimeofday(&tv,&tz);
  78. return(tv.tv_sec*1000+tv.tv_usec/1000);
  79. }
  82. intmain()
  83. {longstarttime,resulttime;
  85. char*errmsg;
  86. sqlite3_open("./Database.db",&database);
  88. sqlite3_stmt*stmt;
  90. strings="createtablewujie(x,z)";
  91. constchar*creatTable=s.c_str();
  92. //cout<<"creatTable:"<<creatTable<<endl;
  94. intresult=sqlite3_exec(database,
  95. creatTable,//stmt
  96. 0,
  97. &errmsg
  98. );
  99. if(result!=sqlITE_OK)
  100. {cout<<"\nCouldnotpreparestatement:creatTable:"<<result<<endl;
  101. return1;
  102. }
  105. if(sqlite3_prepare
  106. (database,
  107. "insertintowujievalues(:x,:y,:z)",//stmt
  108. -1,//Ifthanzero,thenstmtisreaduptothefirstnulterminator
  109. &stmt,
  110. 0//Pointertounusedportionofstmt
  111. )
  112. !=sqlITE_OK)
  113. {printf("\nCouldnotpreparestatement.");
  114. return1;
  115. }
  117. intindex1,index2,index3;
  118. index1=sqlite3_bind_parameter_index(stmt,":x");
  119. index2=sqlite3_bind_parameter_index(stmt,":y");
  120. index3=sqlite3_bind_parameter_index(stmt,":z");
  122. //cout<<index1<<endl;
  123. //cout<<index2<<endl;
  124. //cout<<index3<<endl;
  126. printf("\nThestatementhas%dwildcards\n",sqlite3_bind_parameter_count(stmt));
  129. starttime=timecacul();
  130. sqlite3_exec(database,"BEGINTRANSACTION;",&errmsg);
  131. doublex,z;
  132. doubleyTimes=1.222222222;
  133. intiNum;
  134. for(iNum=1;iNum<=1000000;iNum++)
  135. {x=1*iNum;
  136. y=yTimes*iNum;
  137. z=2*iNum;
  139. if(sqlite3_bind_double(stmt,
  140. index1,//Indexofwildcard
  141. x
  142. )
  143. !=sqlITE_OK)
  144. {printf("\nCouldnotbinddouble.\n");
  145. return1;
  146. }
  148. if(sqlite3_bind_double(stmt,
  149. index2,//Indexofwildcard
  150. y
  151. )
  152. !=sqlITE_OK)
  153. {printf("\nCouldnotbinddouble.\n");
  154. return1;
  155. }
  157. if(sqlite3_bind_double(stmt,
  158. index3,//Indexofwildcard
  159. z
  160. )
  161. !=sqlITE_OK)
  162. {printf("\nCouldnotbinddouble.\n");
  163. return1;
  164. }
  166. if(sqlite3_step(stmt)!=sqlITE_DONE)
  167. {printf("\nCouldnotstep(execute)stmt.\n");
  168. return1;
  169. }
  170. sqlite3_reset(stmt);
  172. }
  174. sqlite3_exec(database,"COMMITTRANSACTION;",NULL);
  175. endtime=timecacul();
  176. resulttime=endtime-starttime;
  177. printf("NOAUTOCOMMITINSERT：%dms.",resulttime);
  179. ///////////////////////////////////////////////
  180. starttime=timecacul();
  182. charselectData[]="Select*fromwujie";
  183. select_stmt(selectData);
  184. sqlite3_close(database);
  186. endtime=timecacul();
  187. resulttime=endtime-starttime;
  188. printf("NOAUTOCOMMITINSERT：%dms.",resulttime);
  190. return0;
  191. }
原文链接：https://www.f2er.com/sqlite/199866.html

猜你在找的Sqlite相关文章

SQLite学习笔记
安装 在Windows上安装SQLite。 访问官网下载下Precompliled Binaries for Windows的两个压...
windows上如何安装Sqlite
一、安装 下载地址：http://www.sqlite.org/download.html 将Precompiled Binaries for Wi...
【Android数据存储】SQLite使用实例(附源码)
实例: 会员信息管理 功能：1.查看数据库 2.清空数据库 3.增加会员 4.删除会员 5.更新会员 ...
SQLite数据库管理
SQLite3常用命令
加密你的SQLite
关于SQLite SQLite是一个轻量的、跨平台的、开源的数据库引擎，它的在读写效率、消耗总量、...
SQLite这么娇小可爱，不多了解点都不行啊
在我眼里，MySQL和Oracle是这样的 而SQLite在是这样的 所以这么萌的数据库，我真的应该多了...
SQLite这么娇小可爱，不多了解点都不行啊
在我眼里，MySQL和Oracle是这样的 而SQLite在是这样的 所以这么萌的数据库，我真的应该多了...
SQLite 这么娇小可爱，不多了解点都不行啊
（点击上方蓝字，快速关注我们) 作者：空之境界 网址：http://supermao.cn/sqlitezhe-yao-...
SQLITE3 使用总结
SQLITE3 使用总结 投稿：hebedich 字体：[ 增加  减小] 类型：转载 时间：2014-08-19 Sql...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问