Unable to execute SQL Query using SAS

[manishiiita]

SAS 9.4, Oracle DB

I am facing issues while executing SQL Query from SAS. This is a simple join query involving 5-6 tables. I'm able to execute it within seconds from SQL Developer while it is taking very long time and going out of TEMP tablespace when I execute it from SAS. I am using SQL Bypass to execute these Oracle queries from SAS Jobs. Please note we have enough Temp tablespace (~100 GB) free with us.

Can you please guide if I should look into some connection properties between SAS & Oracle DB? Are there any parameters used in Oracle for better processing with SAS? Thanks in advance for your help.

I am adding code below for your reference:

select TRN.TRANSACTION_KEY, ALT.TEMP_NUM_1_VALUE as alert_id

from FSK_TEMP_TABLE ALT

join FSK_ACCOUNT_KEYS_PTY KEYS on ALT.TEMP_NUM_1_VALUE = KEYS.alert_id

join FSK_REPLICATION_DATES DTE on ALT.TEMP_NUM_1_VALUE = DTE.alert_id

join fsc_account_event_fact FCT on FCT.account_key = KEYS.account_key

join FSC_TRANSACTION_DIM TRN on FCT.transaction_key = TRN.transaction_key

where FCT.date_key <= 20180603;

[PeterClemmensen]

Post your code?

[manishiiita]

Updated question, added code over there.

[kiranv_]

where are you creating table in SAS or Oracle. please provide complete query, so that someone can help you.

[manishiiita]

This is a part of SAS jobs, only select query is called in this step using SQL bypass.

[kiranv_]

 this means you are doing something like 

proc sql;
connect to oracle(user=user password=password path=path);
create table work.job204 as
select * from connection to oracle
(select * from Employees where jobcode=204);
quit;

 and not below one. first one is moving data to SAS and second one is creating table in oracle itself. First one will take time because now you are moving data from oracle to SAs, but not in Second one. In second query, time of running query should be about same what you have in sql developer

proc sql;
connect to oracle (server=myserver user=myuserid pw=mypass );

execute(create somedb.staging_customer as
select * from edwwrkuser.Cusomter table
where create_dt between ‘2017-01-01’ and ‘2017-01-31’ )
execute(commit work) by oracle;
disconnect from oracle;
quit;

[manishiiita]

I am posting queries using which I have tested from SAS (SQL bypass):

proc sql;
connect to &dbflavor as &dbflavor (authdomain="&segkcAuthDomain." &segKCDBConnOpts);
create table WORK.ABC as
select * from

connection to &dbflavor
            (
             select TRN.*, ALT.TEMP_NUM_1_VALUE as alert_id
             from &segKCSchema..FSK_TEMP_TABLE ALT
             join &segKCSchema..FSK_ACCOUNT_KEYS_PTY KEYS on ALT.TEMP_NUM_1_VALUE = KEYS.alert_id
             join &segKCSchema..FSK_REPLICATION_DATES DTE on ALT.TEMP_NUM_1_VALUE = DTE.alert_id
             join &segKCSchema..fsc_account_event_fact FCT on FCT.account_key = KEYS.account_key
             join &segKCSchema..FSC_TRANSACTION_DIM TRN on FCT.transaction_key = TRN.transaction_key
             where FCT.date_key > DTE.start_date_key and
                        FCT.date_key <= 20180603
               );

disconnect from &dbflavor;
quit;

&segKCSchema refers to my schema user (authdomain). Is there any oracle connection properties/parameters level tuning for SAS?
Please let me know in case of any further queries.

[manishiiita]

@kiranv_, Thank you for your valuable suggestion. I understood your point but the limitation is that, I can't make suggested changes. I am working on SAS Solution (AML) where I am using OOB jobs for alert generation process. The source codes for this job are encrypted/hidden and non-accessible. Neither, I can write it from scratch because there are interdependent processes.

So, considering this fact that I am forced to use below approach:

proc sql;
connect to oracle(user=user password=password path=path);
create table work.job204 as
select * from connection to oracle
(select * from Employees where jobcode=204);
quit;

Can you please help me to optimize it further? Are there any tuning parameters at oracle level or connection level (Oracle JDBC connection) which I can use to optimize my process? That's why, my question is more focused around tuning jobs using oracle parameters / connection properties.

[kiranv_]

this is tricky. Teradata has fast export, which exports rows in bulk instead of row wise from Teradata to SAS. I do not know anything of that sort in oracle. but below few things which I can think may help you optimize moving data from oracle to SAS

1. do not do move data to SAS unless something statistical has to be done, remaining every other can be done in DBMS. Only bring final dataset to SAS, so that you can do the statiisical analysis

2. instead of select * use column names and bring only those columns which you will use in your analysis.

3. bring only amount of data you need for your analysis by using where clause in your explicit pass through.

[manishiiita · Accepted solution]

I have found drastic change in performance after adding an index on FSC_TRANSACTION_DIM on (transaction_key, date_key). Now, above query is finishing in seconds and complete job is taking 15-20 mins. 

Thank you all for your valuable response.