On Tue, Nov 3, 2009 at 9:12 PM, sorka <sorka95...@gmail.com> wrote:
> Is there any way to have an intsert into an FTS3 table ignore a row if the
> ROWID being inserted already exists? This is turning out to be quite
> troublesome because I'm inserting thousands of records where just a few like
> 3 or 4 will have the same rowid as existing records. However, to do the test
> prior to insertion to delete the duplicates first is quite expensive. It
> would be much better if the FTS3 insertion routine had the option of
> ignoring rather than failing on the constraint.
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
