Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
peddi_praveen
Starting Member
48 Posts |
 Posted - 2003-09-16 : 10:29:54
|
| Hi,I was wondering if I can make use of checksum in a way to improve my performance.Every end of day I get data uploaded to my database, which I should reconcile withthe existing data. To do this I have to compare around 30-40 columns of differentdatatypes & lengths.I think of storing checksums of both the tables overnight and make use of them thenext day to see if any of the values in theses tables change. Will this work fasterthan comparing the columns??If so, what are the datatypes which allow to store checksum (binary /varbinary)?are these binary/varbinary data types are comparable?Also what all precautions i need to take care of whIle storing/comparing?Also, will it help if I create a seperate database to hold the uploaded data and thenreconcile the data with the existing data.Any suggestions/feedback is of great help.Thanks & regards,Praveen Kumar |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-09-16 : 12:17:23
|
| Just remember this about checksum's:They will ALWAYS tell you if two rows are different.They MIGHT tell you if two rows are the same.In other words, if checksum(row a) <> checksum(row b), then you know for a fact the two rows are different. But if checksum(row a) = checksum(row b), then all you know is they PROBABLY are the same but it is not guaranteed.- Jeff |
 |
|
|
peddi_praveen
Starting Member
48 Posts |
Posted - 2003-09-16 : 23:42:41
|
| Hi Jeff, Yep,agreed. we want to give a try as our tables are too big.we are planning to do in phase wise, meaning,first we will be able to track those which are different records. this will reduce the process time, rite?. for those reccords with same checksum, we are planned to re-generate checksum and compare it from the application side.back to the point,If so, what are the datatypes which allow to store checksum (binary /varbinary)?are these binary/varbinary data types are comparable?I tried to create a computed column with formula , binary_checksum(column1, column2,....), SQL server automatically taking it as INT datatype....?Also what all precautions i need to take care of whIle storing/comparing?Also, will it help if I create a seperate database to hold the uploaded data and thenreconcile the data with the existing data. |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-09-17 : 01:46:54
|
| Praveen,What about adding a TIMESTAMP column (Use its synonym ROWVERSION) to the tables?DavidM"SQL-3 is an abomination.." |
|
|
|
peddi_praveen
Starting Member
48 Posts |
Posted - 2003-09-17 : 08:45:36
|
| hi david, no chance for adding the timestamp column , coz , my source data will be coming in text format and data consumer is different from data provider.elaborating the scenario, Party A, B,C providing the data in text format/xml format etc.... and we store this data in our db.Parety X will b subscribing for our data and consumes data into his database.So,every time, party X seeks data from our server, needs only changed data from the last sent copy.this is where , i need the comparison of all the columns before uploading the data from data providers.So,when i upload the data from Party A, B, C i need to compare the data to my data in db and should be flagged it as "updated".so , then i can push the "updated" data only to the data consumer.so, iam thinking of ->storing the column in my db to store checksum->generate the checksum for input data.->compare the checksum for PKs on both sides and filter those are DIFFERENT.any help would be appreciated. |
|
|
|
|
|
|
|
|
Checksum comparison