Results 1 to 4 of 4

Thread: Merge vs Update

  1. #1
    Join Date
    Sep 2010
    Posts
    2

    Unanswered: Merge vs Update

    Hi all,
    I m new to db2 and I am working with db2 9.1V. I experienced that Merge statement is faster when compared to update.
    For updating 15 million records using merge took 17.23 mins and Update statement took 30.11 mins.

    Can anyone explain me the reason, whats the strategy that makes Merge faster than Update, even though in Merge also we are using the Update statement only.
    Also can any one tell how mergestatement works in the backend?

    Thanks in Advance.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    How are you doing the UPDATE and MERGE statements? Can you post the statements you are using? It would also be useful to post any DDL including indexes on the affected tables. It is rather hard to answer your specific question with just a general knowledge of the problem.

    Andy

  3. #3
    Join Date
    Sep 2010
    Posts
    2

    Merge vs Update

    The following are the update and the merge statements. In both the tables the Index is created on the column ACCT_ID. The total number of records to be updated is 15,636,417

    ----- Merge statement

    MERGE TABLE_A TA
    USING (SELECT TB.ACCT_ID,
    MAX(CAST(TB.BDT AS DATE)) BDATE
    FROM TABLE_B TB
    WHERE TB.AGE BETWEEN 18 AND 100
    GROUP BY TB.ACCT_ID) TB1
    ON TA.ACCT_ID = TB1.ACCT_ID
    AND TA.DOB IS NULL
    WHEN MATCHED THEN
    UPDATE SET TA.DOB = TB1.BDATE;


    ----- Update statement

    UPDATE TABLE_A TA
    SET BD.DOB = (SELECT MAX(CAST(TB.BDT AS DATE)) BDATE
    FROM TABLE_B TB
    WHERE TB.AGE BETWEEN 18 AND 100
    AND TA.ACCT_ID = TB.ACCT_ID
    GROUP BY TA.ACCT_ID)
    WHERE TA.DOB IS NULL;

    --------------

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    My guess as to why the merge goes faster is because the subselect in the merge is only done once for the entire process and in the update statement it is done for every row. Look at the access plans for both statements and see if you can find the difference.

    Andy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •