topic Re: Duplicate SQL running on Oracle in SAS Programming

Duplicate SQL running on Oracle

DavidCaliman — Tue, 11 Feb 2014 19:53:47 GMT

When I run the PROC SQL below in DI Studio, I saw that at the oracle log, two SQLs are executed.:

PROC SQL;

INSERT INTO ODS.ODS_CUSTOMER (ID,DESC)

SELECT ID, DESC

FROM ORA.CUSTOMER

WHERE ID IN (1,2);

QUIT;

SQL 1) SELECT "ID", "DESC" FROM CUSTOMER WHERE ( ("ID" IN ( 1 , 2 ) ) )

SQL 2) SELECT * FROM CUSTOMER

Can anyone tell me if there is the option to not execute the second SQL (Select * from)? As the table in question is very large, i'm having a performance problem.

Re: Duplicate SQL running on Oracle

DBailey — Tue, 11 Feb 2014 20:53:09 GMT

are both of the tables in oracle?

Re: Duplicate SQL running on Oracle

DavidCaliman — Tue, 11 Feb 2014 22:16:04 GMT

No.

ODS.ODS_CUSTOMER -> SAS table

ORA.CUSTOMER-> Oracle table

Re: Duplicate SQL running on Oracle

Fugue — Tue, 11 Feb 2014 22:58:54 GMT

Since you are using an insert clause, SAS may be passing the entire table to SAS for processing. I'm not familiar with SAS DI, but in foundation SAS you can use SASTRACE= options to determine where the processing is occurring.

Re: Duplicate SQL running on Oracle

Peter_C — Wed, 12 Feb 2014 07:29:35 GMT

Have a look at the SAS system option DBIDIRECTEXEC

Re: Duplicate SQL running on Oracle

DavidCaliman — Mon, 17 Feb 2014 20:28:46 GMT

Using the option DBIDIRECTEXEC the problem persists.

Re: Duplicate SQL running on Oracle

DavidCaliman — Mon, 17 Feb 2014 20:30:29 GMT

With the sastrace option I really identified that SAS is sending two SQL statements to Oracle.

I still can not solve the problem.

Re: Duplicate SQL running on Oracle

Kurt_Bremser — Tue, 18 Feb 2014 08:23:34 GMT

How about using explicit SQL passthrough?

Re: Duplicate SQL running on Oracle

Patrick — Tue, 18 Feb 2014 08:29:58 GMT

You are aware that the SASTRACE option shows you in the log what SQL statements SAS sends to the database to test if they are executable there. Then further down in the log it tells you which SQL actually had been sent to the database for processing.

So if you say there are 2 SQL's sent: Is this just your interpretation of the SASTRACE log messages generated or do you actually see two running SQL's on the database side?

Re: Duplicate SQL running on Oracle

DBailey — Tue, 18 Feb 2014 15:05:55 GMT

proc sql noprint;

connect to oracle as ora (valid connection data would go here);

INSERT INTO ODS.ODS_CUSTOMER (ID,DESC)

select id, desc from connection to ora (

SELECT ID, DESC

FROM ActualOracleSchema.CUSTOMER

WHERE ID IN (1,2)

);

disconnect from ora;

quit;

Re: Duplicate SQL running on Oracle

DavidCaliman — Tue, 18 Feb 2014 16:36:36 GMT

DBailey,

This code is also generating 2 SQLs in the database.

Re: Duplicate SQL running on Oracle

DavidCaliman — Tue, 18 Feb 2014 16:38:41 GMT

I'm looking to the Oracle v$sqlarea view. And there are running the two SQLs.

Re: Duplicate SQL running on Oracle

Reeza — Tue, 18 Feb 2014 16:55:06 GMT

You may want to check with tech support. In SAS there are issues when working across DB's, such as loading entire tables before processing. In a straight insert I can't see why the issue would occur.

Re: Duplicate SQL running on Oracle

Peter_C — Tue, 18 Feb 2014 17:13:09 GMT

David

if you share with us, the whole procedure code and all the sas log generated, including rhe output from sastrace, we might be able to add some more insight.

peterC

Re: Duplicate SQL running on Oracle

DBailey — Tue, 18 Feb 2014 18:56:19 GMT

This code produces two oracle statements, but only one is executed.

