01-05-2014 01:05 AM
Below query takes a huge time to execute..
select * from work.table1 as t1 where not exists
select * from lib1.table2 as t2
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..?
01-05-2014 03:55 AM
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).
01-05-2014 01:15 PM
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.