I'm doing both delete and insert within the same transaction already. The problem is there will alway be a few duplicates out of the hundreds of records so it will always fail. For whatever reason, the delete, even though it's just 2 or 3 records is taking 10 times longer than just the insert alone where I can artificially make a case where there are no duplicates.
Scott Hess wrote: > > My experience suggests that the test before the insert is not quite > expensive, it's just shifting some expense from the insert to the test > (presumably a select). But the overall sequence of: > > BEGIN IMMEDIATE; > DELETE FROM ftstable WHERE docid = ?; > INSERT INTO ftstable (docid, x, y) VALUES (?, ?, ?); > COMMIT; > > Should be more-or-less the same speed as if you ran just the INSERT > without an explicit transaction. Really, you should test it! > > Anyhow, if you're still convinced there's a problem, you should just > do the INSERT, then check whether the INSERT failed due to an index > constraint, then do an UPDATE to set things to your desired values. > That's about as well as fts3 would do internally (fts3 UPDATE is > already implemented as internal-delete-operator followed by > internal-insert-operator). > > Note that the fts3 implementation provides some advantage to doing > updates in order by docid. Optimal would be something like: > > BEGIN IMMEDIATE; > -- for each document to insert, in numerically sorted order > DELETE FROM ftstable WHERE docid = ?; > INSERT INTO ftstable (docid, x, y) VALUES (?, ?, ?); > COMMIT; > > If you're doing the deletes as a separate first pass it will be > somewhat slower, because it is unordered WRT the inserts. If you do > each pass in order, though, the difference might be pretty small. > > [If I misunderstand and your goal is to not insert rows which are > already present, then ... I'm confused. Just insert all the rows you > have, and the ones which are already present will fail to insert, and > that's fine.] > > -scott > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://old.nabble.com/FTS3-IGNORE-OR-REPLACE-----tp26191125p26198341.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
