topic pass through facility very slow in SAS Procedures

pass through facility very slow

Abraham — Tue, 08 Dec 2015 06:25:39 GMT

Hello Everyone

I want to import around 20 tables from Oracle to SAS. I am using pass through facility like below. But it tooks a lot of time due to huge number of records.

is there any alternative to follow in order to save time.

proc sql;
connect to oracle as ext(user=user orapw=pwd path=abcd);
CREATE TABLE emp as
select * from connection to ext
(select e_id, ename from polaris. emp);
CREATE TABLE utub as
select * from connection to ext
(select r_id, s_id from polaris. utubulu);

create table table3------table 20 like above
disconnect from ext;
quit;

Re: pass through facility very slow

RW9 — Tue, 08 Dec 2015 09:47:04 GMT

If there are a huge nunber of records it is going to take time to process it, there is a physical relationship to number of records and processing time? One way which may be quicker, although I doubt by very much is to goto the Oracle database - i.e. Log in to Toad or SQL Developer, and run the query there, export the result to a CSV file which you can then read into SAS.

Note you seem to have typos in your code:

(select e_id, ename from polaris. emp);

Shouldn't be a space after polaris.

Re: pass through facility very slow

LinusH — Tue, 08 Dec 2015 14:00:34 GMT

Can't see the point of using explicit SQL pass-thru here, since your SQL is not doing any Oracle specific.

Use implicit SQL pass-theu instead, toghether with

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

Which probably gives you some kind of timings in Oracle (so you can rule out network bottlenecks).

If Oracle is slow on pushing data, nothing much to about - talk to the DBAs.

What is the bigger picture here - you seem to copy whole datasets?

Is this a one time job?

If recurrent, perhaps you only need to load the delta.

Or let that data reside in Oracle - if you program wise in SAS, that could work perfectly fine.

Re: pass through facility very slow

Patrick — Wed, 09 Dec 2015 08:48:27 GMT

If you really MUST download all the data from Oracle then one thing which could eventually speed up things is storing the data in SAS using the libname SPDE engine.