Help using Base SAS procedures

SAS/ACCESS to Oracle slow queries

Reply
Contributor
Posts: 39

SAS/ACCESS to Oracle slow queries

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!!!

Contributor
Posts: 39

Re: SAS/ACCESS to Oracle slow queries

Posted in reply to PhilfromGermany

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.

Super User
Super User
Posts: 7,955

Re: SAS/ACCESS to Oracle slow queries

Posted in reply to PhilfromGermany

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.   

Super User
Posts: 10,028

Re: SAS/ACCESS to Oracle slow queries

Posted in reply to PhilfromGermany

libname option readbuff=10000  could fast it .

Contributor
Posts: 39

Re: SAS/ACCESS to Oracle slow queries

Posted in reply to PhilfromGermany

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!

Ask a Question
Discussion stats
  • 4 replies
  • 911 views
  • 1 like
  • 3 in conversation