February 10, 2014 at 3:29 pm
Been reading through search and couldn't find article/discussion that met exactly what I was looking for. If missed something, please post url so I can continue further reading.
Traditionally, our shop has done our daily updates by comparing all columns with the syntax of
((column1 <> column 2 or (column1 is null and column2 is not null) or (column1 is not null and column2 is null))
I recently moved to using merge with a filtered CTE and improved performance on my procedures. However, I have to compare all columns. What I've typically done is create a uniqueidentifier on all the columns to compare. This works great but is very touchy, as any changes to tables require the entire population of current records to have the checkid column updated, and then compared. Additionally, I've heard mixed things on indexing a checkid, so wondering if I could go some other route.
I'm considered isnull(column1,'') = isnull(column2,''), computed checkid columns, using except operator, and more. I've also heard mention of using Union for quick fast table comparisons.
Any resources, or ideas, for running my daily comparisons to build a history table? I'm updating a few terrible performing procs and want to see if any better practices out there than what I've pieced together personally.
February 10, 2014 at 5:16 pm
Don't do the ISNULL mechanism. That's going to lead to straight table scans and will not help your performance at all.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 11, 2014 at 2:57 pm
Look at Change Tracking; it can identity changes vastly more efficiently than what you're doing now.
If you're on Enterprise Edition, also look at Change Data Capture, which gives you "point-in-time" capabilities as well.
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