July 19, 2011 at 11:52 am
Hi all,
I've been reading up on the MERGE statement in 2008 and I'm interested to know if you think it is a good idea to upgrade from 2005.
I have a large table that is modified once a day with UPDATE, INSERT and DELETE and then is read-only for the rest of the day. Currently I write the 'new' records to a separate table using SqlBulkCopy and then run the three separate operations by comparing these with the originals.
From what I understand:
The MERGE statement will update all records if a match is found, regardless of whether the record has changed. Will this provide poor performance compared with my current method of updating only changed records using EXCEPT SELECT?
I currently update the table in batches rather than doing an update of the whole table. According to http://technet.microsoft.com/en-us/library/cc879317.aspx I could filter the records in the target table by "defining a view on the target that returns the filtered rows and referencing the view as the target table". Presumably they mean creating a temporary view for this purpose? Has anyone tried this? Do you think it's a good idea? What might the performance be like?
In short, is it possible to use MERGE to do what I'm doing now and in general is MERGE quicker than the three-step process?
Thanks in advance,
Paul
July 19, 2011 at 12:01 pm
Yes, Merge will do what you need. Yes, it has, in my tests, performed at least as well as separate statements, if not better.
There should be no need to use a view for this kind of thing. It's a silly suggestion, since a view is just a stored select statement, and just gets incorporated into the query that accesses it. Just build the right Merge statement and you'll be doing the same thing, just with less work and with all the code in one place (easier to maintain/refactor).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 19, 2011 at 2:04 pm
GSquared (7/19/2011)
Yes, Merge will do what you need. Yes, it has, in my tests, performed at least as well as separate statements, if not better.There should be no need to use a view for this kind of thing. It's a silly suggestion, since a view is just a stored select statement, and just gets incorporated into the query that accesses it. Just build the right Merge statement and you'll be doing the same thing, just with less work and with all the code in one place (easier to maintain/refactor).
Thanks for that.
One other point I forgot to mention. The target table has a non-clustered primary key (string) and a clustered identity column. The source table has the primary key but not the identity column. Will this impact on a merge?
Paul
July 19, 2011 at 2:06 pm
Might have an impact on the execution plan, but not on the syntax of the command. Would have the same impact on separate commands.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 19, 2011 at 2:24 pm
I just ran upgrade advisor and it seems the only potential issue is with full-text search. I'm going to download a copy and give it a try.
Thanks,
Paul
July 19, 2011 at 5:54 pm
Hi
Personally I found MERGE to be less efficient than writing the relevant single queries.
In all honestly your data is going to be cached when search for existing rows etc.
If you have tables that's not wide is also limits the delete functionality such as a dimension table. Not that I'd be deleting from those 🙂
But I wouldn't upgrade for just that statement.
You can also fine tune single queries much better than you can a MERGE.
Cheers
Jannie
July 19, 2011 at 7:48 pm
To answer your original question: Should you upgrade to 2008 for the MERGE statement?
I'd have to say if that is the only reason you have for upgrading then probably not. If you don't have software assurance, that's a lot to spend to fix something that isn't broken.
I've used my home grown merge process in a number of places, and since compared it to the merge statement. I didn't find a lot of difference in performance, with a lot of dependence on table size, number of updates/inserts/deletes.
However there are a lot of other reasons to upgrade to 2008 that are worth considering.
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
July 20, 2011 at 1:19 am
Thanks for your comments.
The MERGE option is appealing because of its simplicity and the fact that it completes the whole process in one pass. It also enables bulk load from a text file, which I'll experiment with.
I'm going to try a side-by-side comparison. If the MERGE option at least matches my current performance, I'll probably upgrade - I've been looking for a reason to upgrade for the other benefits anyway.
Paul
July 21, 2011 at 6:55 am
I have to agree that Merge, by itself, isn't a good reason to spend money on an upgrade.
It's one of many good reasons to upgrade, but it's low on the list.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply