BINARY_CHECKSUM and Database Concurrency


Recently I posted about Timestamps and CommandBuilders and I got a well informed reply by Luciano Evaristo Guerche concerning a related approach of using BINARY_CHECKSUM in SQL Server to do the same thing.  I think Luciano's response means to say that if you can't use Timestamp's in the database (like you don't have control over the schema) then BINARY_CHECKSUM is an improvement over the brute force concurrency that CommandBuilders do by default.

I thought Luciano was right, but I wanted to prove it out.  I ran some tests using a Typed DataSet and the Northwind Customer table:

  • I changed all 91 records of the customer table by changing a single field (Address).
  • I updated the table with the brute force method, the BINARY_CHECKSUM method and the timestamp method.
  • I ran it in batches of iterations to get some scale.  I changed the entire table in each batch. I ran it in batches of 5, 25, 100, 250, 500 and 1,000 iterations.

In general the results were what I thought.  The brute force performed worst, the BINARY_CHECKSUM was a performance boost, but not to a great extent; and the timestamp showed considerable improvement.  Here is a chart of my results.  The number of rows updated corresponds ot the batches (e.g. 5 iterations x 91 rows = 455, etc.) and I have calculated the row/second response time from each result:

Let me know if you have any questions...

Shawn Wildermuth
Author, Teacher, and Coach

My Courses

Wilder Minds Training
Vue.js by Example (New Lower Price)
Bootstrap 4 by Example (New Lower Price)
Intro to Font Awesome 5 (Free Course)
Building an API with ASP.NET Core (New Course)
Building a Web App with ASP.NET Core, MVC6, EF Core, Bootstrap and Angular (updated for 2.2)
Less: Getting Started (New)
Using Visual Studio Code for ASP.NET Core Projects
Implementing ASP.NET Web API

Application Name WilderBlog Environment Name Production
Application Ver v4.0.30319 Runtime Framework x86
App Path D:\home\site\wwwroot\ Runtime Version .NET Core 4.6.27514.02
Operating System Microsoft Windows 10.0.14393 Runtime Arch X86