sqlite3_get_table执行查询速度会变慢的问题

l360220954 2012-10-10 10:14:00

大家好，我自己写了一个sqlite3_get_table函数的封装，在作测试的时候，发现这个函数在使用时，查询的速度会变慢。具体的封装是这样的

/*db_name is the database you want to open and sql is the sql sentence to execute, this
**sql sentence must be sql select sentence***************************/
int SQL_Select(const char* db_name, const char* sql, char ***pResult, int *nRow, int *nCol)
{
if(NULL == db_name || NULL == sql)
return false;

sqlite3 *db = NULL; //the database to be opened

if((db = OpenDB(db_name)) == NULL)
{
return false;
}
//execute select sentence
if(false == SelectFromTable(db, sql, pResult, nRow, nCol))
{
return false;
}

CloseDB(db);

return true;
}

这里的OpenDB和CloseDB函数分别是对sqlite3_open和sqlite3_close函数的封装。SelectFromTable(db, sql, pResult, nRow, nCol)函数封装了sqlite3_get_table函数
这个函数的代码是这样的
static bool SelectFromTable(sqlite3* db,const char *sql, char ***pResult, int *nRow, int *nCol)
{
if(sql == NULL || db == NULL)
return false;

char *errr_msg = NULL;
// char *zErr; // lxx add
int rc;
api.sqlite3_exec(db,"BEGIN",NULL,NULL,&zErr);
rc = (api.sqlite3_get_table)(db,sql,pResult, nRow, nCol, &errr_msg);
api.sqlite3_exec(db,"COMMIT",NULL,NULL,&zErr);
if(rc != SQLITE_OK)
{
if(NULL != errr_msg)
{
#ifdef DEBUG
printf("SQL error: %s\n",errr_msg);
#endif
(api.sqlite3_free)(errr_msg);
errr_msg = NULL;
}//end if
if(NULL != *pResult)
{
(api.sqlite3_free_table)(*pResult);
pResult = NULL;
}
return false;
}//end if
else
return true;
}

在这里我觉得api.sqlite3_exec(db,"BEGIN",NULL,NULL,&zErr);和api.sqlite3_exec(db,"COMMIT",NULL,NULL,&zErr);这两行代码没有起到什么作用？另外，不知道sqlite3_get_table没有返回结果的时候，那个pResult是不是为空的。

我对这个函数进行了测试，在测试的时候，我在一个线程里每秒钟往数据库里添加数据，在另外一个线程里查询10秒钟内添加的表中的一项的平均值，具体的代码是这样的：

int main()
{
int res;
pthread_t a_thread;
// void *thread_result;

char* sql = "create table time(time text, id int, id1 int, id2 int, id3 int, id4 int);";

//创建表
if(FALSE == SQL_Exec(database, sql))
{
printf("lxx add*table cannot created\r\n"); //lxx add
//return 0;
}
EXACT_TIME e_time; //定义时间

char sqlInsert[100];

res = pthread_create(&a_thread, NULL, thread_function, NULL);
if(res != 0)
{
perror("Thread creation failed!");
exit(EXIT_FAILURE);
}

struct timeval tv_begin, tv_end; // lxx add
while(1)
{
// printf("lxx add***%s(%d)of%s*****\r\n",__FUNCTION__,__LINE__,__FILE__); //lxx add
get_exact_time(&e_time);

sprintf(sqlInsert, "insert into time values('%4d-%2d-%2d %2d:%2d:%2d:%6d', 100, 100, 1000, 10, 100);",
e_time.year, e_time.month, e_time.day, e_time.hour, e_time.min, e_time.second,
e_time.microsec);

gettimeofday(&tv_begin, NULL); // lxx add
if(FALSE == SQL_Exec(database, sqlInsert))
{
printf("lxx addcannot insert\r\n"); //lxx add
//return 0;
}//end if
gettimeofday(&tv_end, NULL);
long runT = 1000000 * (tv_end.tv_sec - tv_begin.tv_sec) + tv_end.tv_usec - tv_begin.tv_usec;
// printf( "lxx insert time = %ld\n", runT); // lxx add
lxx_write_long("/sqlite/insert.txt", runT);

sleep(1);
}//end while

printf("Waiting for thread to finish...\n");

// res = pthread_join(a_thread, &thread_result);
// if(res != 0)
// {
// perror("Thread join failed!\n");
// exit(EXIT_FAILURE);
// }

// printf("Thread joined, it returned %s\n", (char *)thread_result);
// printf("Message is now %s\n", message);

exit(EXIT_FAILURE);
}

void *thread_function(void *arg)
{
char sqlSelect[110];
static EXACT_TIME e_start_time; //定义开始时间
static EXACT_TIME e_end_time; //定义开始时间
struct timeval begin, end; // lxx add

while(1)
{
// printf("lxx add***%s(%d)of%s*****\r\n",__FUNCTION__,__LINE__,__FILE__); //lxx add
int nRow = 0;
int nCol = 0;
int i=0, j=0;
char **pResult = NULL;
get_exact_time(&e_start_time);
sleep(10);
get_exact_time(&e_end_time);

sprintf(sqlSelect,"select AVG(id) from time where time between"
"'%4d-%2d-%2d %2d:%2d:%2d:%6d' and '%4d-%2d-%2d %2d:%2d:%2d:%6d';",
e_start_time.year, e_start_time.month, e_start_time.day,
e_start_time.hour, e_start_time.min, e_start_time.second, e_start_time.microsec,
e_end_time.year, e_end_time.month, e_end_time.day,
e_end_time.hour, e_end_time.min, e_end_time.second, e_end_time.microsec);

gettimeofday(&begin, NULL); // lxx add
if(FALSE == SQL_Select(database, sqlSelect, &pResult, &nRow, &nCol))
{
printf("lxx add*cannot select\r\n"); //lxx add
}//end if
gettimeofday(&end, NULL);
long runT = 1000000 * (end.tv_sec - begin.tv_sec) + end.tv_usec - begin.tv_usec;
printf( "lxx select = %ld\n", runT); // lxx add
lxx_write_long("/sqlite/select.txt", runT);

for(i = 0; i < nRow; i++)
{
for(j = 0; j < nCol; j++)
{
//the i+1 term skips over the first record which is the columnheaders
printf("%s\n", pResult[(i+1)*nCol + j]);
}//end for
}//end for
if(pResult != NULL)
{
SQL_FreeMemory(pResult);
pResult = NULL;
}

}//end while
//printf("lxx add***%s(%d)of%s*****\r\n",__FUNCTION__,__LINE__,__FILE__); //lxx add
}

在测试的时候发现输出的时间值会越来越长，而且会有一定的内存泄漏。
想请高手帮我看下，这是怎么回事了，是sqlite本身的问题的问题还是我程序的问题呢？

谢谢啦

...全文