Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
 Posted - 2009-07-08 : 16:00:37
|
| Has anyone done any performance test to do UPSERTs using the MERGE statement? Or know of any good articles?Everything I know or think I know about how it is supposed to work leads me to believe that it should be faster than the traditional UPDATE and INSERT method. However, all the tests I run show that the traditional method is about 20-30% faster than doing a MERGE. Just for reference I'm working with tables in the 100's of millions of rows and attempting to upsert 100's of thousands to several million rows at a time.Just curious if it is me or if others are getting the same results.Cheers! |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-07-09 : 04:23:08
|
| It is much faster than a traditional UPDATE/INSERT. I used this method in my last contract and the more data in a table, the faster this method becomes. This is mainly due to the fact that it is a one pass operation.Really not sure how your getting conflicting results, the only thing I can think of is the way you have wriiten your query (not saying it is, but that's the most likely candidate if you are having issues). |
 |
|
|
Andrewra
Starting Member
17 Posts |
Posted - 2009-07-13 : 11:00:42
|
| I currently am using merge statements for refreshing tables that are dealing with slowly changing dimensions. I think the Merge has definatly performed better then traditional update insert statements. I would agree to Rick that it might be something with your query. Here is what I used as reference while doing my development. The tables I am working on are around the 25 million record size.http://www.kimballuniversity.com/html/08dt/KU107_UsingSQL_MERGESlowlyChangingDimension.pdfAndrew AlexanderLiveLogic llc |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-07-13 : 14:39:13
|
| I'm pretty confident that it is not the query itself, rather how I have my environment setup. I have a VERY streamlined process so I don't need to compare all the columns, which is where I think the MERGE might have its gains. I hit a very slim index so things are very fast. Additionally, I do all my loading with SSIS, so the MERGE statement was more of a curiosity to me than anything. But, I do appreciate the feedback. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-07-13 : 15:41:12
|
The MERGE statement is logged. Do you experience problems with log file?
N 56°04'39.26"E 12°55'05.63" |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-07-13 : 17:25:00
|
quote:
Originally posted by Peso
The MERGE statement is logged. Do you experience problems with log file?
Not that I am aware of. It's been many months since I've messed with it. But, I'll try to set aside some time this week to create a test bed and do a couple of loads and see what kind of results I get. |
|
|
|
|
|
|
Merge Performance