[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-users
Subject:    Re: [sqlite] SQLite Transaction Rate and speed...
From:       "Doug" <pa_ng () poweradmin ! com>
Date:       2009-03-09 18:19:27
Message-ID: 006b01c9a0e3$95a552f0$c0eff8d0$ () com
[Download RAW message or body]

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-bounces@sqlite.org [mailto:sqlite-users-
> bounces@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
[prev in list] [next in list] [prev in thread] [next in thread]