options sastrace=',,,d' sastraceloc=saslog;

proc sql;

connect to oracle as CDBMR (path='PMIS' ........);

create table work.test as select * from connection to cdbmr (

select date_value from mdm.dbdates

where date_value= to_date('30Nov2013')

);

DISCONNECT FROM CDBMR;

quit;

Here's the log:

16 proc sql;

17 connect to oracle as CDBMR (path='PMIS' ..........);

19 create table work.test as select * from connection to cdbmr (

20 select date_value from mdm.dbdates

21 where date_value= to_date('30Nov2013')

22 );

ORACLE_4: Prepared: on connection 5

select date_value from mdm.dbdates where date_value= to_date('30Nov2013')

ORACLE_5: Executed: on connection 5

SELECT statement ORACLE_4

NOTE: Table WORK.TEST created, with 1 rows and 1 columns.

What does your log look like?

Re: Duplicate SQL running on Oracle

DavidCaliman — Tue, 18 Feb 2014 19:11:21 GMT

My Log:

729 /*---- Start of User Written Code ----*/

730

731 options sastrace=',,,d' sastraceloc=saslog;

732 proc sql;

733 connect to oracle (user=XXXXX password=XXXXXX);

734 INSERT INTO ODS.ODS_CUSTOMER

735 SELECT *

736 FROM ORA.CUSTOMER

737 WHERE ID IN (1,2);

12 1705687392 Main 0 SQL (2)

ORACLE_4: Prepared: on connection 2 13 1705687392 Main 0 SQL (2)

SELECT * FROM CUSTOMER 14 1705687392 Main 0 SQL (2)

25 The SAS System 13:28 Saturday, January 18, 2014

15 1705687392 Main 0 SQL (2)

16 1705687392 Main 0 SQL (2)

ORACLE_5: Prepared: on connection 2 17 1705687392 Main 0 SQL (2)

SELECT "ID", "DESC" FROM CUSTOMER WHERE ( ("ID" IN ( 1 , 2) ) ) 18 1705687392 Main 0 SQL (2)

19 1705687392 Main 0 SQL (2)

20 1705687392 Main 0 SQL (2)

ORACLE_6: Executed: on connection 2 21 1705687392 Main 0 SQL (2)

SELECT statement ORACLE_5 22 1705687392 Main 0 SQL (2)

23 1705687392 Main 0 SQL (2)

NOTE: No rows were inserted into ODS.ODS_CUSTOMER.

Re: Duplicate SQL running on Oracle

DavidCaliman — Tue, 18 Feb 2014 19:47:30 GMT

DBailey,

I ran your program here and it worked. Ran only 1 sql on database.

How would the adaptation of the command below to insert rather than create?

create table work.test as select * from connection to cdbmr (

20 select date_value from mdm.dbdates

21 where date_value= to_date('30Nov2013')

22 );

Re: Duplicate SQL running on Oracle

Fugue — Tue, 18 Feb 2014 19:58:45 GMT

David - I see only one execution statement in your log, but you are using explicit pass-through this time -- not your original implicit pass-through (libname method) code.

As I stated in my earlier post, SAS may not be able to pass the SQL to Oracle for processing via implicit pass-through (Libname method) because your code contains an INSERT statement. To clarify, the restriction is actually on usage of the INTO clause. (But, the INSERT statement uses the INTO clause so the restriction extends to the INSERT statement.

See pdf page 6 of https://support.sas.com/techsup/technote/ts661.pdf

The purpose of my post here is to explain why you may be seeing two sql statements executing when attempting to use implicit pass-through (libname method) to perform an INSERT.

Re: Duplicate SQL running on Oracle

DBailey — Tue, 18 Feb 2014 20:05:41 GMT

If that is your log, then you aren't running a pass-through query. Looks like you're running a normal statement. To use the pass-through, add "as conn" to your connection.

proc sql;

create table ods.ods_customer like ora.customer;

connect to oracle as conn (path='...' user=XXXXX password=XXXXXX);

insert into ods.ods_customer

select * from connection to conn (

select * from customer where id in (1,2)

);

disconnect from conn;

quit;

Re: Duplicate SQL running on Oracle

Reeza — Tue, 18 Feb 2014 20:09:48 GMT

I don't think that will work, because ods.ods_customer is a SAS table, not on Oracle.