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

List:       sqlite-users
Subject:    Re: [sqlite] SQLite Transaction Rate and speed...
From:       "VF" <goodarm () gmail ! com>
Date:       2009-03-08 8:47:56
Message-ID: 6A84771436F94F99B5EDFE45CB840B78 () xps
[Download RAW message or body]

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
[prev in list] [next in list] [prev in thread] [next in thread]