topic Proc SQL Delete Very Slow in SAS Procedures

Proc SQL Delete Very Slow

DavidCaliman — Fri, 14 Nov 2014 17:02:42 GMT

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 — Fri, 14 Nov 2014 18:12:39 GMT

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 — Sat, 15 Nov 2014 08:58:12 GMT

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

Xia Keshan