topic Re: Join from SAS Linux x64 to Oracle db table generating additional long running query on Oracle in SAS Programming

Join from SAS Linux x64 to Oracle db table generating additional long running query on Oracle

KirstenM — Mon, 20 Sep 2021 14:40:19 GMT

When running the following join in SAS Enterprise Guide, an additional query is being generated on the Oracle DB which is extremely long running and is causing production performance issues. Does anyone have any idea how to stop this additional query being generated on Oracle?

Actual query submitted on Linux platform (modified slightly for this platform):

proc sql;
create table work.outputtable as
select t1.filename,
t1.int_cell_phone,
t1.response_dttm,
t1.country,
t2.account_id,
t2.org_id
from work.SAS_table1 t1 inner join
ci_mart.ma_account t2 /*** oracle table ***/
on t1.customer_rk = t2.customer_rk;
quit;

Additional long running query being generated on Oracle "automatically" (ie. not in the submitted code):

SELECT "ACCOUNT_ID", "ORG_ID", "CUSTOMER_RK" FROM CI_MART.MA_ACCOUNT WHERE ((("CUSTOMER_RK"=:"CUSTOMER_RK") OR (("CUSTOMER_RK" IS NULL ) AND ( :"CUSTOMER_RK" IS NULL ))))

Re: Join from SAS Linux x64 to Oracle db table generating additional long running query on Oracle

Sajid01 — Tue, 21 Sep 2021 00:17:24 GMT

The oracle table (ci_mart.ma_account t2) you are connecting to must be a view.

So when a view is referenced, the query referencing the view is executed. This is the normal behavior and cannot be avoided.

Discuss with Oracle team to find a solution.

Re: Join from SAS Linux x64 to Oracle db table generating additional long running query on Oracle

ChrisNZ — Tue, 21 Sep 2021 04:22:43 GMT

This is happening exactly as you'd expect: You are writing a SAS query, and asking SAS to translate it into Oracle Syntax, which it does. You could write your own Oracle query if you preferred.

You also ask SAS to merge a SAS table to an Oracle table. The only way is for SAS to read the whole Oracle table. This is slow.

A better option -if you are only interested in a small subset of the Oracle table- is to upload the keys of interest to Oracle and subset there.

Here, SAS seems to have inserted bind variables (prefixed with a colon). My knowledge of Oracle reaches its limit, and I don't know why this happened. I've never seen SAS do this before. If it's a way to pass key values from SAS to Oracle, it's clever, but probably very inefficient for a large number of keys.

To recap:

- If you merge data in SAS, the Oracle data must be downloaded to SAS. Obviously.

- If the Oracle table is much larger than what you need, you can subset by uploading the desired keys to a temp Oracle table, or by inserting the keys as part of the SAS SQL query in a where clause. The second method is limited to queries with a small number of key values.

Re: Join from SAS Linux x64 to Oracle db table generating additional long running query on Oracle

Sajid01 — Tue, 21 Sep 2021 11:43:51 GMT

Further to what I said earlier I would like clarify that the additional long running query pertains to the view.

Your Oracle team can confirm this.
You can engage with them if there are issues.

Re: Join from SAS Linux x64 to Oracle db table generating additional long running query on Oracle

KirstenM — Thu, 28 Oct 2021 06:53:44 GMT

Hi there Thanks for your feedback. MA_ACCOUNT is not view, it is a table. I have logged a SAS track regarding this.

Re: Join from SAS Linux x64 to Oracle db table generating additional long running query on Oracle

KirstenM — Thu, 28 Oct 2021 06:54:49 GMT

Thanks for your feedback. It's the null queries that Oracle is generating that I am trying to get resolved. I have logged a SAS track.