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?
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.
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.
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.
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.
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.