BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
manishiiita
Quartz | Level 8

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;

1 ACCEPTED SOLUTION

Accepted Solutions
manishiiita
Quartz | Level 8

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.

View solution in original post

9 REPLIES 9
manishiiita
Quartz | Level 8
Updated question, added code over there.
kiranv_
Rhodochrosite | Level 12

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

manishiiita
Quartz | Level 8
This is a part of SAS jobs, only select query is called in this step using SQL bypass.
kiranv_
Rhodochrosite | Level 12

 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
Quartz | Level 8

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
Quartz | Level 8

@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_
Rhodochrosite | Level 12

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
Quartz | Level 8

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.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1516 views
  • 2 likes
  • 3 in conversation