none
<MERGE> statement used in big tables

    Question

  • SQL SERVER 2008

    Hi guys

    I am working on a database that represents a data storage for a datawarehouse.

    The data storage is populated by data coming form an OLTP database.

    In the data storage there are less tables than the OLTP databse, e.g.: OLTP tables T1, T2 and T3 merge data into a unique table T123.

    My question is:

    I would like to implement the new <MERGE> statement to Insert/Update/delete data from the three tables T1 T2 and T3 into the T123 (Data store).

    The table T1, T2, T3 contains milions of rows and the T123 as well.

    My concern is if the <MERGE> statement offer good performance when tables (source and destinations, especially in the case n --> 1) are big (milions of rows) and large (lots of columns)?

    Thanks  

    Friday, February 22, 2013 4:24 PM

Answers

  • Like many performance questions, the only good answer is "it depends".  I would start be creating the tables and making the best guess at the appropriate indexes.  Then try the data load with the technique that seems to you to be the most straight forward and maintainable.  If that gives acceptable performance, great.  If it doesn't, start looking for way to improve it (new indexes, removing indexes, using insert and update commands instead of merge, loading the data in batches instead of all at once, etc). 

    I've seen cases were merge worked better than separate update, insert and delete commands and cases where it was much slower.  If you go with merge, I would recommend you read http://technet.microsoft.com/en-us/library/cc879317%28v=sql.105%29.aspx.

    Tom

    Friday, February 22, 2013 4:45 PM

All replies