Performance problem with DELETE FROM/correlated subqueries

Hi,

I have a question regarding the performance of DELETE FROM (or maybe 
better: correlated subqueries).

I have a table "main" and a table "staging". In "staging" I have a 
subset of "main". I want to delete all rows from "main" which are also 
in "staging".

   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. As far as I understand the query plan 
SQLite scans the full staging table for each row in "main":

   sqlite> EXPLAIN QUERY PLAN DELETE FROM main WHERE EXISTS (SELECT 1 
FROM staging WHERE main.att1 = staging.att1 AND main.att2 = staging.att2)
      ...> ;
   QUERY PLAN
   |--SCAN TABLE main
   `--CORRELATED SCALAR SUBQUERY
      `--SCAN TABLE staging

How do I speed this up? The idea is that the database should scan 
"staging" and lookup "main" (because an appropriate primary index exists).

But I'm open to any alternative approach. I just have the situation that 
I have a very large "main" table and a "staging" table which contains 
also a large number of tuples which should be deleted from "main".

Any ideas?

Thanks,

Jürgen

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