Performnce Issue: Merge Update Insert with SAS Pass through facility

[VVDR]

Hi All, 

I am facing performance issue when I am using Method 1. Now, I have to fix the performance issue by re-writing the query. 

Method 1. 

proc sql;

   connect to ORACLE

   (

       DBEXT=XXXX BUFF=XXXXX PATH=XXXXX DOMAIN="XXXX"

   );

   execute

   (

      MERGE INTO

         Lib1.Target

      USING

         Lib1.Source

      ON

         (

            Source.A_KEY = Target.A_key AND

            Source.P_KEY = Target.P_KEY AND

            Source.R_KEY = Target.R_KEY    )

      WHEN MATCHED THEN UPDATE

         set

            Target.col1= Source.col1,

            Target.col2= Source.col2,

            Target.col3= Source.col3,

       WHEN NOT MATCHED THEN INSERT

         (

            Target.A_KEY,

            Target.P_KEY,

            Target.R_KEY,

         )

         values

         (  

            Source.A_KEY,

            Source.P_KEY,

            Source.R_KEY,

         )

   ) by ORACLE;

disconnect from ORACLE;

quit;

ROCEDURE SQL used (Total process time):
real time 30:45.15

I Need to fix the performance. 

Can anyone please suggest on this.

Regards

Rama Goteti.

[SASKiwi]

From what I see both your SOURCE and TARGET tables are in Oracle and all processing is done in Oracle - no SAS involvement at all except for submitting the query. Run your query in SQLPLUS and verify if you have the same performance or not. Also get advice from your Oracle DBA regarding the most efficient techniques used at your site.

[VVDR]

Hi SASKiwi,

Thanks for your quick reply.

Before I put it in Oracle team's review, I just wanted to separate the query and run just to see how the performance is improved. 

Please suggest the best way to check the alternative methods of handling this at SAS level.

Regards

Rama Goteti

[SASKiwi]

Custom pass through queries are generally the most efficient way to do external database processing from SAS. Adding SAS-type processing is highly unlikely to speed things up. You need to tune the query you have and your own Oracle people are in the best position to help.

One thought might be to simplify your query by first deleting rows to be updated, then just do a simple insert from your source table. However I don't know the structure of your data to verify whether this is possible or not.

[VVDR]

Hi,

As a reply to this--

One thought might be to simplify your query by first deleting rows to be updated, then just do a simple insert from your source table. However I don't know the structure of your data to verify whether this is possible or not.

I have few other columns that are not getting changed upon this Update statement. Hence, I can't implement this as a viable solution.

I am also thinking of HASH technique to implement in this. Don't know how it will go, but just a thought. 🙂

Appreciate your support !!

[SASKiwi]

I don't know anything about Oracle hash solutions. A SAS hash solution is not applicable here.

If I were faced with this I would do an "explain plan" to see what Oracle is doing behind the scenes, check appropriate indexes are applied, maybe explore Oracle hints to see if they help.

I suggest you also post your question on an Oracle forum as there is bound to be more Oracle tuning experts there.

[Patrick]

@VVDR 

With source and target both being in Oracle using explicit pass-through SQL is the right way to go which also gives you most flexibility. 

When it comes to developing and tweaking such code then I use normally a client like SQL Developer or Dbiever. 

I don't know anything about the data volumes nor your environment so can't say if the execution times are reasonable or not.

Running an explain for the code directly via a client (like SQL Developer) should tell you where you spend most time. 

As a guess: You either don't have appropriate indexes defined and end up with full table scans or then the statistics are not updated.

To update the statistics below a piece of code "as is" which I've used in a past project. You will have to amend the code for your purposes.

/* update index stats on Ora target tables*/
%macro analize_index_compute_stat();
  %local _source_tbl _ora_user;
  %do i=0 %to %eval(&_output_count - 1);
    %let _source_tbl=%upcase(&&_output&i);

    %let _ora_user=;
    proc sql noprint;
      select sysvalue into :ora_user 
      from dictionary.libnames
        where libname="%scan(&_source_tbl,1,.)" 
      ;
    quit;

    proc sql;
      connect to oracle as ora
          ( &ora_connection_string );
      execute by ora
        (
          begin
            for i in 
              (
                select INDEX_NAME from USER_INDEXES
                where table_name=%unquote(%nrbquote(')%scan(&_source_tbl,-1,.)%nrbquote('))
              )
              LOOP
                execute immediate 'ANALYZE INDEX '||%nrbquote(')%trim(&ora_user).%nrbquote(')||i.INDEX_NAME||' COMPUTE STATISTICS';
            end loop;
          end;
        );
      disconnect from ora;
    quit;
  %end;
%mend;
%analize_index_compute_stat();

An additional option could be to use Oracle hints to have the Oracle SQL execute in parallel (you need to use Proc SQL option PRESERVE_COMMENTS for the SAS compiler not to remove such hints as comments).

....and as @SASKiwi already proposed: We are SAS experts. Why not ask an Oracle expert if you've got a problem with an Oracle only SQL.

[VVDR · Accepted solution]

Hello All,

I have posted the same query in SQL forum. Thanks  @SASKiwi  for this thought. It saved a lot of time.

I was given a simple solution as mentioned in this link.

See: https://jonathanlewis.wordpress.com/2016/06/06/merge-precision/

Cheers

Rams