Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Merge error handling Expand / Collapse
Author
Message
Posted Monday, November 1, 2010 9:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 21, 2014 4:03 AM
Points: 8, Visits: 131
Comments posted to this topic are about the item Merge error handling
Post #1014268
Posted Tuesday, November 2, 2010 2:49 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 2:22 PM
Points: 411, Visits: 1,399
Good article, thank you!

One addition though: if your procedure is called from within a trigger or from a service broker handler, most likely the catch block won't be called upon many errors. Instead execution is resumed from the 1st line after the call to your procedure upon these errors. This is because in these contexts the xact_abort setting defaults to "on". To make sure your catch block does get executed for most (but still not all) errors, include a "set xact_abort off;" line as one of the first statements inside your procedure, before you open your try block. Adding this line won't harm your procedure in other contexts, but it makes sure your catch block will get executed even when called from these contexts.




Posting Data Etiquette - Jeff Moden
Posting Performance Based Questions - Gail Shaw
Hidden RBAR - Jeff Moden
Cross Tabs and Pivots - Jeff Moden
Catch-all queries - Gail Shaw


If you don't have time to do it right, when will you have time to do it over?
Post #1014327
Posted Tuesday, November 2, 2010 7:44 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, March 16, 2014 10:26 AM
Points: 536, Visits: 516
What are your views on using this kind of logic in an ETL flow for slowly changing dimensions - overkill?. What about the performance implications when it goes into the slow path?

For OLTP applications, this looks like an AWESOME post. Can someone comment on ETL usage?
Post #1014479
Posted Tuesday, November 2, 2010 7:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 2:12 PM
Points: 168, Visits: 117
I have been troubled by identifying the error rows in the MERGE process so thanks for sharing this.
What if there are millions of rows to merge ... will this affect the performance?
Post #1014487
Posted Tuesday, November 2, 2010 8:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 21, 2014 4:03 AM
Points: 8, Visits: 131
Performance on the error free "fast path" is great and I'm easily pushing through merges of 10k rows. On the slow path when there are errors, performace is going to be poor because of the row by row iteration. There are some performance improvements to be had, like putting an index on the UDDT but it's always going to be expensive.
Post #1014523
Posted Tuesday, November 2, 2010 8:14 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 3:46 PM
Points: 267, Visits: 303
For large updates, index the update_id column to improve performance:

CREATE TYPE CustomerUpdatesType AS TABLE (
update_id int identity(1,1) not NULL PRIMARY KEY,
customer_id int null,
title nvarchar(16) null,
first_name nvarchar(32) null,
last_name nvarchar(32) null,
phone_number nvarchar(10) null
);
Post #1014524
Posted Tuesday, November 2, 2010 8:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 21, 2014 4:03 AM
Points: 8, Visits: 131
R.P.Rozema (11/2/2010)
Good article, thank you!

One addition though: if your procedure is called from within a trigger or from a service broker handler, most likely the catch block won't be called upon many errors. Instead execution is resumed from the 1st line after the call to your procedure upon these errors. This is because in these contexts the xact_abort setting defaults to "on". To make sure your catch block does get executed for most (but still not all) errors, include a "set xact_abort off;" line as one of the first statements inside your procedure, before you open your try block. Adding this line won't harm your procedure in other contexts, but it makes sure your catch block will get executed even when called from these contexts.


I hadn't thought about that so thank you.
Post #1014527
Posted Tuesday, November 2, 2010 8:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 21, 2014 4:03 AM
Points: 8, Visits: 131
vishal.gamji (11/2/2010)
What are your views on using this kind of logic in an ETL flow for slowly changing dimensions - overkill?. What about the performance implications when it goes into the slow path?

For OLTP applications, this looks like an AWESOME post. Can someone comment on ETL usage?


I'd imagine this would be a good fit for a lot of ETL situations, although I confess to using it in OLTP.
Post #1014531
Posted Tuesday, November 2, 2010 2:54 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:10 PM
Points: 21,211, Visits: 14,904
Thanks for sharing this article.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1014922
Posted Tuesday, November 2, 2010 3:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 28, 2012 12:25 AM
Points: 5, Visits: 26
Thank you, very helpful.

Didn't think it through but might be better if we just process that one faulty row and then send all rows with id> id of error row.
Post #1014932
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse