优化前代码:
#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,0,&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,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,0,&zErrMsg);
if(r!=SQLITE_OK)
{
printf("error happend:%s\n",zErrMsg);
}
/*此处为添加的优化代码*/
r=sqlite3_exec(db,"BEGIN;",0,0,&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);
/*添加的优化代码结束事务*/
r=sqlite3_exec(db,"COMMIT;",0,0,&zErrMsg);/*结束事务,提交*/
/*
这样SQLite将把全部要执行的SQL语句先缓存在内存当中,然后等到COMMIT的时候一次性的写入数据 库,这样数据库文件只被打开关闭了一次,效率自然大大的提高。有一组数据对比:
优化前后时间对比:
前:109。7810ms
后:0.1710ms
可见时间相差之大
*/
finish=(double)clock();
printf("%.4fms",(finish-start)/1000);