I'm running SQLite 3.1.13 under Fedora 3. Our real app is written in
C++. The sample I wrote to debug the insert times is written in C
(gcc 3.4.2).
Here is the table I've defined:
CREATE TABLE sampleTable (
logHost varchar(64) DEFAULT NULL,
compId smallint(5) DEFAULT NULL,
pid int(10) DEFAULT NULL,
version varchar(8) DEFAULT NULL,
rptTime decimal(20,6) DEFAULT NULL,
rptStatus tinyint(3) DEFAULT NULL,
data text
);
And I have an index on the compId column.
Here is a typical insert:
INSERT INTO sampleTable
VALUES (\"hostname\", \"6\", \"5.1.0\", \"0\", \"1708\", \"1196303669.06533598
8998\", \"hostIfc=eth0:1;hostIp=172.16.1.1;msgCount=0;queueSize=0 (0 peak)
;\")";
As I said, this is taking anywhere from 5-50 ms.
Running 10 times in a loop (with a 1 sec sleep in between to let it
finish), here is what I see for insert times:
Insert time in ms 57.444000
Insert time in ms 6.031000
Insert time in ms 5.048000
Insert time in ms 6.372000
Insert time in ms 4.786000
Insert time in ms 11.169000
Insert time in ms 4.020000
Insert time in ms 5.211000
Insert time in ms 7.112000
Insert time in ms 3.962000
Thanks,
Mark
On Dec 3, 2007 12:30 PM, P Kishor <[EMAIL PROTECTED]> wrote:
> you would need to give more info about your db, the data, etc. On my
> Macbook Pro, I get 1000+ inserts a second for a random 100 byte string
> insert (that is, less than one per ms). That includes the time to
> generate the string, and is all in Perl, while I am listening to
> iTunes, and no funny pragma trix. Code follows...
>
> I am using SQLite 3.4.2
>
> CREATE TABLE foo (a);
>
> sub speedtest {
> $sth->execute( str() )
> }
>
> sub str {
> my $str;
> for (0 .. 99) {
> $str .= ('a' .. 'z')[int(rand(25)) + 1];
> }
> return $str;
> }
>
> timethis(1000, \&speed);
>
> $ ./db.pl
> timethis 1000: 2 wallclock secs ( 0.20 usr + 0.75 sys = 0.95 CPU) @
> 1052.63/s (n=1000)
>
>
> On 12/3/07, 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]
> > -----------------------------------------------------------------------------
> >
> >
>
>
> --
> Puneet Kishor
> http://punkish.eidesis.org/
> Nelson Institute for Environmental Studies
> http://www.nelson.wisc.edu/
> Open Source Geospatial Foundation (OSGeo)
> http://www.osgeo.org/
> Summer 2007 S&T Policy Fellow, The National Academies
> http://www.nas.edu/
>
> -----------------------------------------------------------------------------
>
> To unsubscribe, send email to [EMAIL PROTECTED]
> -----------------------------------------------------------------------------
>
>
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
