Re: [sqlite] Single row insert speeds
"Mark Riehl" <[EMAIL PROTECTED]> wrote: >> > For testing, I've modified the the insert to look like this: > char *insertStatement = "PRAGMA synchronous=OFF;BEGIN;INSERT INTO > sampleTable VALUES (\"hostname\", \"6\", \"5.1.0\", \"0\", \"1708\", > \"1196303669.065335988998\", > \"hostIfc=eth0:1;hostIp=172.16.1.1;msgCount=0;queueSize=0 (0 > peak);\");COMMIT;"; > > Is this the correct syntax for the PRAGMA statement? Can I issue it > once and will it remain active as long as the connection is open? > The PRAGMA syntax is correct. Issue it once when you initially open the connection. The BEGIN and COMMIT are automatic and can be omitted (for additional speed). SQL wants values to be quoted using single-quotes, not double-quotes. SQLite allows double-quotes but it is technically wrong. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Single row insert speeds
All - After reviewing some of my calculations, I realized I made a mistake and that the inserts didn't improve as much as I thought. I used the PRAGMA synchronouse = OFF and I'm averaging about 0.21 ms for an insert on a Core 2 Shuttle running Fedora 3. 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 ); 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) ;\")"; For testing, I've modified the the insert to look like this: char *insertStatement = "PRAGMA synchronous=OFF;BEGIN;INSERT INTO sampleTable VALUES (\"hostname\", \"6\", \"5.1.0\", \"0\", \"1708\", \"1196303669.065335988998\", \"hostIfc=eth0:1;hostIp=172.16.1.1;msgCount=0;queueSize=0 (0 peak);\");COMMIT;"; Is this the correct syntax for the PRAGMA statement? Can I issue it once and will it remain active as long as the connection is open? Thanks, Mark On Dec 3, 2007 6:45 PM, Mark Riehl <[EMAIL PROTECTED]> wrote: > I used the PRAGMA statement and turned off the synchronous option. It > made a huge difference. Single inserts were ranging from 5 - 50 ms, > now, they're at ~.04 ms. > > However, I guess there is a tradeoff between the safety of the > synchronous operation (in case power is lost) versus the insert > speeds. > > Thanks for the help, > Mark > > > On Dec 3, 2007 12:59 PM, <[EMAIL PROTECTED]> wrote: > > "P Kishor" <[EMAIL PROTECTED]> wrote: > > > I get 1000+ inserts a second for a random 100 byte string > > > insert ( > > > > I get 5+ inserts/sec on my Linux box. > > > > Insert speed is not the issue. It is COMMIT speed. At > > each commit, SQLite waits until all data is on oxide before > > continuing. That will typically take at least two rotations > > of the disk platter, or about 17 millisecond, depending on > > your disk drive. Waiting for data to get to oxide is > > part of being ACID. You can set: > > > >PRAGMA synchronous=OFF; > > > > and your COMMITs will go *much* faster because it will no > > longer wait on the disk drive. But if you lose > > power in the middle of a commit, you might corrupt your > > database file. > > > > Note that there is an implied BEGIN...COMMIT around every > > INSERT statement if you do not explicitly start a transaction > > using your own BEGIN. > > > > -- > > D. Richard Hipp <[EMAIL PROTECTED]> > > > > > > > > - > > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Single row insert speeds
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]
-
Re: [sqlite] Single row insert speeds
A faster disk will give you better performance. A 15,000 rpm disk will
give almost three times the performance of a 5,400 rpm one and retain
the ACID mode.
You could also queue your input and launch periodic Sqlite transactions
to empty the queue.
Mike Marshall wrote:
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]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Single row insert speeds
On 12/3/07, Mark Riehl <[EMAIL PROTECTED]> wrote: > I used the PRAGMA statement and turned off the synchronous option. It > made a huge difference. Single inserts were ranging from 5 - 50 ms, > now, they're at ~.04 ms. > > However, I guess there is a tradeoff between the safety of the > synchronous operation (in case power is lost) versus the insert > speeds. there is always a tradeoff between caution and impetuousness. Such is life. Glad it worked out for you. ;-) > > Thanks for the help, > Mark > > On Dec 3, 2007 12:59 PM, <[EMAIL PROTECTED]> wrote: > > "P Kishor" <[EMAIL PROTECTED]> wrote: > > > I get 1000+ inserts a second for a random 100 byte string > > > insert ( > > > > I get 5+ inserts/sec on my Linux box. > > > > Insert speed is not the issue. It is COMMIT speed. At > > each commit, SQLite waits until all data is on oxide before > > continuing. That will typically take at least two rotations > > of the disk platter, or about 17 millisecond, depending on > > your disk drive. Waiting for data to get to oxide is > > part of being ACID. You can set: > > > >PRAGMA synchronous=OFF; > > > > and your COMMITs will go *much* faster because it will no > > longer wait on the disk drive. But if you lose > > power in the middle of a commit, you might corrupt your > > database file. > > > > Note that there is an implied BEGIN...COMMIT around every > > INSERT statement if you do not explicitly start a transaction > > using your own BEGIN. > > > > -- > > D. Richard Hipp <[EMAIL PROTECTED]> > > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Single row insert speeds
I used the PRAGMA statement and turned off the synchronous option. It made a huge difference. Single inserts were ranging from 5 - 50 ms, now, they're at ~.04 ms. However, I guess there is a tradeoff between the safety of the synchronous operation (in case power is lost) versus the insert speeds. Thanks for the help, Mark On Dec 3, 2007 12:59 PM, <[EMAIL PROTECTED]> wrote: > "P Kishor" <[EMAIL PROTECTED]> wrote: > > I get 1000+ inserts a second for a random 100 byte string > > insert ( > > I get 5+ inserts/sec on my Linux box. > > Insert speed is not the issue. It is COMMIT speed. At > each commit, SQLite waits until all data is on oxide before > continuing. That will typically take at least two rotations > of the disk platter, or about 17 millisecond, depending on > your disk drive. Waiting for data to get to oxide is > part of being ACID. You can set: > >PRAGMA synchronous=OFF; > > and your COMMITs will go *much* faster because it will no > longer wait on the disk drive. But if you lose > power in the middle of a commit, you might corrupt your > database file. > > Note that there is an implied BEGIN...COMMIT around every > INSERT statement if you do not explicitly start a transaction > using your own BEGIN. > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Single row insert speeds
On 12/3/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > "P Kishor" <[EMAIL PROTECTED]> wrote: > > I get 1000+ inserts a second for a random 100 byte string > > insert ( > > I get 5+ inserts/sec on my Linux box. turned off AutoCommit, turned off rand string generation, and lookee what I get... 200,000 inserts in half a second. If my math is not wrong, that works out to more than 360 thousand inserts per sec on the same laptop with a 5400 rpm 2.5" disk. Of course, I batched up the commit at the end. Including the COMMIT, the speed dropped down to almost 67,000 inserts per sec. sqlite> select count(*) from foo; 21 sqlite> All of the below comes from a wiser man than I. > > Insert speed is not the issue. It is COMMIT speed. At > each commit, SQLite waits until all data is on oxide before > continuing. That will typically take at least two rotations > of the disk platter, or about 17 millisecond, depending on > your disk drive. Waiting for data to get to oxide is > part of being ACID. You can set: > >PRAGMA synchronous=OFF; > > and your COMMITs will go *much* faster because it will no > longer wait on the disk drive. But if you lose > power in the middle of a commit, you might corrupt your > database file. > > Note that there is an implied BEGIN...COMMIT around every > INSERT statement if you do not explicitly start a transaction > using your own BEGIN. > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Single row insert speeds
"P Kishor" <[EMAIL PROTECTED]> wrote: > I get 1000+ inserts a second for a random 100 byte string > insert ( I get 5+ inserts/sec on my Linux box. Insert speed is not the issue. It is COMMIT speed. At each commit, SQLite waits until all data is on oxide before continuing. That will typically take at least two rotations of the disk platter, or about 17 millisecond, depending on your disk drive. Waiting for data to get to oxide is part of being ACID. You can set: PRAGMA synchronous=OFF; and your COMMITs will go *much* faster because it will no longer wait on the disk drive. But if you lose power in the middle of a commit, you might corrupt your database file. Note that there is an implied BEGIN...COMMIT around every INSERT statement if you do not explicitly start a transaction using your own BEGIN. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Single row insert speeds
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.02
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]
-
Re: [sqlite] Single row insert speeds
hi,
i am using a "commit-thread":
my application is starting a transaction with "begin" and is inserting a
lot of data at random times,
a thread is doing "commit" and "begin" every second (while locking the
main app of course :-).
this runs very fast and in case of crashes i loose only 1 second of data !
cu, gg
Mark Riehl 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]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Single row insert speeds
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]
-
RE: [sqlite] Single row insert speeds
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]
-
