BINARY_CHECKSUM and Database Concurrency


Url: http://wildermuth.com/viewrant.aspx?id=2053

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
Shawn Wildermuth
Author, Teacher, and Coach



My Courses

pluralsight
Building a Web App with ASP.NET Core, MVC6, EF Core, Bootstrap and Angular (updated for 2.0)
Using Visual Studio Code for ASP.NET Core Projects (new)
Implementing and Securing an API with ASP.NET Core
Building a Web App with ASP.NET Core, MVC6, EF Core and AngularJS
Building a Web App with ASP.NET5, MVC6, EF7, and AngularJS (Retired)
Best Practices in ASP.NET: Entities, Validation, and View Models
Webstorm Fundamentals
Front-End Web Development Quick Start
Lessons from Real World .NET Code Reviews
Node.js for .NET Developers

Application Name WilderBlog Environment Name Production
Application Ver 2.0.0.0 Runtime Framework .NETCoreApp,Version=v2.0
App Path D:\home\site\wwwroot\ Runtime Version .NET Core 4.6.26020.03
Operating System Microsoft Windows 10.0.14393 Runtime Arch X86