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;
}
