jose isaias cabrera wrote:
> This command,
> INSERT OR REPLACE INTO LSOpenProjects
> SELECT * FROM client.LSOpenProjects as A
> WHERE id IN
> (
> SELECT id from LSOpenProjects
> WHERE login != 'user1' AND Date != A.Date
> );
>
> is about 20 slower than if I follow this two step process:
>
> Step 1: prepare and execute this command,
> SELECT id,Date FROM LSOpenProjects WHERE login != 'user1';
>
> Step 2: Use the result from that SELECT to create a set of INSERTS like this
> (say that the result provides 20 ids and Dates), I build a huge statement
> like this one...
>
> INSERT OR REPLACE INTO LSOpenProjects
> SELECT * FROM client.LSOpenProjects WHERE id = 1 AND Date != '2014-04-23';
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'
);
Regards,
Clemens
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
