Hi Dennis,
I corrected one problem of mine, but is left with another. As
you told me, I tried creating index for the seq_number. But I came to
know that if we are creating a table and assigning primary key to a
particular column (here seq_number), we do not want to create index for
that column. It automatically assigns index for that column. Please see
the link.
http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html
So my problem of retrieving 1,000,000 random records 1,000,000 times
works fine for 15 bytes. But it is taking too long a time for 1k
records. It is almost taking 102 seconds for retrieving 50,000 records
of size 1k. Can u suggest me a way for reducing the time taken for the
same? I have'nt done any changes in my program. The only change I made
was adding primary key to the seq_number column in my data insertion
program.
sqlite3_exec(db, "create table data_table(seq_number integer PRIMARY
KEY, data text,curr_date text)",NULL, NULL, NULL);
I want to insert and retrieve 4k sized data also. So if I am going in
this retrieval rate it's going to take a long time for 4k also.
With regards,
Anish Enos Mathew
-----Original Message-----
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: Thursday, May 18, 2006 9:00 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] High retrieval time. Please help
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?
>
>
>//data retrieval program.
>
> 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. :-)
The information contained in, or attached to, this e-mail, contains
confidential information and is intended solely for the use of the individual
or entity to whom they are addressed and is subject to legal privilege. If you
have received this e-mail in error you should notify the sender immediately by
reply e-mail, delete the message from your system and notify your system
manager. Please do not copy it for any purpose, or disclose its contents to any
other person. The views or opinions presented in this e-mail are solely those
of the author and do not necessarily represent those of the company. The
recipient should check this e-mail and any attachments for the presence of
viruses. The company accepts no liability for any damage caused, directly or
indirectly, by any virus transmitted in this email.
www.aztecsoft.com
