Help using Base SAS procedures

sql join run time

Reply
Frequent Contributor
Posts: 87

sql join run time

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;

Respected Advisor
Posts: 3,124

Re: sql join run time

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.

Super Contributor
Posts: 418

Re: sql join run time

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

Frequent Contributor
Posts: 87

Re: sql join run time

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!

Super User
Posts: 3,100

Re: sql join run time

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.

Super User
Posts: 5,254

Re: sql join run time

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

Data never sleeps
Ask a Question
Discussion stats
  • 5 replies
  • 286 views
  • 6 likes
  • 5 in conversation