topic Re: How to improve efficency for the following codes? in SAS Programming

How to improve efficency for the following codes?

bbb_NG — Mon, 07 May 2012 09:46:59 GMT

proc sql;

create TABLE thesis.Slideback_F AS

select t1.'HUB Cust Id'n ,t1.'Version Date'n

from thesis.Slideback_v1 as t1 inner join cnmap.VIEW_MAPRH_M_CN_CUSTOMER_PFS as t2

on t1.'HUB Cust Id'n =t2.'HUB Cust Id'n and t1.'Version Date'n =t2.'Ver Date'n

where (t1.'Version Date'n-t2.'Date Requested Added'n)/3600/24>=120;

Quit;

Note:'HUB Cust Id'n is customer id,'Version Date'n is like '30SEP2011:00:00:00'

'HUB Cust Id'n +'Version Date'n is the primary key in

cnmap.VIEW_MAPRH_M_CN_CUSTOMER （oracle database

）

observation in thesis.Slideback_F is 7300.

observation in cnmap.VIEW_MAPRH_M_CN_CUSTOMER_PFS is about 24 months * 100k(per month)

sas log

real time 10:19.60

CPU time 9.48 s

how to short real time based on the above code?

Re: How to improve efficency for the following codes?

Doc_Duke — Mon, 07 May 2012 20:20:10 GMT

need more information.

Is thesis also an oracle database on the same server?

If not, then SAS is transferring all the data from the Oracle database to your SAS server for execution. That would involve a lot of network and disk I/O and explain the discrepancy between the real time and the CPU time.

Two ways to shorten the real time:

-- move the thesis data into tables on the Oracle database so the join executes there.

-- if that is not feasible, subset your Oracle data before transferring it to SAS.

Doc Muhlbaier

Duke

Re: How to improve efficency for the following codes?

bbb_NG — Tue, 08 May 2012 01:44:06 GMT

Duke,

Thanks for your reply.

1.Thesis is a library opened in sas server, that is to say thesis.Slideback_F is stored in sas server.

2.The oracle database is readable only, can not put thesis.Slideback_F there.

3.Can you explain in detail that how to "subset your Oracle data before transferring it to SAS"?

Thanks.

Dawn

Re: How to improve efficency for the following codes?

Ksharp — Tue, 08 May 2012 09:47:00 GMT

How about put them all into a condition ? and try to balance equation .

on t1.'HUB Cust Id'n =t2.'HUB Cust Id'n and t1.'Version Date'n =t2.'Ver Date'n

and t1.'Version Date'n>=(120*3600/24) + t2.'Date Requested Added'n;

Re: How to improve efficency for the following codes?

bbb_NG — Tue, 08 May 2012 10:07:13 GMT

Ksharp,

real time 10:21.99

CPU time 10.23 秒

almost equal to the former SQL.

Anyway, thanks for giving another thought.

Re: How to improve efficency for the following codes?

LinusH — Tue, 08 May 2012 10:18:00 GMT

The real time is the clock time from the start of Proc SQL.

CPU time is the SAS part of the execution. So, one can assume that most CPU cycles is used to extract data from Oracle.

Once in SAS, and the join, it will seems pretty fast.

I don't know about Oracle index strategies, but it's usually a problem to have a calculation in the where, since then the optimizer will use full tablescan strategy.

By adding

options sastrace=',,,d' sastraceloc=saslog;

Oracle will echo the Oracle part of the query. I suggest you use that to discuss to an Oracle DBA/expert about optimize that part of the query.

/Linus

Re: How to improve efficency for the following codes?

bbb_NG — Tue, 08 May 2012 10:22:31 GMT

Linus,Thanks for your suggestion.I'll try.

Re: How to improve efficency for the following codes?

LinusH — Tue, 08 May 2012 11:13:57 GMT

Another option if the Thesis.Slideback_V1 is substantially smaller than the Oracle table, try the DBKEY= option:

http://support.sas.com/documentation/cdl/en/acreldb/63144/HTML/default/viewer.htm#n0tcetvx1zpnayn1r8actrkjrd5o.htm

If you can at least get the create temporary table authorization in Oracle, you can look at the Oracle temporary tables support for SAS/ACCESS (also in the online doc). Use that to upload your thesis table to Oracle.

Re: How to improve efficency for the following codes?

Ksharp — Wed, 09 May 2012 08:01:34 GMT

Haha. That doesn't work.

Maybe you can try some options to speed it. Still I don't know whether it is worked.

System Option Description

DBIDIRECTEXEC= Controls SQL optimization for SAS/ACCESS engines.