Re: [sqlite] Single row insert speeds

[Ken]

I have a few suggestions for you to try: 
     1. Use prepared statements. Do not free the statement, reset it and re-use 
that way you don't incure the parsing overhead for each statement.
     2. Use bind variables in conjunction.
   
  Redesign to perform more than single row inserts into a transaction. Perhaps 
utilizing a timer or data row count to trigger a commit.
   
     

Mark Riehl <[EMAIL PROTECTED]> wrote:
  I've got an application that logs real-time data. Some of the data is
periodic (every few secs), other data comes more frequently.
Basically, I'm not dealing with bulk inserts, so, I can't queue things
up and insert all at once.

I'm noticing that my insert times are pretty slow (~5-50 ms on a Intel
Core 2) for a single record of ~100 bytes.

Any suggestions for speeding up single row inserts? I saw some of the
other threads on using transactions, but, not sure if that applied to
single inserts.

Here is a simple app I wrote to test the timing:

int rc = sqlite3_open("mydb.db", &db);

for (i = 0; i < 10; i++) {

printf("Executing %s\n", insertStatement);

gettimeofday(&before, 0);
rc = sqlite3_exec(db, insertStatement, NULL, NULL, &zErr);
gettimeofday(&after, 0);
if (rc != SQLITE_OK) {
...
}
}

printf("Before %d %d\n", before.tv_sec, before.tv_usec);
printf("After %d %d\n", after.tv_sec, after.tv_usec);

sleep(1);
}

sqlite3_close(db);

Thanks for the help,
Mark

-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------