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

List:       sqlite-users
Subject:    Re: [sqlite] SQLite Transaction Rate and speed...
From:       Jim Ursetto <jim+sqlite () 3e8 ! org>
Date:       2009-03-11 8:40:09
Message-ID: 20090311084009.GA4803 () 3e8 ! org
[Download RAW message or body]

At 03:47am on 2009 March 08, VF did write:
> 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.

Is it possible for you to do the following?  The INSERT should fail
silently if you violate a uniqueness constraint; we also set the initial
counter to 0 as it will be immediately bumped to 1 by the UPDATE.  You
should be able to wrap the whole thing in a transaction.

***

INSERT OR IGNORE INTO MAPPINGS_$idx
 (key, mapping, rank, counter, timeCreated, timeModified)
   values (?, ?, 1, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

UPDATE MAPPINGS_$idx SET counter = counter + 1, 
  timeModified = CURRENT_TIMESTAMP WHERE key = ? AND mapping = ?;

-- 
jim@3e8.org / 0x43340710 / 517B C658 D2CB 260D 3E1F  5ED1 6DB3 FBB9 4334 0710
_______________________________________________
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]