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: [email protected]
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
