topic Re: Delete with an exists statement really slow in SAS Enterprise Guide

Delete with an exists statement really slow

mgm — Wed, 09 Aug 2017 22:27:02 GMT

proc sql;
delete from DATA1 a
	where exists (select 1 from  DATA2 b where a.ID = b.ID and a.ID2 = b.ID2);
quit;

This statement is tremendously slow for a table with 8.3 million rows.

Delete with an exists statement really slow

mgm — Wed, 09 Aug 2017 22:28:06 GMT

proc sql;
delete from DATA1 a
	where exists (select 1 from  DATA2 b where a.ID = b.ID and a.ID2 = b.ID2);
quit;

This statement is tremendously slow for a table with 8.3 million rows, any suggestions on improving the performance ?

Re: Delete with an exists statement really slow

TomKari — Wed, 09 Aug 2017 22:42:20 GMT

Two questions:

First, the obvious one. How "slow" is really slow? How long is it taking?

Second, what kind of tables are "DATA1" and "DATA2". SAS datasets? Database tables accessed via SAS/Access? Other?

And if they're database tables, which technology (Oracle, DB2...)

Tom

Re: Delete with an exists statement really slow

ChrisBrooks — Thu, 10 Aug 2017 00:25:29 GMT

With 8.3M rows I would have thought either indexing the ID and ID2 fields on both files or pre-sorting them by ID and ID2 might help.

Re: Delete with an exists statement really slow

Kurt_Bremser — Thu, 10 Aug 2017 07:22:12 GMT

@mgm wrote:
proc sql;
delete from DATA1 a
	where exists (select 1 from  DATA2 b where a.ID = b.ID and a.ID2 = b.ID2);
quit;
This statement is tremendously slow for a table with 8.3 million rows.

- define "slow". Use options fullstimer to get a clear picture.

- given that all datasets reside in WORK, and the SQL utility file will also be placed there, I'm not surprised that the performance of this step is dismal. Depending on the size of data2, I'd either use sort/data steps, or a format created from data2, or a hash object created from data2.

Re: Delete with an exists statement really slow

LinusH — Thu, 10 Aug 2017 07:26:17 GMT

Correlated subquery with exists can be slow.
Indexing on id could help.
But if your id is unique it could be faster recreating the table with a data step merge (like if a a not b).

Re: Delete with an exists statement really slow

Ksharp — Thu, 10 Aug 2017 13:31:44 GMT

I would try Hash Table if the table is very big.

Or try this one , maybe save you some time.

proc sql;
delete from DATA1
	where catx('|',ID,ID2) in (select catx('|',ID,ID2) from  DATA2 );
quit;