Not sure if it would work in your situation, but I got a good performance boost in a similar situation by essentially queuing all the updates in memory (not using SQLite), and eventually flushing thousands of queued updates as a single transaction. Worked great and was simple to implement, with the caveat that some items were 'processed' but in volatile memory longer than they might have been otherwise.
Doug > -----Original Message----- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of VF > Sent: Sunday, March 08, 2009 3:48 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] SQLite Transaction Rate and speed... > > Hi SQLite Gurus, > > I am a pretty new SQLite user, and looks like (from the research I've > made > so far) I am facing a pretty typical problem with the product - > performing > millions upserts as fast as possible. The good news is - there's no > concurrent access involved - it's pretty much a single Perl script > which > processes text and inserts into SQLite DB. The bad news - upsert of > hundreds > of thousands of rows takes hours (!). > > Here's the schema of my table (it's split mod 10): > > create table MAPPINGS_$idx ( > key VARCHAR(32) NOT NULL > , mapping VARCHAR(32) NOT NULL > , rank CHAR(2) NOT NULL > , counter INTEGER NOT NULL > , timeCreated DATE NOT NULL > , timeModified DATE NOT NULL > ); > CREATE UNIQUE INDEX MAP_IDX_$idx ON MAPPINGS_$idx (key, mapping); > CREATE INDEX KEY_IDX_$idx ON MAPPINGS_$idx(key); > > I am trying to do an upsert with the following logic: > > UPDATE MAPPINGS_$idx > SET counter = counter + 1 > , timeModified = CURRENT_TIMESTAMP > WHERE > key = ? AND > mapping = ?; > IF rowcount == 0 -- here tried rowid and other variations, too, it > still > fails at he IF > BEGIN > INSERT INTO MAPPINGS_$idx ( > key > , mapping > , rank > , counter > , timeCreated > , timeModified > ) values ( > ? > , ? > , 1 > , 1 > , CURRENT_TIMESTAMP > , CURRENT_TIMESTAMP > ) > END; > > Unfortunately, it fails. I ended up having separate update, check the > number > of rows modified, and if 0 - do insert in a separate statement. It > works, > but painfully slow. Because of this I can't do BEGIN/COMMIT transaction > from > the SQL. I am using Perl DBI interface. I made sure Per's AucoCommit is > turned off, but I am not sure it still prevents SQLite engine to do > commit > after each upsert. And 'pragma synchronous=off' doesn't seem to work > either > (or else I am using it incorrectly). > > Any suggestion would be highly appreciated (ideally with some Perl > examples). > > Thanks in advance, Bobby > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
