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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply