Dennis, Thank you so much for sending me the code. Its works fine!! It took 8 sec to insert 100000 records into the table where previously it was taking 30 sec to insert 10000 records. Dennis, if u could do me a favour, can u explain me how to retrieve datas from the table. Or if u have the code for that, can u pls send it to me...
-----Original Message----- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 09, 2006 9:33 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Low Level API for SQLite3 Anish Enos Mathew wrote: > Thank you so much cote, for ur valuable suggestion. I would be greatly > thankful to u if u could send me a simple c program which uses the above > API's to insert some values into the SQLite database. > > Anish, The sample program below shows how to use precompiled inserts in SQLite. On my computer I get the following results for 1,000,000 inserts. This produces a database file that is 46.9 MB. Executed 1000000 inserts in 11 seconds, 90909 inserts/sec For comparison I made a second program that builds equivalent SQL statements and then passes them to SQLite to compile and execute using sqlite3_exec(). Executed 1000000 inserts in 34 seconds, 29412 inserts/sec This program takes 3 times longer to execute, so it is spending 2/3 of its time generating and parsing the SQL statements. Note that surrounding the insert loop with a "begin transaction"/"commit transaction" pair is extremely important to getting these high insert rates. Without a transaction these programs are both much slower since they become I/O bound. They both take about 50 seconds to do 500 inserts at about 10 inserts/sec. This is slower by a factor of about 3000 or 9000. HTH Dennis Cote P.S. I apologize if Anish is not your first name, but I believe that most people use the normal spoken order of their names for when setting up an email account. Prepared insert statements. #include <stdio.h> #include <stdlib.h> #include <time.h> #include "sqlite3.h" // struct for sample records. typedef struct { int a; float b; char c[50]; } record; // error checking elimnated for clarity int main(int argc, char *argv[]) { char *database = "test.db"; sqlite3 *db; sqlite3_stmt *insert; record sample; int samples = 1000000; int i; time_t bgn, end; double t; // open a new database (after deleting any previous database) remove(database); sqlite3_open(database, &db); // create a table sqlite3_exec(db, "create table t (a integer, b float, c text)", NULL, NULL, NULL); // open transaction to speed inserts sqlite3_exec(db, "begin transaction", NULL, NULL, NULL); // compile an SQL insert statement sqlite3_prepare(db, "insert into t values (?, ?, ?)", -1, &insert, NULL); // records start time bgn = time(NULL); // loop to insert sample values for (i = 0; i < samples; i++) { // generate the next sample values sample.a = i; sample.b = i * 1.1; sprintf(sample.c, "sample %d %f", sample.a, sample.b ); // bind parameter values sqlite3_bind_int(insert, 1, sample.a); sqlite3_bind_double(insert, 2, sample.b); sqlite3_bind_text(insert, 3, sample.c, -1, SQLITE_STATIC); // execute the insert sqlite3_step(insert); // reset for next loop sqlite3_reset(insert); } // record end time end = time(NULL); // finalize compiled statement to free memory sqlite3_finalize(insert); // close transaction sqlite3_exec(db, "commit transaction", NULL, NULL, NULL); // close the database sqlite3_close(db); // report timing t = difftime(end, bgn); printf("Executed %d inserts in %.0f seconds, %.0f inserts/sec\n", samples, t, samples / t); return 0; } Compiled insert statements. #include <stdio.h> #include <stdlib.h> #include <time.h> #include "sqlite3.h" // struct for sample records. typedef struct { int a; float b; char c[50]; } record; // error checking elimnated for clarity int main(int argc, char *argv[]) { char *database = "test.db"; sqlite3 *db; char insert[200]; record sample; int samples = 1000000; int i; time_t bgn, end; double t; // open a new database (after deleting any previous database) remove(database); sqlite3_open(database, &db); // create a table sqlite3_exec(db, "create table t (a integer, b float, c text)", NULL, NULL, NULL); // open transaction to speed inserts sqlite3_exec(db, "begin transaction", NULL, NULL, NULL); // records start time bgn = time(NULL); // loop to insert sample values for (i = 0; i < samples; i++) { // generate the next sample values sample.a = i; sample.b = i * 1.1; sprintf(sample.c, "sample %d %f", sample.a, sample.b ); // build next insert statement sprintf(insert, "insert into t values (%d, %#f, '%s')", sample.a, sample.b, sample.c); // execute the insert sqlite3_exec(db, insert, NULL, NULL, NULL); } // record end time end = time(NULL); // close transaction sqlite3_exec(db, "commit transaction", NULL, NULL, NULL); // close the database sqlite3_close(db); // report timing t = difftime(end, bgn); printf("Executed %d inserts in %.0f seconds, %.0f inserts/sec\n", samples, t, samples / t); return 0; } 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