On 10/6/2011 10:43 PM, Ivan Shmakov wrote:
Jim Morris writes:
> The recent thread may relate: "[sqlite] Is there an efficient way to
> insert unique rows (UNIQUE(a, b, c)) into an fts3 virtual table?"
> INSERT INTO fts3_table (a,b,c)
> SELECT 'an A','a B','a C'
> WHERE NOT EXISTS
> (SELECT DISTINCT a,b,c
> FROM fts3_table
> WHERE a='an A' AND b='a B' AND c='a C');
> The above SQL could be adapted to your schema. As mentioned, the
> performance will be slower than a straight insert.
Thanks. It's a solution not quite for the problem I'm having,
but I'll probably stick to it (and to the denormalized schema it
imples.)
However, I wonder, would the following (slightly more concise)
query imply any performance loss in comparison to the one above?
INSERT INTO fts3_table (a, b, c)
SELECT 'an A', 'a B', 'a C'
EXCEPT SELECT DISTINCT a, b, c
FROM fts3_table;
Also, I'm curious if DISTINCT may cause any performance loss in
the case that the columns in question are constrained by an
UNIQUE index? Like:
CREATE UNIQUE INDEX "foo-unique"
ON "foo" (a, b, c);
I don't know for sure. You would need to do some testing to determine
performance issues.
I wouldn't use distinct if the values are already guaranteed to be
unique or in a not exits clause since it may have additional overhead.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users