Post reply

MERGE statement performance issue

  • March 29, 2012 at 10:22 am

    #270936

    Hi all,

    I have 3 sales tables and each is of size around 60million rows. I created a proc which get all information from these tables with different logic and UNION ALL into CTE.

    Finally I am using MERGE to UPSERT into a single table(Sales). But this proc taking so long time to finish. Indexes are set properly on every table including the join tables.

    I suspect MERGE is taking long time. Do you have any suggestion to improve the performance of the above?

    For a full load, the proc will run for 1 hour. Incremental load(for 1 week data), it takes 15 minutes.

    Please advise.

    tahnsk

  • March 29, 2012 at 10:36 am

    #1466490

    My guess is that it is the UNION ALL. Break apart your statements to see which is taking the longest and let us know. Also, we cannot do much unless you post the actual DML and DDL in accordance with the article in my signature.

    Jared
    CE - Microsoft

  • March 29, 2012 at 11:04 am

    #1466507

    This too: How to Post Performance Problems[/url]

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply