BookmarkSubscribeRSS Feed
lambdavu
Fluorite | Level 6

Hi everyone,

 

for some analysis we sometimes need to run in SAS, our IT department set up a set ov views on the SAS 9.3 server that channel data stored in an ORACLE db. The table is huge (hundreds of columns, millions of rows) and if i try to set even a small part of it through a data step it takes a very long time. I think this happens because the whole table is first downloaded to the SAS Server and the rest of the instructions are executed. This is of course very slow and I would like to speed it up limiting as much as possible the data that is downloaded from ORACLE.

 

Running this code

proc sql; 
describe view_lib.v_oratb;
quit;

 

 

I get the following result:

select *

from ORADB.ORATB

where REPORTING_DATE like '1706%M'

using libname ORADB ORACLE PATH=XXXXXXXXX SCHEMA=YYYYYY USER=ZZZZZ PASSWORD=XXXXX;

is there a way to use passthrough or some nifty data step options on a view defined like this to avoid excessive processing time?

 

 

Thanks!

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Could you not create a smaller subset dataset in your Oracle temporary area something like:

proc sql;  
  connect to oracle (...);
  execute by oracle ("create table <tempschema>.test select * from ... where ...;);
  select * from <tempschema>.test...
quit;

Lots of elipses as I have no information, but essentially you let the db filter down your data into a temp user area, then get SAS to retrieve from there.

TomKari
Onyx | Level 15

If it's an Oracle view, it shouldn't be downloading all of the data to your SAS server. However, with another database type I've seen views be very slow. In that case, it was because the result for the view had to be completely resolved before any data was returned, whereas in a normal select the DBMS started returning data immediately.

 

I would suggest setting up a passthrough query that directly runs the select against the Oracle table, instead of using the view. That should be the fastest possible mechanism. Spend some time with different options to speed that up as much as possible, and then compare using the view and the different SAS mechanisms to that.

 

Tom

Patrick
Opal | Level 21

@lambdavu

For pass-through the view would need to be defined in Oracle and not SAS (which would be my preferred method).

 

You can of course always NOT use the view but execute all your code directly via pass-through SQL. And you can get the code of the view with the describe statement so you can get more complex logic directly from there.

 

Having said all that: As long as you're not using functions or formats which the SAS access engine can't push the database, I'd expect all the code to execute on the Oracle side.

If you set options...

options sastrace='d' sastraceloc=saslog nostsuffix; 

....you'll see in the log where things execute.

 

If Oracle returns results as soon as possible or only after the whole result set has been created is as much as I know an Oracle configuration.

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 connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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