BookmarkSubscribeRSS Feed
kds
Calcite | Level 5 kds
Calcite | Level 5

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..?

3 REPLIES 3
Patrick
Opal | Level 21

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).

LinusH
Tourmaline | Level 20

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.

Data never sleeps
ballardw
Super User

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

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1222 views
  • 0 likes
  • 4 in conversation