January 7, 2014 at 11:45 am
I'm trying to reduce the overhead in some table triggers. I was hoping to replace audit-trail functionality w/ CDC but doesn't look like it will work unless you're using sprocs for all table access and we're using EF. The way they are currently working is as follows:
1. Populates #tempAfter from Inserted and #tempBefore from Deleted.
2. Declares (2) local variables for each field to hold @before and @after values.
- ex. - @Field1_before and @Field_after.
3. Inside a while loop (Egads!)
- selects top 1 from #tempAfter RBAR nonsense
- populate local variables (w/ type conversions and isnull()s).
- includes joins and scalar functions to get names to replace id's.
- Compares before & after variables for each field and calls logging sproc if not equal
So the good news is ... plenty of opportunity to improve performance.
What I'm hoping the smart folks here can help me with is the most efficient way to compare the Inserted and Deleted tables to determine which fields changed IN A SINGLE PIVOTED RESULTSET, so that I can insert the audit trail records in a single insert statement.
Thanks in advance.
January 7, 2014 at 12:22 pm
j_maloney (1/7/2014)
I'm trying to reduce the overhead in some table triggers. I was hoping to replace audit-trail functionality w/ CDC but doesn't look like it will work unless you're using sprocs for all table access and we're using EF. The way they are currently working is as follows:1. Populates #tempAfter from Inserted and #tempBefore from Deleted.
2. Declares (2) local variables for each field to hold @before and @after values.
- ex. - @Field1_before and @Field_after.
3. Inside a while loop (Egads!)
- selects top 1 from #tempAfter RBAR nonsense
- populate local variables (w/ type conversions and isnull()s).
- includes joins and scalar functions to get names to replace id's.
- Compares before & after variables for each field and calls logging sproc if not equal
So the good news is ... plenty of opportunity to improve performance.
What I'm hoping the smart folks here can help me with is the most efficient way to compare the Inserted and Deleted tables to determine which fields changed IN A SINGLE PIVOTED RESULTSET, so that I can insert the audit trail records in a single insert statement.
Thanks in advance.
You are right that there is plenty of opportunity for improvement. :w00t:
It is pretty difficult to offer much in the way of help because we have no idea what you are really trying to do here. If you can provide some ddl for the tables involved and some sample data along with an explanation of what you want to do we can help you find a solution.
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 8, 2014 at 3:54 pm
I'm not sure what you mean about procs and CDC.
CDC uses the SQL log to get changes, so it shouldn't require use of procs, or even know how any change that it captures was originally made.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply