RE: [sqlite] Single row insert speeds

[Mike Marshall]

What platform are you running on?

Most of these sorts of issues that have come up before relate to the fact
that SQLite is committing the data to the disc with each insert and has to
ensure that the buffer has been written before it can process the next
insert.

IIRC there is a PRAGMA to switch this behavior off, but of course you then
lose your ACID compliance which may or may not be a big deal to you.

This page http://www.sqlite.org/cvstrac/wiki?p=SpeedComparison might also be
useful to you.

M

-----Original Message-----
From: Mark Riehl [mailto:[EMAIL PROTECTED] 
Sent: 03 December 2007 16:58
To: sqlite-users@sqlite.org
Subject: [sqlite] Single row insert speeds

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]
----------------------------------------------------------------------------
-



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