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