BookmarkSubscribeRSS Feed
helloSAS
Obsidian | Level 7

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;

5 REPLIES 5
Haikuo
Onyx | Level 15

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.

Anotherdream
Quartz | Level 8

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

helloSAS
Obsidian | Level 7

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!

SASKiwi
PROC Star

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.

LinusH
Tourmaline | Level 20

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 934 views
  • 6 likes
  • 5 in conversation