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

Checksum function??? Expand / Collapse
Author
Message
Posted Tuesday, September 1, 2009 10:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 16, 2010 10:55 AM
Points: 33, Visits: 113
I did this recently to determine if a row needed to be updated. I put a checksum column in the table using the values that could possibly be updated. The stored procedure had a checksum variable based on the input variables. The input variables for the proc are also the DB columns that could possibly be updated.

By comparing the DB checksum calculation with the stored procedure checksum variable I knew whether the row needed updating or not.
Post #780876
Posted Tuesday, September 1, 2009 11:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 13,872, Visits: 9,600
Mark Pratt (9/1/2009)
I did this recently to determine if a row needed to be updated. I put a checksum column in the table using the values that could possibly be updated. The stored procedure had a checksum variable based on the input variables. The input variables for the proc are also the DB columns that could possibly be updated.

By comparing the DB checksum calculation with the stored procedure checksum variable I knew whether the row needed updating or not.


Makes sense.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #780908
Posted Wednesday, September 2, 2009 11:38 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, September 19, 2014 1:52 PM
Points: 122, Visits: 274
Mark Pratt (9/1/2009)
I did this recently to determine if a row needed to be updated. I put a checksum column in the table using the values that could possibly be updated. The stored procedure had a checksum variable based on the input variables. The input variables for the proc are also the DB columns that could possibly be updated.

By comparing the DB checksum calculation with the stored procedure checksum variable I knew whether the row needed updating or not.


Hi,

Yep, this is exactly the point I was trying to make. Note that is very easily doable using a script component in a data flow and from what we've tried to far, goes pretty fast.

Regards,

Greg
Post #781666
Posted Friday, September 4, 2009 8:37 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 29, 2012 8:08 AM
Points: 29, Visits: 155
If you are using sql 2005, and the columns you are interested in are varchar,nvarchar, or varbinary, you can try making a computed column out of the /b HashBytes /b function. There are a few limits, one being the total input size cannot be greater than 8k ( I think this was fixed in either sp3 or 2008). I also believe it is deterministic so you can put an index on it. Use the SHA1 algorithm as the chance of collision is 1 in 2^80.

Josef Richberg
2009 Exceptional DBA
http://www.josef-richberg.squarespace.com
http://twitter.com/sqlrunner
Post #782973
Posted Tuesday, September 8, 2009 9:53 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, December 8, 2014 1:04 PM
Points: 3,840, Visits: 3,854
Is there any sort of date column in the source table that could show you which rows have been updated? If you could do something to avoid comparing the million rows, that may help.

Also, have you tried the SCD transformation? Between that and proper indexing on your dimension, you should still get decent performance out of the compare.

How much free reign do you have on the source system? Would it be possible to make changes there to aid in making this easier?




John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #784362
Posted Thursday, September 10, 2009 7:29 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
I have found the checksum to be useful in some cases and unreliable in some. If the data set if distinct then the checksum function (of all rows stands good), but in another case i had to get 27 Million rows from DB2 with no primary key, no uniqueness on the table, so i thought of using checksum to compare the rows in a staging environment. Not only did i have the overhead of creating the checksum, i also had to compare it + the checksum gives me a integer value based on the row uniqueness, where i had duplicates and everything was getting messed up..... so in short i would think the usage totally depends on the case.
Post #785674
Posted Thursday, September 10, 2009 10:26 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, December 8, 2014 1:04 PM
Points: 3,840, Visits: 3,854
The OP has not responded for quite some time now.

SQL Learner? Are you still working on this? We'd appreciate some feedback as to how you've resolved the problem. Having that in the thread will help others that come accross this thread down the road.




John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #785829
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse