Speed to Cancel Data Compare

DBNewbie_2007

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

richardjm

If you kill the process for a comparison you'll need to clean up the temporary files. If you set your RGTEMP environment variable then they'll be in that directory and you can delete them all.

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

DBNewbie_2007

Sorry for the delay in getting back a response... too many projects, never enough time!

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).

richardjm

We do have a bug raised in the system for time to cancel being too long. Sadly we've not had the time to investigate it before version 6 as all the code reads like it should cancel pretty quickly.

Hopefully we'll be able to address this problem for the next release.

richardjm

Tell a lie, I've just looked into this with fresh eyes from the last time I checked it and I've sneaked a fix in.

Hopefully this won't come back to bite me - but for the moment it's in there for the version 6 release.

Enjoy!