Slow Data Comparison
ashaw
Posts: 12
OK, I know I've seen this before in a post about how slow the data conversion can be, but I really need some help and I'm hoping my circumstance is slightly different than someone elses.
I am doing a compare of a table with 1million+ records. Of those million+ records, 700,000+ have changed since the last compare (about 2 months ago). I set up a DataCompare project which compared the tables told me about all the differences and I set off in an attempt to "equalize" the tables. I began by trying this with the default Data Compare options, and let it rock and roll (at about noon yesterday) I left last night at 5pm it was 10% complete. When I came back in this morning and checked it, it was only at 28%. SQL had stopped responding (I believe). So I canceled the compare, redgate tools not responding, killed process for redgate. Decided maybe that it was the transaction that was causing issues with memory and decided to attempt the compare this morning without a transaction, I saved the syncronization script prior to running it this time (1.04GB). But still to no avail, 2 hrs later it hadn't even progressed to 1%.
Any ideas? Both databases are SQL 2005 and the version of DataCompare is 5.3.0.68.
Thanks !
I am doing a compare of a table with 1million+ records. Of those million+ records, 700,000+ have changed since the last compare (about 2 months ago). I set up a DataCompare project which compared the tables told me about all the differences and I set off in an attempt to "equalize" the tables. I began by trying this with the default Data Compare options, and let it rock and roll (at about noon yesterday) I left last night at 5pm it was 10% complete. When I came back in this morning and checked it, it was only at 28%. SQL had stopped responding (I believe). So I canceled the compare, redgate tools not responding, killed process for redgate. Decided maybe that it was the transaction that was causing issues with memory and decided to attempt the compare this morning without a transaction, I saved the syncronization script prior to running it this time (1.04GB). But still to no avail, 2 hrs later it hadn't even progressed to 1%.
Any ideas? Both databases are SQL 2005 and the version of DataCompare is 5.3.0.68.
Thanks !
Comments
Thank you for your post into the SQL Data Compare.
Please take a look at the following forum post, titled Support of large data set comparisons:
http://www.red-gate.com/MessageBoard/vi ... php?t=3389
This article explains the use of the WHERE clause filter to reduce the amount of data compared and other useful information.
Many Thanks
Eddie
Eddie Davis
Red Gate Software Ltd
Technical Support Engineer
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com
I have referred to that topic and read it multiple times, however this does not suit my needs as I need to syncronize all of the data, the compare works just fine, it's the amount of data that needs to be syncronized following the compare. This is going to be set up as a batch file command prompt compare at some point that will be done unattended at night. I cannot afford to either write multiple compares based on some arbitrary value that I may not know to split the data up. There are many times that there may only be a couple thousand changed records each time, but there are also the occasions that we will have the hundreds of thousands or very soon millions of records change between syncronizations. There has got to be a better solution.
Anthony
Unfortunately I do not have another solution for you. Discussing your problem with my Technical Support colleagues, I can only recommend that you investigate the replication technologies provided by Microsoft for SQL Server.
Many Thanks
Eddie
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com