1

We have a few 100's lines of code, that according to some complex logic decides if certain columns of certain entities should be updated from some legacy system.
Those columns don't necessarily get different values, if the requirements are met, they are assigned with the new values, regardless of the old ones.

Now we've started using temporal tables (which is a SQL Server feature), that once an update statement is executed on a certain record, saves the old record in a "history table".

The problem: since this code might update tens on thousands of records, and it run on a daily basis, we want to avoid having millions of records in the history table, that don't actually have anything different than the original records, but since we "updated" a record - they were saved as "history".

The code is written using C#, with EF, but we use .NoTracking() for performance.

Some of the alternatives we've considered:

  1. Go through all the relevant columns of the relevant entities, just before save, and check if any changed.
    This solution has a big flaw - the next person that will add some column to that code, will definitely not know he should update that "change detection" code.

  2. Create methods for updating columns in this piece of code, that update a column AND also ticks some flag that says we've changed something. The next person that will read that code will definitely run into any of those, and will have to understand why it's needed.. but this solution feels very awkward.

  3. Maybe use some trigger in the database that detects and avoids non-changing updates. Again - probably no one will remember to update it. Especially since we currently don't have a single trigger in our system.

  4. Avoid NoTracking and use EF's ChangeTracker.DetectChanges method. According to the documentation, this method has performance issues, and performance is an issue here.

So - so far we haven't come up with a decent, elegant solution to the problem.

[Migrated from SO]

6
  • 2
    Suspecting performance issues isn’t the same as having performance issues, test and verify.
    – jmoreno
    Dec 9, 2020 at 12:59
  • In EF, is there a way to loop through the parameters to an insert without explicitly referencing them?
    – JimmyJames
    Dec 9, 2020 at 16:21
  • Add a SQL RowVersion field to the tables in question, then use your option #1 to test if this field has changed. No need to worry about if the next dev doesn't know to add code to check new columns since all checks only use the RowVersion field. And your question begs the question, How are you handling concurrency?
    – quaabaam
    Dec 17, 2020 at 1:06
  • @quaabaam seems I'm missing something. RowVersion changes even if no changes were made to the actual table.. According to here for example: database.guide/what-is-rowversion-in-sql-server search for "Updates with No Changes".. As for concurrency.. those process we run are not expected to run simultaneously, or at a time users work on the system..
    – Oren A
    Dec 17, 2020 at 9:39
  • If you use your option 1 with RowVersion, then the RowVersion is the only field you need to 'go through...and check if [it] changed' before you call the update. Of course this means you need to add a RowVersion property to your model. Since your concern with option 1 was the possibility that a future dev wouldn't know to update columns to check before the update, using the RowVersion means no need to add new columns to the check logic as only RowVersion is the only column ever needed for the check. If RowVersion is different you do the update if it's not you don't.
    – quaabaam
    Dec 17, 2020 at 17:30

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.