Menu
I recently upgraded to MSSQL 2008 R2 and was wondering whether or not the "new" Merge function was more efficient/faster than Truncating and Inserting for updating tables given each table has millions of rows. If anyone could provide some data of their performance, that would be great, but any explanation would be helpful!
596
MERGE is designed to apply both UPDATE and INSERTs into a target table from a source table. The statement can do both at once, or simply do INSERTs or only UPDATEs. One might even get the impression that INSERT and UPDATE are no longer needed.
Answer. Testing with a variety of source row sets against a target with about 6 mio. rows showed a slighty time advance using the merge command. Overall less internal steps are performed in the merge compared to delete/insert.
The UPDATE statement will most likely be more efficient than a MERGE if the all you are doing is updating rows. Given the complex nature of the MERGE command's match condition, it can result in more overhead to process the source and target rows.
The DELETE statement removes rows one at a time and inserts an entry in the transaction log for each removed row. On the other hand, the TRUNCATE TABLE statement deletes the data by deallocating the data pages used to store the table data and inserts only the page deallocations in the transaction logs.
Not sure I got your point of TRUNCATE and INSERT correctly. If not, then feel free to correct me.
MERGE is meant as a mechanism to do either an UPDATE to an existing row, or, in case an existing row is not found, an INSERT.
You suggest TRUNCATE and INSERT, which would remove the use of MERGE as everything would be an INSERT. I don't have any idea whether or not a MERGE in that case would be faster or not, because I never tried it, but I would assume that the part of the MERGE that determines whether to use an UPDATE or INSERT does impose some overhead.
184
I used MERGE because i needed to track data changes (was keeping a checksum of the rows), but since it seems that you just need that data "cloned" everytime I don't see any particular reason to use MERGE , after all MERGE does some kind of checks (there's a check on the primary keys and additional conditions are put on every WHEN);
by using TRUNCATE and then a bulk INSERT you shouldn't have all that conditions in play
by the way don't take this as 100% true, I don't have any performance test to bring as a proof; I suggest you to try both operations and see which one takes more time =)
36
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
