linux下sqlite3插入性能测试

今天上班的时候，突发奇想，不知道sqlite3的插入性能如何，于是做了如下测试。

1. 创建空表userinfo

CREATE TABLE 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在事务和非事务两种模式下的插入性能差距巨大，有兴趣的朋友可以自己测试一下哈。