Checksum Compare times out after 30 seconds

Charles

I'm trying out Data Compare, and ran a successful compare on a table. However, when I try to use a checksum compare, after 30 seconds I get:

Timeout expired. The timeout period elapsed prior to the completion of the
operation or the server is not responding.
Conditional Fast Rowcount method requested without specifying an expected

I'm comparing a local 2005 database to a database running on a remote 2000 system. Tried authenticating as Windows Administrator, SQL administrator, and database owner.

Any ideas?

richardjm

Not sure why that would be the case. It seems that generating the checksum on your table is timing out for some reason.

You can set a timeout by setting the registry entry.

HKCU\Software\Red Gate\SQL Data Compare 5 (even in v6 - whoops!)
ChecksumTimout - int - number of seconds to timeout.

Charles

richardjm wrote:

HKCU\Software\Red Gate\SQL Data Compare 5 (even in v6 - whoops!)
ChecksumTimout - int - number of seconds to timeout.

In my registry, actually, I have a "SQL Data Compare 6" branch, and no "5" (I never installed 5). However, there's no checksum value in there.

Should I create a new ChecksumTimeout value directly under that key (i.e. as a sibling to "ProjectFolder"? Or are you saying that I need to actually create a "...5" key and put it under that?

richardjm

Create a 5 and put it under that. I'd forgot to change the location for the 6 release, I'll fix it when we do the point release (there's always something isn't there ).

Just create the value and set it. At least you can set a reasonable timeout for it.

Charles

Thanks, Richard. I increased the timeout to an hour, and it works fine on smaller tables.

Now...

I have a very large table (over a billion rows), of which I'm trying to compare a relatively small subset (2.3 million rows).

I have a non-clustered index built on two fields (sourcetype & sourceid). I added a WHERE clause to the compare to define a range on this index, and I'm only asking it to compare these two fields.

can do a count(*) on the table using this where clause, and it uses the index and does the deed in under 3 minutes. (If I include another field not in the index, it plans a table scan and forecasts a ridiculous cost)

The checksum method times out after an hour (because that's what I set the timeout to), showing no progress. I never noticed any progress on smaller tables, either, but they finished (e.g. just did a 17-million row table in 3 or 4 minutes)

Is it normal for no progress to be shown during the checksum stored procedure run? I've seen this, where with a "normal" compare progress is displayed normally. Of course, now, I killed the SQL process (because cancel didn't do the deed in a reasonable amt of time) and restarted a "normal" compare with the same parameters, and it's showing 100% done while it works... but anyway, it's running the SELECT that I expect to see, and cancels okay (I tried it again to see if it did the same thing) so maybe the progress display just doesn't like this table... actually, now that I think more about it, the progress display is probably based on an estimated rowcount for the table from the statistics, so a WHERE clause makes it impossible to use that.

What kind of performance improvement have you seen on larger tables using the checksum vs. non- method, anyway? (assuming relatively few differences; otherwise I'd guess it would actually be longer)


Any Thoughts? Is the reason for the timeout on the checksum method that the checksum procedure does nothing for a long time and then returns an answer list where the "classic" SELECT trickles data out as it goes? I would think that an hour would be enough for either method, but maybe I need to increase the timeout more and be patient... :-(

richardjm

The checksum is a shortcut which should return almost instantly, at least it has in any tests I've tried. What I think may be happening is that checksum is returning fast and revealing the tables to need comparing. At which point it will attempt to do a SELECT columns FROM table ORDER BY comparison key.

What you seem to be saying is that if you try to add another column to your SELECT statement within query analyzer SQL Server wants to do a table scan and forecasts some obscene time.

To create a chucksum on such a large table would take considerable time and it's probably SQL Server that's hanging on the generation (we use a standard checksum call in SQL Server). The checksum merely checks that we need to perform a comparison at all, if you're sure the tables aren't matching then I'd just turn off the option and see if that gets you further.

Cancel isn't responded to whilst attempting to generate the checksum as we never had any test where this was anything other than almost instantaneous, also the no progress being reported as it's an atomic operation of SQL Server - hence those problems at least.

Hope this makes sense, it's too cold and I've not had my coffee yet.

Charles

Oh, I see, so apparently the checksum is performed at the table level (or selected group of rows defined by the where clause) , not the row level as I had assumed (thus saving a LOT of data transmission across the network, rather than a little). I didn't understand that. I'll definitely leave it off, then.

I also figured out why my latest compares on that table were still timing out and not behaving i/o-wise the way I thought they should (performing a table scan instead of a bookmark lookup): When I was editing my where clause, I was forgetting to select the appropriate table, and apparently the 1st table on the list was selected automatically (though it was scrolled off the screen and invisible any time the list was pulled up) and I was changing the clause on a table I wasn't even checking.... :oops:

So, I think I'm finally figuring this out. I just want to double-check: Any time I add a where clause, I should not expect to see the progress bar move during the procedure, correct?

Thanks for all the info!

richardjm

Nope the WHERE clause is fine - the only time progress won't happen is during the check for the table checksum.