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?
