sqlite默认是事务的
单一一条事务插入在100ms左右
sqlite3_exec(devDB, "BEGIN", 0, 0, &szError);
sqlite3_exec(devDB, "COMMIT", 0, 0, &szError);处理批量的性能很好
sqlite3_busy_timeout的存在时为了防止不同的连接实例间的锁定问题(系统级的文件锁),同一实例可以不用
单一一条事务插入在100ms左右
sqlite3_exec(devDB, "BEGIN", 0, 0, &szError);
sqlite3_exec(devDB, "COMMIT", 0, 0, &szError);处理批量的性能很好
sqlite3_busy_timeout的存在时为了防止不同的连接实例间的锁定问题(系统级的文件锁),同一实例可以不用
先sqlite3_prepare_v2,然后循环sqlite3_step和循环sqlite3_exec的性能差别不大
混乱的测试代码:
extern "C"
{
#include "Sqlite3/sqlite3.h"
}
void *sqlThread(void *arg)
{
struct timeval tvafter,tvpre;
struct timezone tz;
sqlite3* devDBx = (sqlite3*)arg;
char* szError=NULL;
int nRet;
pthread_t tid;
tid = pthread_self();
printf("start time %d", time(0));
for(int i=0; i<50000; ++i)
{ printf("%d:i=%d\n", tid, i);
gettimeofday (&tvpre , &tz);
//sqlite3_open("./wsqlitetest.db", &devDBx);
nRet = sqlite3_exec(devDBx, "insert into EXPANDER_TABLE (expander, expanderfather, hba, mark) values('aaaaaaa', 'bbbbbbb', 'ccccccc', 'dddddd');"
, 0, 0, &szError);
if(SQLITE_OK!=nRet)
{
printf("insert failed, nRet %d, %s\n", nRet, szError);
}
if(!(i&0xff))
{
//printf("i=%d\n", i);
}
if(devDBx)
{
//sqlite3_close(devDBx);
//devDBx = NULL;
}
gettimeofday (&tvafter , &tz);
printf("花费时间:%d\n", (tvafter.tv_sec-tvpre.tv_sec)*1000+(tvafter.tv_usec-tvpre.tv_usec)/1000);
}
printf("end time %d", time(0));
return NULL;
}
void *sqlThread2(void *arg)
{
struct timeval tvafter,tvpre;
struct timezone tz;
sqlite3* devDBx = (sqlite3*)arg;
//prepare test
sqlite3_stmt *stmt = 0;
const char *tail = 0;
char *sqlcmd = " insert into EXPANDER_TABLE (expander, expanderfather, hba, mark) "
" values('1111111', '222222', '333333', '4444444');";
int nRet = sqlite3_prepare_v2(devDBx, sqlcmd, strlen(sqlcmd), &stmt, &tail);
printf("start time %d", time(0));
for(int i=0; i<50000; ++i)
{ printf("i=%d\n", i);
gettimeofday (&tvpre , &tz);
nRet = sqlite3_step(stmt);
if (nRet != SQLITE_DONE)
{
printf("[findhdds]sqlite3_step failed, %d\n", nRet);
}
if(!(i&0xff))
{
//printf("i=%d\n", i);
}
gettimeofday (&tvafter , &tz);
printf("花费时间:%d\n", (tvafter.tv_sec-tvpre.tv_sec)*1000+(tvafter.tv_usec-tvpre.tv_usec)/1000);
}
printf("end time %d", time(0));
sqlite3_finalize(stmt);
printf("end time %d", time(0));
return NULL;
}
void sqlitetest(void)
{
struct timeval tvafter,tvpre;
struct timezone tz;
gettimeofday (&tvpre , &tz);
gettimeofday (&tvafter , &tz);
printf("花费时间:%d\n", (tvafter.tv_sec-tvpre.tv_sec)*1000+(tvafter.tv_usec-tvpre.tv_usec)/1000);
sqlite3* devDB;
int nRet = sqlite3_open("./wsqlitetest.db", &devDB);
char* szError=NULL;
if(SQLITE_OK!=nRet)
{
printf("open ./wsqlitetest.db failed\n", nRet);
}
nRet = sqlite3_exec(devDB, "create table if not exists EXPANDER_TABLE "
" (sasaddress integer PRIMARY KEY, "
" expander char(20), "
" expanderfather char(20), "
" hba char(20), "
" mark char(128));", 0, 0, &szError);
if(SQLITE_OK!=nRet)
{
printf("open create failed\n", nRet);
}
//sqlite3_busy_timeout(devDB, 10000);
int cnt=50;
pthread_t threadId_[cnt];
for(int x=0;x<cnt;++x)
{
//pthread_create(&threadId_[x],NULL,sqlThread,devDB);
pthread_create(&threadId_[x],NULL,sqlThread2,devDB);
}
sleep(1000);
printf("start time %d", time(0));
//sqlite3_exec(devDB, "BEGIN", 0, 0, &szError);
for(int i=0; i<50000; ++i)
{ printf("i=%d\n", i);
//
gettimeofday (&tvpre , &tz);
nRet = sqlite3_exec(devDB, "insert into EXPANDER_TABLE (expander, expanderfather, hba, mark) values('1111111', '222222', '333333', '4444444');"
, 0, 0, &szError);
if(SQLITE_OK!=nRet)
{
printf("insert failed, %s\n", szError);
}
if(!(i&0xff))
{
//printf("i=%d\n", i);
}
gettimeofday (&tvafter , &tz);
printf("花费时间:%d\n", (tvafter.tv_sec-tvpre.tv_sec)*1000+(tvafter.tv_usec-tvpre.tv_usec)/1000);
//
}
printf("end time %d", time(0));
//sqlite3_exec(devDB, "COMMIT", 0, 0, &szError);
//prepare test
sqlite3_stmt *stmt = 0;
const char *tail = 0;
char *sqlcmd = " insert into EXPANDER_TABLE (expander, expanderfather, hba, mark) "
" values('1111111', '222222', '333333', '4444444');";
nRet = sqlite3_prepare_v2(devDB, sqlcmd, strlen(sqlcmd), &stmt, &tail);
//int index1 = sqlite3_bind_parameter_index(stmt, "@pa1");
//int index2 = sqlite3_bind_parameter_index(stmt, "@pa2");
//int index3 = sqlite3_bind_parameter_index(stmt, "@pa3");
//int index4 = sqlite3_bind_parameter_index(stmt, "@pa4");
printf("start time %d", time(0));
for(int i=0; i<50000; ++i)
{ printf("i=%d\n", i);
gettimeofday (&tvpre , &tz);
if (sqlite3_step(stmt) != SQLITE_DONE)
{
printf("[findhdds]sqlite3_step failed\n");
}
if(!(i&0xff))
{
//printf("i=%d\n", i);
}
gettimeofday (&tvafter , &tz);
printf("花费时间:%d\n", (tvafter.tv_sec-tvpre.tv_sec)*1000+(tvafter.tv_usec-tvpre.tv_usec)/1000);
}
printf("end time %d", time(0));
sqlite3_finalize(stmt);
}