Re: Performance problem with DELETE FROM/correlated subqueries

Hi,

On 07.02.20 09:25, Clemens Ladisch wrote:
> Jürgen Baier wrote:
>>    CREATE TABLE main ( ATT1 INT, ATT2 INT, PRIMARY KEY (ATT1,ATT2) );
>>    CREATE TABLE staging ( ATT1 INT, ATT2 INT );
>>
>> Then I execute
>>
>>    DELETE FROM main WHERE EXISTS (SELECT 1 FROM staging WHERE main.att1 = staging.att1 AND main.att2 = staging.att2)
>>
>> which takes a very long time.
> 
> DELETE FROM main WHERE (att1, att2) IN (SELECT att1, att2 FROM staging);

Thank you very much.

I can confirm that this solves my problem and indeed scans the staging 
table and looks up the main table:

sqlite> EXPLAIN QUERY PLAN DELETE FROM main WHERE (att1, att2) IN 
(SELECT att1, att2 FROM staging);
QUERY PLAN
|--SEARCH TABLE main USING INDEX sqlite_autoindex_main_1 (ATT1=? AND ATT2=?)
`--LIST SUBQUERY
    `--SCAN TABLE staging

For reference: This syntax is not supported by Microsoft SQL Server 
(2017). But Microsoft SQL Server is relatively fast when using the 
original DELETE FROM query.

Thanks,

Jürgen

> 
> 
> Regards,
> Clemens
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users