对比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 ?

#include<iostream>
#include<iostream>
#include"sqlite3.h"
#include<string.h>
#include<stdio.h>
#include<sys/time.h>
#include<boost/lexical_cast.hpp>
usingnamespacestd;
usingnamespaceboost;
intfirst_row;
sqlite3*database;
//callbackfunction;
intselect_callback(void*p_data,intnum_fields,char**p_fields,char**p_col_names)
{
inti;
int*nof_records=(int*)p_data;
(*nof_records)++;
//first_rowwasdefinedin<select_stmt>function;
//iffirst_row==1,printthefirstrow
//andthensetfirst_row=0toavoidthesubsequentexecutionforthefollowingrows.
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");
return0;
}
//Withcallbackfunction;
voidselect_stmt(constchar*stmt)
{char*errmsg;
intret;
intnrecs=0;
first_row=1;
ret=sqlite3_exec(database,stmt,select_callback,&nrecs,&errmsg);
if(ret!=sqlITE_OK)
{printf("Errorinselectstatement%s[%s].\n",errmsg);
}
else
{printf("\n%drecordsreturned.\n",nrecs);
}
}
//timecaculation
longtimecacul(){
structtimevaltv;
structtimezonetz;
gettimeofday(&tv,&tz);
return(tv.tv_sec*1000+tv.tv_usec/1000);
}
intmain()
{longstarttime,endtime,resulttime;
char*errmsg;
sqlite3_open("./Database.db",&database);
//sqlite3_exec(database,"PRAGMAsynchronous=OFF",&errmsg);
sqlite3_stmt*stmt;
strings="createtablewujie(xdecimal(5,2),ydecimal(5,zdecimal(5,2))";
constchar*creatTable=s.c_str();
cout<<"creatTable:"<<creatTable<<endl;
//charcreatTable[]="createtablewujie(a,b,c)";
intresult=sqlite3_exec(database,
creatTable,//stmt
0,
0,
&errmsg
);
if(result!=sqlITE_OK)
{cout<<"\nCouldnotpreparestatement:creatTable:"<<result<<endl;
return1;
}
////////BEGINTRANSACTION
starttime=timecacul();
sqlite3_exec(database,"BEGINTRANSACTION;",&errmsg);
stringinsertDataStr;
doublex,y,z;
doubleyTimes=1.222222222;
intiNum;
for(iNum=1;iNum<=1000000;iNum++)
{x=1*iNum;
y=yTimes*iNum;
z=2*iNum;
insertDataStr="insertintowujieVALUES("
+lexical_cast<string>(x)+","
+lexical_cast<string>(y)+","
+lexical_cast<string>(z)+")";
//cout<<"insertDataStr:"<<insertDataStr<<endl;
constchar*insertDataChar=insertDataStr.c_str();
result=sqlite3_exec
(database,
insertDataChar,//stmt
0,
0,
&errmsg
);
if(result!=sqlITE_OK)
{cout<<"\nCouldnotpreparestatement:inserData:"<<result<<endl;
return1;
}
}
sqlite3_exec(database,"COMMITTRANSACTION;",NULL);
endtime=timecacul();
resulttime=endtime-starttime;
printf("NOAUTOCOMMITINSERT：%dms.",resulttime);
cout<<endl;
charselectData[]="Selectx,zfromwujie";
starttime=timecacul();
select_stmt(selectData);
endtime=timecacul();
resulttime=endtime-starttime;
printf("Selectsqltime：%dms.",resulttime);
sqlite3_close(database);
return0;
}

使用sqlite3_bind_double 插入100万行数据

[html] view plain copy print ?

@H_404_1082@

#include<iostream>

#include"sqlite3.h"

#include<string.h>

#include<stdio.h>

#include<sys/time.h>

#include<boost/lexical_cast.hpp>

usingnamespacestd;

usingnamespaceboost;

intfirst_row;

sqlite3*database;

//callbackfunction;

intselect_callback(void*p_data,intnum_fields,char**p_fields,char**p_col_names)

{

inti;

int*nof_records=(int*)p_data;

(*nof_records)++;

//first_rowwasdefinedin<select_stmt>function;

//iffirst_row==1,printthefirstrow

//andthensetfirst_row=0toavoidthesubsequentexecutionforthefollowingrows.

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");

return0;

}

//Withcallbackfunction;

voidselect_stmt(constchar*stmt)

{char*errmsg;

intret;

intnrecs=0;

first_row=1;

ret=sqlite3_exec(database,&errmsg);

if(ret!=sqlITE_OK)

{printf("Errorinselectstatement%s[%s].\n",errmsg);

}

else

{printf("\n%drecordsreturned.\n",nrecs);

}

//timecaculation

longtimecacul(){

structtimevaltv;

structtimezonetz;

gettimeofday(&tv,&tz);

return(tv.tv_sec*1000+tv.tv_usec/1000);

}

intmain()

{longstarttime,resulttime;

char*errmsg;

sqlite3_open("./Database.db",&database);

sqlite3_stmt*stmt;

strings="createtablewujie(x,z)";

constchar*creatTable=s.c_str();

//cout<<"creatTable:"<<creatTable<<endl;

intresult=sqlite3_exec(database,

creatTable,//stmt

&errmsg

);

if(result!=sqlITE_OK)

{cout<<"\nCouldnotpreparestatement:creatTable:"<<result<<endl;

return1;

}

if(sqlite3_prepare

(database,

"insertintowujievalues(:x,:y,:z)",//stmt

-1,//Ifthanzero,thenstmtisreaduptothefirstnulterminator

&stmt,

0//Pointertounusedportionofstmt

)

!=sqlITE_OK)

{printf("\nCouldnotpreparestatement.");

return1;

}

intindex1,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("\nThestatementhas%dwildcards\n",sqlite3_bind_parameter_count(stmt));

starttime=timecacul();

sqlite3_exec(database,"BEGINTRANSACTION;",&errmsg);

doublex,z;

doubleyTimes=1.222222222;

intiNum;

for(iNum=1;iNum<=1000000;iNum++)

{x=1*iNum;

y=yTimes*iNum;

z=2*iNum;

if(sqlite3_bind_double(stmt,

index1,//Indexofwildcard

)

!=sqlITE_OK)

{printf("\nCouldnotbinddouble.\n");

return1;

}

if(sqlite3_bind_double(stmt,

index2,//Indexofwildcard

)

!=sqlITE_OK)

{printf("\nCouldnotbinddouble.\n");

return1;

}

if(sqlite3_bind_double(stmt,

index3,//Indexofwildcard

)

!=sqlITE_OK)

{printf("\nCouldnotbinddouble.\n");

return1;

}

if(sqlite3_step(stmt)!=sqlITE_DONE)

{printf("\nCouldnotstep(execute)stmt.\n");

return1;

}

sqlite3_reset(stmt);

}

sqlite3_exec(database,"COMMITTRANSACTION;",NULL);

endtime=timecacul();

resulttime=endtime-starttime;

printf("NOAUTOCOMMITINSERT：%dms.",resulttime);

///////////////////////////////////////////////

starttime=timecacul();

charselectData[]="Select*fromwujie";

select_stmt(selectData);

sqlite3_close(database);

endtime=timecacul();

resulttime=endtime-starttime;

printf("NOAUTOCOMMITINSERT：%dms.",resulttime);

return0;

}

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

猜你在找的Sqlite相关文章