DATA Step, Macro, Functions and more

How to improve efficency for the following codes?

Reply
Frequent Contributor
Posts: 99

How to improve efficency for the following codes?

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?

Trusted Advisor
Posts: 2,116

Re: How to improve efficency for the following codes?

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

Frequent Contributor
Posts: 99

Re: How to improve efficency for the following codes?

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

Super User
Posts: 10,035

Re: How to improve efficency for the following codes?

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;

Frequent Contributor
Posts: 99

Re: How to improve efficency for the following codes?

Ksharp,

real time          10:21.99

      CPU time          10.23 秒

almost equal to the former SQL.

Anyway, thanks for giving another thought.

Super User
Posts: 5,430

Re: How to improve efficency for the following codes?

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

Data never sleeps
Frequent Contributor
Posts: 99

Re: How to improve efficency for the following codes?

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

Super User
Posts: 5,430

Re: How to improve efficency for the following codes?

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#n0tcetvx1zpnayn1r8...

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.

Data never sleeps
Super User
Posts: 10,035

Re: How to improve efficency for the following codes?

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.

Ask a Question
Discussion stats
  • 8 replies
  • 310 views
  • 3 likes
  • 4 in conversation