sqlite性能、安全

sqlite默认是事务的
单一一条事务插入在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);

	

}