Re: [sqlite] Slow response using WHERE - IN

2014-07-28 Thread 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

Re: [sqlite] Slow response using WHERE - IN

2014-07-28 Thread Clemens Ladisch 
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

Re: [sqlite] Slow response using WHERE - IN

2014-07-27 Thread jose isaias cabrera 


Clemens Ladisch wrote...


On 07/26/2014 06:22 AM, jose isaias cabrera wrote:

   INSERT OR REPLACE INTO LSOpenProjects
SELECT * FROM client.LSOpenProjects WHERE id = 1 AND Date != 
A.Date;


What is A?


Sorry, this should have been written like this.

INSERT OR REPLACE INTO LSOpenProjects
SELECT * FROM client.LSOpenProjects WHERE id = 1 AND Date != 
'2014-04-23';


the id and the date is grabbed from the first step:
Step 1: prepare and execute this command,
   SELECT id,Date FROM LSOpenProjects WHERE login != 'user1';

Then, I will create a huge SQL command using the data grabbed from step one.

thanks.


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

Re: [sqlite] Slow response using WHERE - IN

2014-07-27 Thread Clemens Ladisch 
On 07/26/2014 06:22 AM, jose isaias cabrera wrote:
>INSERT OR REPLACE INTO LSOpenProjects
> SELECT * FROM client.LSOpenProjects WHERE id = 1 AND Date != 
> A.Date;

What is A?


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users