Forum Stats

  • 3,784,122 Users
  • 2,254,894 Discussions
  • 7,880,697 Comments

Discussions

Performance Issue: Merge Update Insert

Rama_G
Rama_G Member Posts: 14
edited Aug 22, 2019 7:42AM in SQL & PL/SQL

Hi Team,

In my existing code we are using Merge, Update when match and Insert when not matched. However, it is making performance issue. Below is the query.

I am from SAS and hence requesting experts here to show some light on this issue. I am connecting to Oracle through SAS and the query works just as it is like SQL query(you may please ignore Proc sql and disconnect SQL statements).

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;

PROCEDURE 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.

mathguyJonathan LewisStew AshtonRama_GMustafa_KALAYCI
«13

Answers

  • Stew Ashton
    Stew Ashton Member Posts: 2,864 Bronze Crown
    edited Aug 16, 2019 10:18AM

    Your query cannot execute as written: there are extra commas in three places:

    Target.col3= Source.col3, -- extra commaWHEN NOT MATCHED THEN INSERT...    Target.R_KEY, -- extra comma ) values...    Source.R_KEY, -- extra comma )

    Also, you are updating every matching row, even if the data is already good. Consider:

    create table source(a_key, p_key, r_key, col1, col2, col3) asselect 1,2,3,4,5,6 from dual;create table target as select * from source;

    The two table have exactly the same data. Now run your MERGE:

    MERGE INTO TargetUSING SourceON (  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.col3WHEN NOT MATCHED THEN INSERT(  Target.A_KEY,  Target.P_KEY,  Target.R_KEY)values (Source.A_KEY,Source.P_KEY,Source.R_KEY);1 row merged.

    To avoid unnecessary updates, use the WHERE subclause of the UPDATE clause (see lines 12 through 16):

    MERGE INTO TargetUSING SourceON (  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  where 1 in (    decode(Target.col1,Source.col1,0,1),     decode(Target.col2,Source.col2,0,1),     decode(Target.col3,Source.col3,0,1)  )WHEN NOT MATCHED THEN INSERT(  Target.A_KEY,  Target.P_KEY,  Target.R_KEY)values (Source.A_KEY,Source.P_KEY,Source.R_KEY);

    Finally, you are inserting the KEY columns but not the COL* columns, so next time the target row will be updated. That doesn't make sense to me. Why not do all six columns in the INSERT?

    Best regards,

    Stew Ashton

    Jonathan LewisRama_GRama_G
  • jaramill
    jaramill Member Posts: 4,299 Gold Trophy
    edited Aug 16, 2019 11:11AM

    Per the forum guidelines -->

    on #5, it helps to post this information which could determine the type of answer you get:

    5) Database Version and IDE Version
    Ensure you provide your database version number e.g. 11.2.0.3 so that we know what features we can use when answering.If you're not sure what it is you can do the following:select * from v$version;

    in an SQL*Plus session and paste the results.

  • Rama_G
    Rama_G Member Posts: 14
    edited Aug 19, 2019 1:39AM

    Hi,

    A quick question.

    Do I need to mention the columns from ON clause in the where statements, to increase the performance ?

    ON ( 

    Source.A_KEY = Target.A_key AND 

      Source.P_KEY = Target.P_KEY AND 

      Source.R_KEY = Target.R_KEY 

    )

    into the below mentioned listed columns ?

    where 1 in ( 

        decode(Target.col1,Source.col1,0,1),  

        decode(Target.col2,Source.col2,0,1),  

        decode(Target.col3,Source.col3,0,1) 

      ) 

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited Aug 19, 2019 2:09AM
    4066099 wrote:Hi,A quick question. Do I need to mention the columns from ON clause in the where statements, to increase the performance ?ON (  Source.A_KEY = Target.A_key AND  Source.P_KEY = Target.P_KEY AND  Source.R_KEY = Target.R_KEY )into the below mentioned listed columns ? where 1 in (  decode(Target.col1,Source.col1,0,1),  decode(Target.col2,Source.col2,0,1),  decode(Target.col3,Source.col3,0,1)  ) 

    No. Oracle will already be comparing them.

    Please share the execution plan being used. How many rows in source? Is there an appropriate index on your target table to be used to do the join?

    Stew AshtonRama_GRama_G
  • Rama_G
    Rama_G Member Posts: 14
    edited Aug 19, 2019 2:28AM

    Hi Andrew,

    The reason behind my question is, I could not see much performance improvement with this where statement.

    Is there an appropriate index on your target table to be used to do the join?- Yes, I did check, it has Indexes on the columns being used in the ON clause.

    Thanks

  • Rama_G
    Rama_G Member Posts: 14
    edited Aug 19, 2019 2:36AM

    Hi Jonathan,

    When I am using the suggested select statement next to the MERGE AND INTO, it is showing some error by saying that ON clause is missing or similar to the below mentioned one.

    ERROR: ORACLE execute error: ORA-02012: missing USING keyword.

    Regards

    Rama

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited Aug 19, 2019 2:44AM
    4066099 wrote:Hi Andrew, The reason behind my question is, I could not see much performance improvement with this where statement. Is there an appropriate index on your target table to be used to do the join?- Yes, I did check, it has Indexes on the columns being used in the ON clause.Thanks

    I would expect most of the work is done in joining the two tables rather than actually doing the update so I’m not surprised that the filter didn’t improve things significantly (although is still a sensible addition).

    Rather than just checking, please make sure you show us too, misinterpretations happen all the time and you don’t want to waste time when the problem is usually simple.

    Again, how many rows in source? please share the execution plan so we can see how Oracle has decided to execute the merge (there are several ways and we cannot just guess). The execution plan is probably the single thing that will tell us almost everything we need to know - (most of) what it did and (most of) why it did it.

    Rama_GRama_G
  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited Aug 19, 2019 2:46AM
    4066099 wrote:Hi Jonathan, When I am using the suggested select statement next to the MERGE AND INTO, it is showing some error by saying that ON clause is missing or similar to the below mentioned one.ERROR: ORACLE execute error: ORA-02012: missing USING keyword.RegardsRama 

    Then you’ve written it with a syntax error. If you want help resolving that then you’ll need to share the code you‘ve now written.

    Jonathan LewisRama_GRama_G
  • Rama_G
    Rama_G Member Posts: 14
    edited Aug 19, 2019 3:09AM

    Hi Andrew,

    How many rows in source ? -- I am using my test data with about 1000 records. In the actual data it has 30k records.

    May I know what exactly are you referring to "ececution plan".

    Thanks

    Rama