Think you're going to have to post an example of how to replicate it, then. A really good way to demonstrate is using self-contained code (the tcl bindings are great for this kind of thing). Your description doesn't match my experience with fts3, so obviously either you're doing something differently than I would, or I'm understanding the operation of your problem differently than your computer is.
-scott On Wed, Nov 4, 2009 at 7:35 AM, sorka <sorka95...@gmail.com> wrote: > > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
