topic SAS/ACCESS to Oracle slow queries in SAS Procedures

SAS/ACCESS to Oracle slow queries

PhilfromGermany — Tue, 06 Jan 2015 09:23:15 GMT

Hi guys, I'm using SAS 9.4 and I have SAS/ACCESS to Oracle installed. Whenever I query data from Oracle, the interface seems to first SELECT * from the oracle tables involved instead of only selecting the colums that I selected in my proc sql statements. For example, when I execute: PROC SQL; select t1.name, t2.age, t1.weight from oratable t1 inner join oratable2 t2 on t1.name = t2.name; quit; Then the log shows that SAS first queries the entire table which is going to be transferred over the network and takes forever: select * from oratable select * from oratable2 This is an abstract from the actual log: NOTE: PROCEDURE PRINTTO used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.01 seconds memory 96.18k OS Memory 23144.00k Timestamp 01/06/2015 09:17:21 AM Step Count 51 Switch Count 112 NOTE: DATA statement used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 599.21k OS Memory 22884.00k Timestamp 01/06/2015 09:17:21 AM Step Count 52 Switch Count 0 ORACLE_49: Prepared: on connection 1 SELECT * FROM oratable.DATA_STRUCTURES ORACLE: DESCRIBE on ORACLE_49 ORACLE_50: Prepared: on connection 1 SELECT "PHASE", "DSD_ID" FROM oratable0.DATA_STRUCTURES WHERE ( UPPER("DSD_bla") = 'bla_bla' ) ORACLE: DESCRIBE on ORACLE_50 ORACLE_51: Executed: on connection 1 SELECT statement ORACLE_50 ORACLE: READBUFF option value set to 682. ORACLE: The fetch time in seconds for 1 rows is 0.000000 ORACLE: Rows fetched : 1 Summary Statistics for ORACLE are: Total row fetch seconds were: 0.001689 Total SQL execution seconds were: 0.000429 Total SQL prepare seconds were: 0.005079 Total seconds used by the ORACLE ACCESS engine were 0.008692 3 The SAS System 08:57 Tuesday, January 6, 2015 NOTE: PROCEDURE SQL used (Total process time): real time 0.30 seconds user cpu time 0.01 seconds system cpu time 0.00 seconds memory 993.53k OS Memory 23312.00k Timestamp 01/06/2015 09:17:21 AM Step Count 53 Switch Count 7 ORACLE_52: Prepared: on connection 1 SELECT * FROM oratable.OBSERVATIONS ORACLE: DESCRIBE on ORACLE_52 ORACLE_53: Prepared: on connection 1 SELECT * FROM oratable.VARIABLES ORACLE: DESCRIBE on ORACLE_53 ORACLE_54: Prepared: on connection 1 SELECT * FROM oratable.REC_MOD ORACLE: DESCRIBE on ORACLE_54 ORACLE_55: Prepared: on connection 1 SELECT * FROM oratable.MOD ORACLE: DESCRIBE on ORACLE_55 ORACLE_56: Prepared: on connection 1 SELECT * FROM oratable.TAXES ORACLE: DESCRIBE on ORACLE_56 SAS_SQL: Unable to convert the query to a DBMS specific SQL statement due to an error. ACCESS ENGINE: SQL statement was not passed to the DBMS, SAS will do the processing. This couldn't possibly be the way the interface is supposed to work. When I run some of these queries directly on Oracle using SQL Developer they take less than a second. In SAS they take 1 minute, because it frist transfers the entire table to the application server. Any suggestions? Thanks!!!

Re: SAS/ACCESS to Oracle slow queries

PhilfromGermany — Tue, 06 Jan 2015 09:25:21 GMT

I have no idea why this forum discards all kinds of formatting when writing posts. This is probably a pain to read now, sorry about that.

Re: SAS/ACCESS to Oracle slow queries

RW9 — Tue, 06 Jan 2015 10:10:38 GMT

Eeek, yes, very hard to read. What I think is happening there is you are passing some SQL to Oracle, that is being executed at the Oracle end with the result of a table being created. That table is then fed through to SAS. That's the normal way. However I think the problem you are seeing is that this is not being done, something is happening which stops the processing being done on the database:

SAS_SQL: Unable to convert the query to a DBMS specific SQL statement due to an error. ACCESS ENGINE: SQL statement was not passed to the DBMS, SAS will do the processing. Maybe post your actual code (in full) for that query as the code you post seems to be SAS SQL (i.e. using work library) rather than Oracle - also try:

proc sql;

create table WANT as

connect to oracle (username=....);

select * from connection to oracle (

select t1.name, t2.age, t1.weight from *schema.table* t1 inner join *schema.table* t2 on t1.name = t2.name);

disconnect from oracle;

quit;

Replace *schema.table* with the data location on the oracle server.

Re: SAS/ACCESS to Oracle slow queries

Ksharp — Tue, 06 Jan 2015 10:40:28 GMT

libname option readbuff=10000 could fast it .

Re: SAS/ACCESS to Oracle slow queries

PhilfromGermany — Tue, 06 Jan 2015 18:54:14 GMT

Thanks for your responses. I already tried tweaking the buffers. I was hoping I wouldn't have to use explicit pass-through but there seems now way around it... Thanks!