BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ngaiwill
Calcite | Level 5


Hi all,

We're undergoing migration from 9.1.3 to 9.3. As part of that we've created direct feeds into libraries of our Datawarehouse oracle databases on the server. However I cannot get my 9.1.3 queries to work as quickly as they did without the oracle connection and libname code in the query. In 9.1.3 it was

Proc SQL NoPrint;

connect to oracle (user=xxxxx password=xxxxx path='oraprd12.clear1');

drop table prod.prod_acct_2mths;

create table prod.prod_acct_2mths as select * from connection to oracle

(select distinct

e.ac_ar_id, e.ac_no, ph.pg4, ph.pg6, ph.pd_id

from tdw.jnl_det b, tdw.ac_ar e, Acct_mth_2 g, tdw.cst cst,

prod_hierarchy ph

where b.rev_pd_id = ph.pd_id

and e.ac_ar_id=b.ac_ar_id

and b.rpt_amt > 0

and b.sro_id = 1 

and b.acct_mnth = g.Year_Month

and cst.cst_id = e.p_cst_id

and cst.cst_mkt_seg_id in (1164337)

group by e.ac_ar_id, e.ac_no, ph.pg4, ph.pg6, ph.pd_id);

Disconnect from oracle;

quit;

Now that the libraries are permanently available I've been using this.

Proc SQL NoPrint;

drop table prod.prod_acct_2mths;

create table prod.prod_acct_2mths as select distinct

e.ac_ar_id, e.ac_no, ph.pg4, ph.pg6, ph.pd_id

from tdw.jnl_det b, tdw.ac_ar e, Acct_mth_2 g, tdw.cst cst,

prod_hierarchy ph

where b.rev_pd_id = ph.pd_id

and e.ac_ar_id=b.ac_ar_id

and b.rpt_amt > 0

and b.sro_id = 1 

and b.acct_mnth = g.Year_Month

and cst.cst_id = e.p_cst_id

and cst.cst_mkt_seg_id in (1164337)

group by e.ac_ar_id, e.ac_no, ph.pg4, ph.pg6, ph.pd_id;

quit;

However it takes hours in 9.3 compared to minutes in the 9.1.3 environment. What am i doing wrong?

1 ACCEPTED SOLUTION

Accepted Solutions
ngaiwill
Calcite | Level 5

Thanks! have got the system admins looking at it for me. Yes it is onto a different server and the oracle databases are a new connection type for this server, so I'm thinking its the configuration or bandwith.

View solution in original post

2 REPLIES 2
LinusH
Tourmaline | Level 20

The difference are unlikely the SAS version, rather that you are going from explicit SQL pass-to to using implicit pass-thru. Add options sastrace=',,,d' sastreceloc=saslog msglevel=i; to your program and see what's going on with the Oracle connection.

Also, is 9.3 installed on a new server? If so, other parameters such as bandwidth to the Oracle server, server configuration etc can play a part.

Data never sleeps
ngaiwill
Calcite | Level 5

Thanks! have got the system admins looking at it for me. Yes it is onto a different server and the oracle databases are a new connection type for this server, so I'm thinking its the configuration or bandwith.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 2 replies
  • 382 views
  • 3 likes
  • 2 in conversation