paul.barbin (1/16/2013)
Curious to hear from others on whether they use the MERGE command in production and what it's performance is like.We began using it in our unit test scripts to build data for developers immediately following the database build (we're using SSDT 2012). We wanted to use MERGE so the scripts could run even if developers already had data in their database.
So, rather than having an insert script of 10,000 records, we use the Merge command along with the 10,000 rows using the VALUES statement. This is EXTREMELY slow!
It takes 30 minutes to populate a small database (5GB) vs 2-3 minutes using TRUNCATE/INSERT.
Paul
Have you checked out MS's article (link) for MERGE optimization?
The biggest takeaway:
To improve the performance of the MERGE statement, we recommend the following index guidelines:
Create an index on the join columns in the source table that is unique and covering.
Create a unique clustered index on the join columns in the target table.
I've noticed slow performance in MERGE, but never when following these guidlines - which sometimes forces me into staging tables, but the MERGE benefits have outweighed the downsides there.