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

Reply via email to