topic Re: Heterogenous join of sas dataset with oracle in SAS Programming

Heterogenous join of sas dataset with oracle

nickspencer — Sun, 10 Apr 2022 01:46:38 GMT

Hi all,
I have a sas dataset with around 15k record that I have to do an inner join with an Oracle table. I am doing a heterogeneous join but it is taking extremely long to complete even with options like readbuff, dbkey and multi_datasrc_opt=in.
Is there any other way I could run my program faster?

Thanks,
Nick

Re: Heterogenous join of sas dataset with oracle

Patrick — Sun, 10 Apr 2022 06:34:48 GMT

Load the SAS dataset into Oracle - eventually into a temporary table - to execute the join in-database.

This of course is only worth doing if the inner join will significantly reduce the volume meaning that the resulting table you will load into SAS is much smaller than the source table in Oracle.

Re: Heterogenous join of sas dataset with oracle

Sajid01 — Sun, 10 Apr 2022 12:40:35 GMT

Hello @nickspencer
Optimizing query performance depends on the details of your query and I have a general observation.
You are having a heterogeneous join. if your SAS dataset is the smaller one I would suggest try moving to a temporary table into Oracle and then perform the join and fetch the result.
Do not forget to delete the temporary table in oracle.
Again the devil is in the detail.