Options
Speed to Cancel Data Compare
DBNewbie_2007
Posts: 47 Bronze 3
I was doing a compare against two tables, one with 10M records and one with 9M records (the 9M should be all new records compared to the 10M record table). I realized I might be able to speed up the compare by putting in a WHERE clause, so I selected CANCEL on the compare.
The "SQL Data Compare - cancelling" screen has been running for over 15-20 minutes now. Is there a faster way to cancel the compare other than issuing a "Task Manager" "End Process" directive? What are the consquences of doing an "End Process", since it appears it takes WAY to long to end the compare?
SQL Data Compare 5.3.0.68
The "SQL Data Compare - cancelling" screen has been running for over 15-20 minutes now. Is there a faster way to cancel the compare other than issuing a "Task Manager" "End Process" directive? What are the consquences of doing an "End Process", since it appears it takes WAY to long to end the compare?
SQL Data Compare 5.3.0.68
Comments
Cancelling taking too long is on our 'TO DO' list. Not really sure why it still takes time at the moment, is each row of your data very large? Do you have a large Blobs or something in the table? We should check for cancel after comparing each row. Mind you we also have to tidy up all the temp files which if the comparison has been running a long time could take longer than you'd imagine.
The WHERE clause has the potential to speed up your comparison massively if used correctly as you can partition the table into areas which you know are the same and so avoid comparing them. Another way to speed up the comparison if you're comparing lots of tables which may or may not be the same is to turn on the 'checksum' comparison in the options - you have to turn off store identical rows for this to work and if you have Blobs in your table I don't think it works but still it's an option for faster comparison.
We'll be releasing a RC of version 6 very soon although I'm not sure I'll have the time to investigate the slow cancel before release. It may be worth trying anyway just-in-case.
HTH
Project Manager
Red Gate Software Ltd
The table(s) I deal with are typically about 40-60 columns wide. I am transfering anywhere from 200,000 records to 5 million records, depending on what it is I am trying to do (something as large as 5 million being rare).
Nothing unusual about the data... mostly varchars and a few datetime fields. I will typically put some sort of restriction on the data using a WHERE clause (mostly date, ''+getdate()-150+'' or something similar).
I just ran into a situation today where I needed to do a comparison/sync, started the compare then realized almost immediately I forgot the WHERE clause. I selected cancel... same problem.... over 20 minutes now, no cancel.
The servers are not located on the same network/location (going from a remote server to a local server), but network performance has not been an issue (typically).
Hopefully we'll be able to address this problem for the next release.
Project Manager
Red Gate Software Ltd
Hopefully this won't come back to bite me - but for the moment it's in there for the version 6 release.
Enjoy!
Project Manager
Red Gate Software Ltd