07-02-2018 09:04 AM
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?
07-02-2018 09:23 AM
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.
07-02-2018 10:47 AM
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.
07-03-2018 04:53 AM
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.