Proc sql issue during 9.1.3 to 9.3 migration

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Proc sql issue during 9.1.3 to 9.3 migration


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?


Accepted Solutions
Solution
‎03-23-2014 05:44 PM
New Contributor
Posts: 2

Re: Proc sql issue during 9.1.3 to 9.3 migration

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


All Replies
Super User
Posts: 5,429

Re: Proc sql issue during 9.1.3 to 9.3 migration

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
Solution
‎03-23-2014 05:44 PM
New Contributor
Posts: 2

Re: Proc sql issue during 9.1.3 to 9.3 migration

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 176 views
  • 3 likes
  • 2 in conversation