Anish Enos Mathew wrote:
Hi Dennis,
I am stuck up with a problem. I want to calculate the
time taken for retrieval of 10,00,000 random records 10,00,000 times. I
am using a function getNumber for getting the random number and getTime
for getting the current time.getNumber function will return a value
between 1 and 10,00,000. When I am passing the range of random number to
be generated as 1000, the program is taking 340 seconds to complete. I
am not able to give 10,00,000 as the range, since It is going to take a
lot time.Y is it so. I am attaching the program with this. Please have a
look at it. Can any body tell me where am I going wrong. Is there any
other methods that can be used so that I can reduce the retrieval time?
int main(int argc, char *argv[])
{
char *database = "test.db";
sqlite3 *db;
sqlite3_stmt *select;
int col1,rc,i,j;
int no_of_rows=1000000,count;
char *col2;
char *col3;
double start_time, end_time, elapsed_time, total_elapsed_time=0;
sqlite3_open(database, &db);
sqlite3_prepare(db, "select * from data_table where seq_number = ?",
-1,&select,NULL );
for(i=0;i<1000000;i++)
{
j=getNumber(1000000);
sqlite3_bind_int(select, 1, j);
sqlite3_step(select);
start_time=getTime(); //get the starting time of fetching rows
col1 = sqlite3_column_int(select,0);
col2 = sqlite3_column_text(select,1);
col3 = sqlite3_column_text(select,2);
end_time = getTime();// getting the ending time of fetching rows
elapsed_time = end_time - start_time;
total_elapsed_time = total_elapsed_time + elapsed_time;
// printf("\n%d %s %s",col1,col2,col3);
sqlite3_reset(select);
}
sqlite3_finalize(select);
sqlite3_close(db);
printf(" \nRetrieved %d datas in %lf seconds,%lf selects/sec\n\n",
no_of_rows,total_elapsed_time,no_of_rows / total_elapsed_time);
return 0;
}
Anish,
From your code it looks like your 10,00,000 should really be 1,000,000
(i.e. one million).
Your code is summing the time it takes sqlite to return the column
values from the queries, not the time it takes sqlite to locate the
records. Is that what you want? I suspect you should be starting your
timer before the the sqlite3_step() call. Personally, I think all the
time from the sqlite3_bind() call through to the end of the
sqlite3_reset() call should be counted. That is the total execution time
it takes to retrieve one record. This is the loop time less the per loop
overhead, the time taken to measure the time, and the time to generate
your random number.
You are timing a query that does a lookup with a where clause. I suspect
that you do not have an index on the seq_number column that the where
clause is searching. If this is the case, sqlite must do a table scan
for each returned row. Even if the records were inserted in sequence,
without an index sqlite won't know that. Add an index on your seq_number
column and the lookup will go much faster.
HTH
Dennis Cote
P.S. Y don't u post ur questions in English? Those silly abbreviations
aren't cool, they're annoying. I had to get that off my chest, it's one
of my pet peeves. :-)
