对比sqlite3_exec 和sqlite3_bind 插入100万行数据的速度 with BEGIN TRANSACTION using C++ and SQLite
来源：互联网 发布：网络上办美术培训行吗 编辑：程序博客网 时间：2024/05/22 09:03
使用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,NULL,&errmsg); 和 sqlite3_exec(database,"COMMIT TRANSACTION;",NULL,NULL,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万行数据
#include <iostream>#include <iostream>#include "sqlite3.h"#include <string.h>#include <stdio.h>#include <sys/time.h>#include <boost/lexical_cast.hpp>using namespace std;using namespace boost;int first_row;sqlite3* database;// callback function;int select_callback(void *p_data, int num_fields, char **p_fields, char **p_col_names){ int i; int* nof_records = (int*) p_data; (*nof_records)++; // first_row was defined in <select_stmt> function; // if first_row == 1, print the first row // and then set first_row = 0 to avoid the subsequent execution for the following rows. if (first_row == 1) { first_row = 0; for (i=0; i < num_fields; i++) {// printf("%20s", p_col_names[i]); }// printf("\n"); for (i=0; i< num_fields*10; i++) {// printf("="); }// printf("\n"); } for(i=0; i < num_fields; i++) { if (p_fields[i]) {// printf("%20s", p_fields[i]); } else {// printf("%20s", " "); } }// printf("\n"); return 0;}// With callback function;void select_stmt(const char* stmt){ char *errmsg; int ret; int nrecs = 0; first_row = 1; ret = sqlite3_exec(database, stmt, select_callback, &nrecs, &errmsg); if(ret!=SQLITE_OK) { printf("Error in select statement %s [%s].\n", stmt, errmsg); } else { printf("\n %d records returned.\n", nrecs); }}//time caculationlong timecacul () { struct timeval tv; struct timezone tz; gettimeofday(&tv,&tz); return (tv.tv_sec * 1000 + tv.tv_usec / 1000);}int main(){ long starttime, endtime, resulttime; char *errmsg; sqlite3_open("./Database.db", &database);// sqlite3_exec(database, "PRAGMA synchronous = OFF", 0, 0, &errmsg); sqlite3_stmt* stmt; string s = "create table wujie (x decimal(5,2), y decimal(5,2), z decimal(5,2))"; const char *creatTable = s.c_str(); cout << "creatTable : " << creatTable << endl;// char creatTable[] = "create table wujie (a, b, c)"; int result = sqlite3_exec ( database, creatTable, // stmt 0, 0, &errmsg ); if ( result != SQLITE_OK ) { cout << "\nCould not prepare statement: creatTable: " << result << endl; return 1; } ////////BEGIN TRANSACTION starttime=timecacul(); sqlite3_exec(database,"BEGIN TRANSACTION;",NULL,NULL,&errmsg); string insertDataStr; double x, y, z; double yTimes = 1.222222222; int iNum; for ( iNum = 1; iNum <= 1000000; iNum++ ) { x = 1 * iNum; y = yTimes * iNum; z = 2 * iNum; insertDataStr = "insert into wujie VALUES(" + lexical_cast<string>(x) + "," + lexical_cast<string>(y) + "," + lexical_cast<string>(z) + ")";// cout << "insertDataStr: " << insertDataStr << endl; const char* insertDataChar = insertDataStr.c_str(); result = sqlite3_exec ( database, insertDataChar, // stmt 0, 0, &errmsg ); if ( result != SQLITE_OK ) { cout << "\nCould not prepare statement: inserData: " << result << endl; return 1; } } sqlite3_exec(database,"COMMIT TRANSACTION;",NULL,NULL,NULL); endtime=timecacul(); resulttime=endtime-starttime; printf("NO AUTOCOMMIT INSERT：%dms.",resulttime); cout << endl; char selectData[] = "Select x,y,z from wujie"; starttime=timecacul(); select_stmt(selectData); endtime=timecacul(); resulttime=endtime-starttime; printf("Select SQL time：%dms.",resulttime); sqlite3_close(database); return 0;}使用sqlite3_bind_double 插入100万行数据
#include <iostream>#include <iostream>#include "sqlite3.h"#include <string.h>#include <stdio.h>#include <sys/time.h>#include <boost/lexical_cast.hpp>using namespace std;using namespace boost;int first_row;sqlite3* database;// callback function;int select_callback(void *p_data, int num_fields, char **p_fields, char **p_col_names){ int i; int* nof_records = (int*) p_data; (*nof_records)++; // first_row was defined in <select_stmt> function; // if first_row == 1, print the first row // and then set first_row = 0 to avoid the subsequent execution for the following rows. if (first_row == 1) { first_row = 0; for (i=0; i < num_fields; i++) {// printf("%20s", p_col_names[i]); } printf("\n"); for (i=0; i< num_fields*10; i++) {// printf("="); }// printf("\n"); } for(i=0; i < num_fields; i++) { if (p_fields[i]) {// printf("%20s", p_fields[i]); } else {// printf("%20s", " "); } }// printf("\n"); return 0;}// With callback function;void select_stmt(const char* stmt){ char *errmsg; int ret; int nrecs = 0; first_row = 1; ret = sqlite3_exec(database, stmt, select_callback, &nrecs, &errmsg); if(ret!=SQLITE_OK) { printf("Error in select statement %s [%s].\n", stmt, errmsg); } else { printf("\n %d records returned.\n", nrecs); }}//time caculationlong timecacul () { struct timeval tv; struct timezone tz; gettimeofday(&tv,&tz); return (tv.tv_sec * 1000 + tv.tv_usec / 1000);}int main(){ long starttime, endtime, resulttime; char *errmsg; sqlite3_open("./Database.db", &database); sqlite3_stmt* stmt; string s = "create table wujie (x , y , z )"; const char *creatTable = s.c_str();// cout << "creatTable : " << creatTable << endl; int result = sqlite3_exec ( database, creatTable, // stmt 0, 0, &errmsg ); if ( result != SQLITE_OK ) { cout << "\nCould not prepare statement: creatTable: " << result << endl; return 1; } if ( sqlite3_prepare ( database, "insert into wujie values (:x,:y,:z)", // stmt -1, // If than zero, then stmt is read up to the first nul terminator &stmt, 0 // Pointer to unused portion of stmt ) != SQLITE_OK ) { printf("\nCould not prepare statement."); return 1; } int index1, index2, index3; index1 = sqlite3_bind_parameter_index(stmt, ":x"); index2 = sqlite3_bind_parameter_index(stmt, ":y"); index3 = sqlite3_bind_parameter_index(stmt, ":z");// cout << index1 << endl;// cout << index2 << endl;// cout << index3 << endl; printf("\nThe statement has %d wildcards\n", sqlite3_bind_parameter_count(stmt)); starttime=timecacul(); sqlite3_exec(database,"BEGIN TRANSACTION;",NULL,NULL,&errmsg); double x, y, z; double yTimes = 1.222222222; int iNum; for ( iNum = 1; iNum <= 1000000; iNum++ ) { x = 1 * iNum; y = yTimes * iNum; z = 2 * iNum; if (sqlite3_bind_double (stmt, index1, // Index of wildcard x ) != SQLITE_OK) { printf("\nCould not bind double.\n"); return 1; } if (sqlite3_bind_double (stmt, index2, // Index of wildcard y ) != SQLITE_OK) { printf("\nCould not bind double.\n"); return 1; } if (sqlite3_bind_double (stmt, index3, // Index of wildcard z ) != SQLITE_OK) { printf("\nCould not bind double.\n"); return 1; } if (sqlite3_step(stmt) != SQLITE_DONE) { printf("\nCould not step (execute) stmt.\n"); return 1; } sqlite3_reset(stmt); } sqlite3_exec(database,"COMMIT TRANSACTION;",NULL,NULL,NULL); endtime=timecacul(); resulttime=endtime-starttime; printf("NO AUTOCOMMIT INSERT：%dms.",resulttime); /////////////////////////////////////////////// starttime=timecacul(); char selectData[] = "Select * from wujie"; select_stmt(selectData); sqlite3_close(database); endtime=timecacul(); resulttime=endtime-starttime; printf("NO AUTOCOMMIT INSERT：%dms.",resulttime); return 0;}- 对比sqlite3_exec 和sqlite3_bind 插入100万行数据的速度 with BEGIN TRANSACTION using C++ and SQLite
- SQLite语法 BEGIN TRANSACTION
- SQLite语法 BEGIN TRANSACTION
- 提高上百万行数据insert速度的方法
- 提高上百万行数据insert速度的“经典”方法
- access数据库和SQLite数据库速度的简单对比
- SQLite BEGIN TRANSACTION创建提交事务 http://www.uedsc.com/sqlite-begin-transaction.html
- SQLite，加快插入数据的速度
- JDBC各种插入数据的速度对比
- 对比一下年薪1万10万和100万的生活
- 对比一下年薪1万10万和100万的生活
- Using libmemcached with C and C++
- Using Swift with Cocoa and Objetive-C
- Using Swift with Cocoa and Objective-C
- SQlite数据库的C编程接口（六） 返回值和错误码（Result Codes and Error Codes） ——《Using SQlite》读书笔记
- SQlite数据库的C编程接口（六） 返回值和错误码（Result Codes and Error Codes） ——《Using SQlite》读书笔记
- SQlite数据库的C编程接口（六） 返回值和错误码（Result Codes and Error Codes） ——《Using SQlite》读书笔记
- sqlite数据库，读写 sqlite3_exec
- Android获取SharedPreferences三种方式
- apache htpasswd 命令
- 单表，多条件查询之if else 语句
- Struts2的web.xml配置
- linux logo的格式
- 对比sqlite3_exec 和sqlite3_bind 插入100万行数据的速度 with BEGIN TRANSACTION using C++ and SQLite
- 日语输入法的输入规则
- java mysql存储过程
- 教你如何看懂photoshop中的直方图 让曝光达到完美
- java中不同数据库连接字符串
- 中国电信C+W策略分析
- 学习算法的一点体会
- ubuntu 添加用户
- sqlserver MMC 不能打开文件
