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;
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.
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
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!
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.
Am I missing something here? This is what I understand a pure Oracle issue, and would be better discussed on an Oracle forum.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.