优化前代码:
#include <stdio.h>
#include <stdlib.h>
#include "sqlite3.h"
int main()
{
double start,finish;
sqlite3 *db;
sqlite3_stmt* stmt;
const char *zTail;
char *zErrMsg=0;
int r=sqlite3_open("test.db",&db);
if(r){
printf("error happened:%s\n",sqlite3_errmsg(db));
}
char* sql="create table [test]([id] INTEGER PRIMARY KEY,[s] TEXT COLLATE NOCASE)";
start=(double)clock();
r=sqlite3_exec(db,sql,NULL,&zErrMsg);
if(r!=sqlITE_OK)
{
printf("error happend:%s\n",zErrMsg);
}
sqlite3_prepare(db,"insert into test(s) values(?);",-1,&stmt,&zTail);
int i;
for(i=0;i<1000;i++)
{
sqlite3_bind_int(stmt,1,i);
r=sqlite3_step(stmt);
sqlite3_reset(stmt);
}
sqlite3_finalize(stmt);
finish=(double)clock();
printf("%.4fms",(finish-start)/1000);
}
优化后代码:
#include <stdio.h>
#include <stdlib.h>
#include "sqlite3.h"
int main()
{
double start,zErrMsg);
}
r=sqlite3_exec(db,"BEGIN;",&zErrMsg);/*手动开始事务*/
sqlite3_prepare(db,i);
r=sqlite3_step(stmt);
sqlite3_reset(stmt);
}
sqlite3_finalize(stmt);
r=sqlite3_exec(db,"COMMIT;",&zErrMsg);/*结束事务,提交*/ /* 这样sqlite将把全部要执行的sql语句先缓存在内存当中,然后等到COMMIT的时候一次性的写入数据 库,这样数据库文件只被打开关闭了一次,效率自然大大的提高。有一组数据对比: 优化前后时间对比: 前:109。7810ms 后:0.1710ms 可见时间相差之大 */ finish=(double)clock(); printf("%.4fms",(finish-start)/1000);