Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2009
    Posts
    4

    Unanswered: Can this query be optimized?

    Hi Guys,
    I have a table containing requests, with about 500,000 rows, and another table called REQUESTS_MERGER which contains the previous 5 days requests, and I need to merge the request table with the merger table (insert new records, and update and changed details in existing requests). My current query uses DB2/SQL MERGE INTO statement:

    MERGE INTO REQUESTS A1 USING REQUESTS_MERGER A2

    ON (A1.REQUEST_NUMBER = A2.REQUEST_NUMBER)

    WHEN MATCHED THEN
    UPDATE
    SET
    [Columns in A1 with their values from A2]

    WHEN NOT MATCHED THEN
    INSERT ([A1 Columns])
    VALUES ([A2 Columns])

    (I removed the columns, to save space, but there is about 30 columns in the REQUESTS table)

    The query runs fine, it just takes ages! Is there a better process to merge the two tables... Temporary tables are possible, but I can't think of a way to do it.

    If you think indexes would help, please could you help how to create and manage indexes?

    Looking forward to your suggestions!-Cheers in advance!

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Indexes for REQUESTS.REQUEST_NUMBER and REQUESTS_MERGER.REQUEST_NUMBER may improve performance.

  3. #3
    Join Date
    Dec 2009
    Posts
    4
    Quote Originally Posted by tonkuma View Post
    Indexes for REQUESTS.REQUEST_NUMBER and REQUESTS_MERGER.REQUEST_NUMBER may improve performance.
    Thanks a lot tonkuma,

    For those who are interested, I created UNIQUE indexes on both the original and merger tables using the syntax:

    CREATE UNIQUE INDEX SR_MAIN
    ON REQUESTS (REQUEST_NUMBER);


    CREATE UNIQUE INDEX SR_MERGE
    ON REQUESTS_MERGER (REQUEST_NUMBER);

    The query now runs in 5 seconds as opposed to 8 hours! Thanks again for your help

Tags for this Thread

Posting Permissions

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