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
