/* 求助：在嵌入式系统中sqlite的select和update执行时间过长问题。
* 以下是示意性源代码。请各位帮忙看看，问题何在。
* 软硬件环境S3C2410 203MHz, Linux 2.4 ,sqlite 3.3，
* 数据库在NAND Flash上存储，文件系统是YAFFS
*/
#define RECORD_NUM 1
char QueryData(void)
{
struct timeval tpstart,tpend;
float timeuse;
char ErrorFlag=1;
unsigned int di,dj,dk;
DataBuffer[0]='\0'; //DataBuffer是全局变量,长度384*16
//查询数据库，获得RECORD_NUM条记录，每条记录大约384个字节.
sprintf(sqlstr,"select * from db where isSent=0 limit %d;",RECORD_NUM);
p(semid);
gettimeofday(&tpstart,NULL);
// 添加了事务也没有减少时间消耗
//rc=sqlite3_exec(db,"begin transaction",0,0,&zErrMsg);
rc = sqlite3_exec(db, sqlstr, SQLCallBack, 0, &zErrMsg);
//rc=sqlite3_exec(db,"commit transaction",0,0,&zErrMsg);
gettimeofday(&tpend,NULL);
timeuse=1000000*(tpend.tv_sec-tpstart.tv_sec)+
tpend.tv_usec-tpstart.tv_usec;
timeuse/=1000000;
printf("sqlite3 select * Used Time：%f\n",timeuse); // 这里打印值为5秒左右
v(semid);
// 更新数据库
sprintf(sqlstr,"update db set isSent=1 where ID in (select ID from db where isSent=0 limit %d);",RECORD_NUM);
p(semid);
gettimeofday(&tpstart,NULL);
rc = sqlite3_exec(db, sqlstr, 0, 0, &zErrMsg);
gettimeofday(&tpend,NULL);
timeuse=1000000*(tpend.tv_sec-tpstart.tv_sec)+
tpend.tv_usec-tpstart.tv_usec;
timeuse/=1000000;
printf("sqlite3 update Used Time：%f\n",timeuse); // 这里打印值为5秒左右
v(semid);
}
static int SQLCallBack(void *NotUsed, int argc, char **argv, char **azColName)
{
struct timeval tpstart,tpend;
float timeuse;
int i;
char tstr[500];
gettimeofday(&tpstart,NULL);
// argv[0] is ID ,no use for server
sprintf(tstr,"'%s',",argv[1]);
strcat(DataBuffer,tstr);
for(i=2; i<argc; i++){
// printf("%s,",argv
? argv : "N");
sprintf(tstr,"%s,",argv);
strcat(DataBuffer,tstr);
}
strcat(DataBuffer,"\n");
gettimeofday(&tpend,NULL);
timeuse=1000000*(tpend.tv_sec-tpstart.tv_sec)+
tpend.tv_usec-tpstart.tv_usec;
timeuse/=1000000;
printf("SQLCallBack Used Time：%f\n",timeuse); //这里打印值为0.000280左右
return 0;
}
