Help using Base SAS procedures

pass through facility very slow

Accepted Solution Solved
Reply
Contributor
Posts: 54
Accepted Solution

pass through facility very slow

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; 

 


Accepted Solutions
Solution
‎12-09-2015 07:28 AM
Super User
Super User
Posts: 7,413

Re: pass through facility very slow

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.

View solution in original post


All Replies
Solution
‎12-09-2015 07:28 AM
Super User
Super User
Posts: 7,413

Re: pass through facility very slow

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.

Super User
Posts: 5,260

Re: pass through facility very slow

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.

Data never sleeps
Respected Advisor
Posts: 3,900

Re: pass through facility very slow

[ Edited ]

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 296 views
  • 0 likes
  • 4 in conversation