今天上班的时候,突发奇想,不知道sqlite3的插入性能如何,于是做了如下测试。
1. 创建空表userinfo
"username" TEXT,
"address" TEXT,
"sex" TEXT,
"email" TEXT,
"tel" TEXT,
"other" TEXT
)
2. 写了个测试程序,源码如下
#include <sqlite3.h>
#include <string.h>
#include <stdio.h>
#include <stdlib.h>
#include <sys/time.h>
int main(void)
{
int i;
int rc;
sqlite3 *db;
char *zErrMsg = 0;
sqlite3_stmt *stmt = NULL;
rc = sqlite3_open_v2(DB_PATH, &db, SQLITE_OPEN_READWRITE, NULL);
if(rc)
{
sqlite3_close(db);
return -1;
}
sqlite3_exec(db, "begin;", NULL, 0, &zErrMsg); /* 事务开始, 非事务时注释掉本行 */
char scmd[128] = {0};
snprintf(scmd, sizeof(scmd), "insert into userinfo \
(username, address, sex, email, tel, other) values (?, ?, ?, ?, ?, ?)");
sqlite3_prepare_v2(db, scmd, strlen(scmd), &stmt, NULL );
char name[16] = {0};
char addr[16] = {0};
char sex[8] = {0};
char email[32] = {0};
char tel[32] = {0};
char other[16] = {0};
sqlite3_bind_text(stmt, 3, "male", strlen("male"), NULL);
struct timeval tpstart,tpend;
float timeuse;
gettimeofday(&tpstart,NULL);
for (i = 0; i < NUMBER; i++)
{
snprintf(name, sizeof(name), "name%d", i);
snprintf(addr, sizeof(addr), "addr%d", i);
snprintf(email, sizeof(email), "email%d", i);
snprintf(other, sizeof(other), "other%d", i);
sqlite3_bind_text(stmt, 1, name, strlen(name), NULL);
sqlite3_bind_text(stmt, 2, addr, strlen(addr), NULL);
sqlite3_bind_text(stmt, 4, email, strlen(email), NULL);
sqlite3_bind_int(stmt, 5, i);
sqlite3_bind_text(stmt, 6, other, strlen(other), NULL);
sqlite3_step(stmt);
sqlite3_reset(stmt);
}
sqlite3_exec(db, "commit;", NULL, 0, &zErrMsg);/* 事务提交, 非事务时注释掉本行 */
gettimeofday(&tpend, NULL);
timeuse = 1000000*(tpend.tv_sec - tpstart.tv_sec)+ tpend.tv_usec - tpstart.tv_usec;
timeuse /= 1000000;
printf("time used:%f\n", timeuse);
sqlite3_free(zErrMsg);
return 0;
}
我测试了几个不同的NUMBER(插入记录数),结果如下
NUMBER TimeUsed(事务) TimeUsed(非事务)
100 0.145632 12.576010
1000 0.178998 106.686035
100000 0.993440 >N, N未知
从上面的结果可以看出,sqlite在事务和非事务两种模式下的插入性能差距巨大,有兴趣的朋友可以自己测试一下哈。