topic Re: sql join run time in SAS Procedures

sql join run time

helloSAS — Mon, 21 Oct 2013 21:18:04 GMT

Can the below query be written in a way that can improve its efficiency? that is to reduce the run time?

Proc sql;

Connect to oracle

create table x as

Select * from connection to oracle

(select cnt.accnt_id,

cnt.a_cd,

cnt.r_cd,

dst.accnt_id

from table1 cnt

left outer join

table2 dst

on cnt.accnt_id=dst.accnt_id

where cnt.ts >= '18-OCT-2013');

Disconnect from oracle;

Quit;

Re: sql join run time

Haikuo — Mon, 21 Oct 2013 21:34:01 GMT

I am no where near a PL/SQL expert, but what is the purpose of this code? will dst.accnt_id somehow be kept? if not, then there is no need to do the join, just do the subset part of it.

Re: sql join run time

Anotherdream — Mon, 21 Oct 2013 22:33:01 GMT

I agree with Hai.kuo. If you are planning on doing something with the information that specific account ID's are not present in Table B but are in Table A, there are better ways to go about getting that information...

Otherwise why are you doing the join to table B?

Assuming your query is correct, there isn't much you can do. You can try either of the following, however the second solution has to do with your Network and if your working on a database located far from your location (i've had this problem before, and it made a 55 minutes query turn into a 2 minute query).

1) Change your where clause into your join clause... This won't help much tho.

Proc sql;

Connect to oracle

create table x as

Select * from connection to oracle

(select cnt.accnt_id,

cnt.a_cd,

cnt.r_cd,

dst.accnt_id

from table1 cnt

left outer join

table2 dst

on cnt.accnt_id=dst.accnt_id and cnt.ts >= '18-OCT-2013');

Disconnect from oracle;

Quit;

2) Make sure your database doesn't have to go over a network if going to another part of the country... If it does, that's a huge problem that you will need to fix through a process or structural change!

Good luck!

Brandon

Re: sql join run time

helloSAS — Tue, 22 Oct 2013 12:56:21 GMT

My bad. I missed a variable that comes from dataset dst. I'm not sure if I'll be able to do anything with the above query to improve performance. Because the same query runs for 15+ mins as well as in 15 secs depending upon different times of the day that it is being run.

Thanks for your help!

Re: sql join run time

SASKiwi — Tue, 22 Oct 2013 19:04:50 GMT

You could try parallelising the query with an Oracle hint to see if that speeds it up.

Try adding this: SELECT /*+ PARALLEL(4) */ to your query.

You will also need to add preserve_comments to your Oracle connection options for this to work - check the SAS doc on Oracle hints.

Re: sql join run time

LinusH — Wed, 23 Oct 2013 06:11:15 GMT

Am I missing something here? This is what I understand a pure Oracle issue, and would be better discussed on an Oracle forum.