Query

Reply
Contributor kds
Contributor
Posts: 31

Query

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

Respected Advisor
Posts: 4,173

Re: Query

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

Super User
Posts: 5,424

Re: Query

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
Super User
Posts: 11,343

Re: Query

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

Ask a Question
Discussion stats
  • 3 replies
  • 234 views
  • 0 likes
  • 4 in conversation