November 21, 2009 at 9:35 am
Dave Ballantyne (11/20/2009)
TheSQLGuru (11/16/2009)
Peso-284236 (11/15/2009)
This may also be useful to knowhttp://weblogs.sqlteam.com/peterl/archive/2008/11/24/SQL-Server-2008-with-MERGE-and-triggers.aspx
I think the trigger issue has the potential to zap a lot of unsuspecting MERGE users.
Seems a bit short sighted that the order of the insert-update-delete trigger cant be guaranteed.
Imagine if the trigger did some work that broke some integrity rules on a INSERT-UPDATE-DELETE order but not a DELETE-UPDATE-INSERT order. Hmmm , some experimentation is required.
On any reasonably complex system it could be virtually impossible to have sufficient test plans that guarantee that you don't break something with a MERGE. I am advising my clients to avoid merge on any system that has triggers unless those triggers are extremely simple with no interdependencies whatsoever.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 28, 2015 at 9:25 pm
Hi I use lot of merge in my SPs. Never noticed performance issue. If you can provide substantial evi it will great
December 29, 2015 at 7:17 am
hemabalan (12/28/2015)
Hi I use lot of merge in my SPs. Never noticed performance issue. If you can provide substantial evi it will great
Binoogle search for the words merge bugs sql server will get you lots of useful hits.
It isn't so much about performance - it is about BAD DATA, including the very thing you would expect it to do correctly every time, which is the classic UPSERT scenario.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 30, 2015 at 10:26 pm
Thanks for the response.
Heres another one. Using CTE I undestand we dont have to worry about storing and retrieving from tempdb. It works as we have the USING Stmts. But I get this doubt is , if the table is very huge keeping in memory the entire table and doing manipulation , will it be same as retriving from tempdb after manipulation .
December 31, 2015 at 7:59 am
hemabalan (12/30/2015)
Thanks for the response.Heres another one. Using CTE I undestand we dont have to worry about storing and retrieving from tempdb. It works as we have the USING Stmts. But I get this doubt is , if the table is very huge keeping in memory the entire table and doing manipulation , will it be same as retriving from tempdb after manipulation .
1) Using a CTE allows developers to write queries WAY more complex than they would be able to do otherwise, often with disastrous performance consequences.
2) It also often leads to writing queries that "iterate" operations in a sequential manner, which is also poor query design many times. Sometimes the optimizer can put things right for them, but often it cannot.
3) If you reference a CTE more than once it is FULLY INSTANTIATED each time. Most don't know that.
4) ANY temp storage object (table vars, temp tables, or any engine-created one such as hash, sort, etc) will go into memory while memory is available and get put down to tempdb when it isn't. Also note that "big" queries don't necessarily need to keep all the data in storage at once - they could stream data through for processing at query time with no need for the expensive tempdb storage.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 31, 2015 at 8:14 am
If you're using a MERGE statement to perform something like a Type 2 Slowly Chaning Dimension operation, then it helps if you can apply a range condition like: WHERE SaleDate >= '2015/12'.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 4, 2016 at 9:37 pm
Thanks for the detailed response!!
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply