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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
VVDR
Obsidian | Level 7

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

View solution in original post

7 REPLIES 7
SASKiwi
Opal | Level 21

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
Obsidian | Level 7

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
Opal | Level 21

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
Obsidian | Level 7

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
Opal | Level 21

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
Opal | Level 21

@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
Obsidian | Level 7

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

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

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

View all other training opportunities.

Discussion stats
  • 7 replies
  • 734 views
  • 2 likes
  • 3 in conversation