sqlite性能、安全

先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=%dn",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');",&szError);
		if(SQLITE_OK!=nRet)
		{
			printf("insert failed,nRet %d,%sn",nRet,szError);
		}
		if(!(i&0xff)) 
		{
			//printf("i=%dn",i);
		}
		if(devDBx)
		{
			//sqlite3_close(devDBx);
			//devDBx = NULL;
		}
		gettimeofday (&tvafter,&tz);
		printf("花费时间:%dn",(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,mark) "
				  " values('2222211','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=%dn",i);
		
		gettimeofday (&tvpre,&tz);
		nRet = sqlite3_step(stmt);
		if (nRet != SQLITE_DONE)
		{
			printf("[findhdds]sqlite3_step failed,%dn",nRet);
		}
		if(!(i&0xff)) 
		{
			//printf("i=%dn",i);
		}
		gettimeofday (&tvafter,(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("花费时间:%dn",(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 failedn",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));",&szError);

	if(SQLITE_OK!=nRet)
	{
		printf("open create failedn",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],sqlThread2,devDB);
	}

	
	sleep(1000);
	printf("start time %d",time(0));
	//sqlite3_exec(devDB,&szError);

	for(int i=0; i<50000; ++i)
	{	printf("i=%dn",i);
		//
		gettimeofday (&tvpre,&tz);
		nRet = sqlite3_exec(devDB,mark) values('2222211','4444444');",(tvafter.tv_sec-tvpre.tv_sec)*1000+(tvafter.tv_usec-tvpre.tv_usec)/1000);
	//
	}
	printf("end time %d",&szError);

	//prepare test
	sqlite3_stmt *stmt = 0;
	const char *tail = 0;
	char *sqlcmd = " insert into EXPANDER_TABLE (expander,'4444444');";
	nRet = sqlite3_prepare_v2(devDB,&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",&tz);
		if (sqlite3_step(stmt) != SQLITE_DONE)
		{
			printf("[findhdds]sqlite3_step failedn");
		}
		if(!(i&0xff)) 
		{
			//printf("i=%dn",(tvafter.tv_sec-tvpre.tv_sec)*1000+(tvafter.tv_usec-tvpre.tv_usec)/1000);
	}
	printf("end time %d",time(0));
	sqlite3_finalize(stmt);

	

}

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!