Very Computer

I have a very large table with > 1 million entries and I wish to delete rows
which match any entries in a second table.
What is the most efficient way of doing this?

CREATE TABLE AA (keyA integer NOT NULL, info TEXT);
CREATE TABLE obsolete_AA_entries (keyA integer NOT NULL);

I want to remove all entries from AA where keyA matches that from
obsolete_AA_entries,
i.e. SELECT * FROM AA NATURAL JOIN obsolete_AA_entries;

Both of the tables are UNIQUE indiced on keyA.

DELETE FROM AA WHERE EXISTS(
          SELECT * from obsolete_AA_entries o where AA.keyA = o.keyA);
seems to be faster than
DELETE FROM AA where (keyA) in (SELECT * from obsolete_AA_entries);

However, both are sequentially going through AA which is huge rather than
looking up values one by one from obsolete_AA_entries which is small.
How do I persuade the database to change its query strategy?
Thanks a lot
Llew

Quote:> What is the most efficient way of doing this?

> CREATE TABLE AA (keyA integer NOT NULL, info TEXT);
> CREATE TABLE obsolete_AA_entries (keyA integer NOT NULL);

> I want to remove all entries from AA where keyA matches that from
> obsolete_AA_entries,
> i.e. SELECT * FROM AA NATURAL JOIN obsolete_AA_entries;

> Both of the tables are UNIQUE indiced on keyA.

> DELETE FROM AA WHERE EXISTS(
>           SELECT * from obsolete_AA_entries o where AA.keyA = o.keyA);
> seems to be faster than
> DELETE FROM AA where (keyA) in (SELECT * from obsolete_AA_entries);

in mssql i would try delete via

  delete aa from obsolete_AA_entries o inner join aa a on o.keyA =
a.keyA

but your code seems to be pl-sql(oracle) or something else but not for
mssql because t-sql
(thats also used by the sybase derivative mssql) not uses ';'.
Sure having asked the appropriate group?
Greetings

     Robert

Robert,

Beginning with SQL Server 7.0, T-SQL accepts semi-colons ... probably to
meet ANSI SQL compliance requirements.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

Quote:> > What is the most efficient way of doing this?

> > CREATE TABLE AA (keyA integer NOT NULL, info TEXT);
> > CREATE TABLE obsolete_AA_entries (keyA integer NOT NULL);

> > I want to remove all entries from AA where keyA matches that from
> > obsolete_AA_entries,
> > i.e. SELECT * FROM AA NATURAL JOIN obsolete_AA_entries;

> > Both of the tables are UNIQUE indiced on keyA.

> > DELETE FROM AA WHERE EXISTS(
> >           SELECT * from obsolete_AA_entries o where AA.keyA = o.keyA);
> > seems to be faster than
> > DELETE FROM AA where (keyA) in (SELECT * from obsolete_AA_entries);

> in mssql i would try delete via

>   delete aa from obsolete_AA_entries o inner join aa a on o.keyA =
> a.keyA

> but your code seems to be pl-sql(oracle) or something else but not for
> mssql because t-sql
> (thats also used by the sybase derivative mssql) not uses ';'.
> Sure having asked the appropriate group?
> Greetings

>      Robert

Actually, from SQL7 you can use ; to terminate your statements.

Then again, he used NATURAL JOIN which is not present in T-SQL. But
that might have been for brevity.

--
Erland Sommarskog, Abaris AB

SQL Server MVP