Proc SQL Delete Very Slow

[DavidCaliman]

Hi!!

I have two tables:

table1 - 10,000,000 rows

Key: cd1, cd2, cd3, cd4

table2 - 30,000 rows

Key: cd1, cd2

And I want to run the sql below:

proc sql;

delete

from table1 a

where exists (select 1 from table2 b where a.cd1=b.cd1 and a.cd2=b.cd2)

;

This sql took more than three hours and did not finish.

table1 and table2 are SAS tables.

Does anyone have any tips on how to optimize this SQL?

[jakarman]

optimizing.... then do not uses SQL. 

SQL is designed to have the machine decide. When that decisions are not as wanted the human brain should do his work. 

[Ksharp]

I would like to use data step ,when the table is very very big. For you scenario, I recommend Hash Table.

Xia Keshan