pass through facility very slow

[Abraham]

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; 

[RW9 · Accepted solution]

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.

[LinusH]

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.

[Patrick]

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.