Re: [sqlite] Slow response using WHERE - IN

[jose isaias cabrera]

Clemens Ladisch wrote...
The first command is slow because the subquery generates lots of results,
but because of the reference to A.Date, it is a correlated subquery and
must be re-executed for each row in A.
If a have understood the query correctly, you want to check whether
a corresponding row with a different date exists.  Do it like this:

 INSERT OR REPLACE INTO LSOpenProjects
   SELECT * FROM client.LSOpenProjects AS A
     WHERE EXISTS
     (
       SELECT 1 FROM LSOpenProjects
         WHERE id = A.id
           AND Date != A.Date
           AND login != 'user1'
     );

Thanks, Clemens.  This works.  Slower than the two SELECT steps, but, 
nonetheless it works much faster than the other one.  I think I know why the 
other one works faster as the preparation is done in memory and not on the 
hard drive.  Thanks so much.

josé 

_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users