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