BookmarkSubscribeRSS Feed
bbb_NG
Fluorite | Level 6

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?

8 REPLIES 8
Doc_Duke
Rhodochrosite | Level 12

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

bbb_NG
Fluorite | Level 6

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

Ksharp
Super User

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;

bbb_NG
Fluorite | Level 6

Ksharp,

real time          10:21.99

      CPU time          10.23 秒

almost equal to the former SQL.

Anyway, thanks for giving another thought.

LinusH
Tourmaline | Level 20

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
bbb_NG
Fluorite | Level 6

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

LinusH
Tourmaline | Level 20

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
Ksharp
Super User

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.

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1053 views
  • 3 likes
  • 4 in conversation