topic Re: Query in SAS Programming

Query

kds — Sun, 05 Jan 2014 06:05:19 GMT

Hi Experts,

Below query takes a huge time to execute..

select * from work.table1 as t1 where not exists
(
select * from lib1.table2 as t2
where
t2.column1=t1.column1 and
t2.column2=t1.column2 and
t2.column3=t1.column3 and
.....
.....
.....
t2.column14=t1.column14 and
t2.column15=t1.column15
);

If table1 has around 15000 rows and table2 around 20000000, then it takes around 15 hours for the above query to execute.

What could be the cause for query taking so long?
Is there any way I can rewrite the above query to reduce execution time..?

Re: Query

Patrick — Sun, 05 Jan 2014 08:55:11 GMT

Is lib1.table2 a SAS or a database table?t

With your syntax 15T passes through table2 with 20M rows are required. That's a lot of disk I/O.

If both tables are in SAS then I would go for a data step approach where you load the 15T table into a hash and then in a data null step have one pass through the 20M table. If there is a match with the hash delete the row(s) in the hash. At the very end write the hash table to a normal SAS table (output method).

Re: Query

LinusH — Sun, 05 Jan 2014 18:15:38 GMT

20M rows ain't that much, so I think you could pre sort both tables and use a merge by approach. Perhaps not as efficient as hash tables, but easier to code.

Re: Query

ballardw — Mon, 06 Jan 2014 16:24:10 GMT

I'm not sure if it would run any faster but this looks like you might investigate and EXCEPT operation.