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