Proc sql issue during 9.1.3 to 9.3 migration

ngaiwill — Sun, 23 Mar 2014 07:20:32 GMT

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?

Re: Proc sql issue during 9.1.3 to 9.3 migration

LinusH — Sun, 23 Mar 2014 12:04:28 GMT

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.

Re: Proc sql issue during 9.1.3 to 9.3 migration

ngaiwill — Sun, 23 Mar 2014 21:44:29 GMT

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.

topic Re: Proc sql issue during 9.1.3 to 9.3 migration in SAS Programming

Proc sql issue during 9.1.3 to 9.3 migration

Re: Proc sql issue during 9.1.3 to 9.3 migration

Re: Proc sql issue during 9.1.3 to 9.3 migration