[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-users
Subject:    Re: [sqlite] Slow response using WHERE - IN
From:       "jose isaias cabrera" <cabrera () wrc ! xerox ! com>
Date:       2014-07-28 18:46:37
Message-ID: 4080ACAF7FF04DA584DE65A9C1227AA8 () stso ! mc ! xerox ! com
[Download RAW message or body]

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
[prev in list] [next in list] [prev in thread] [next in thread]