Update via C# EF Core or SQL

Question

Our work involves updating products, we have a large table of products, and the prices and other pertinent information gets updated hourly. Let’s say it’s an amazon store, and we are talking of amazon products, and we have to update the selling price, buy box price etc. we pull the information hourly from amazon into our program and update the data in database.

My workflow is, pull all the products from the database into the program, (we use C# and EF Core), update the relevant products, and send back the updates to the database.

This way has the penalty of reading a lot of info from the database into the program, but I feel this is very efficient, because EF Core has change detection, so even if I assign all products with the price of the incoming products, if there is no change EF core will not change anything, it will only generate an update statement for those products which have changed information.

Also, it does not make large update statements, it generates small, targeted update statements, like update products, set BuyBoxPrice = 12.23 where productid = 23345.

I am working with a very gifted developer, who is very natural with SQL, he argues that this way is wrong, I should rather put all the incoming info in a temp table named #products, and send it into the database, and then run a stored procedure which should do like this,

update products, set BuyBoxPrice = #products.buyboxprice from products inner join #products on products.produtid = #products.productid.

So this approach avoids having to make large reads from the database.

I am not so experienced, my question is, do reads create locks or degrade the database performance, probably yes?

Now here are the reasons I am not comfortable with his approach.

It creates a lot of unnecessary updates, which is very wasteful in my eyes, since only 25 percent of the information changes, so why update all columns.

My colleague counters, that I can remedy that by adding a where statement, like

update products where products.buyboxprice <> #products.buyboxprice

I don’t think that this reduces the penalty u pay, I think its still the same effect.

Another major worry is that large updates create locks, for that alone it should be avoided. Now of course I can break up the updates into chunks of less than 3000 etc.

Third point, when SQL has tummy trouble, it throws symptoms all over, and strange things start to happen, customers call in yelling and boss gets angry, and I have very little visibility of what’s going on, but in C# whenever something crashed is right there clear for me.

So my question is, who is right, is it more performant to do the updates via reads and EF core, or via SQL

Answer 1

Doing relational things will ways be fastest in the context of the database. Even though Entity Framework does things efficiently, in an apples to apples comparison, it'll always have slightly more overhead.

That being said, the big performance difference comes in with how you implement a database focussed solution or a C# focussed solution. I won't comment on your colleague's recommended alternative as that seems a little bit outside of what I'd suggest. I think there's probably better solutions, situation depending, such as using EF Core's DBContext.Find() methods to apply WHERE predicates on the primary key values, but your post asks a lot of important questions that require a lot more thought to respond to, so I'll update my answer later when I have more time.

One quick one though is yes reads do cause locks against writes in a table, and pulling in the whole table into a consumer like a C# application to only update a few records is usually not performant. Rather you should use WHERE predicates on your data pull to only access the rows you need for the lifetime of the current context of the app. (Assuming your tables are indexed appropriately.)

---

To address the points you made / other questions you asked:

1. "rather put all the incoming info in a temp table named #products": While using a temp table to temporarily hold the primary keys of the records you want to update is one way of doing things in a potentially efficient way, it sounds a little roundabout. If you already have the primary keys (IDs) of the records you want to update, then you can just pass in those IDs to the stored procedure (via a couple different options for the parameter's data type) or even better yet just write the update statement itself with those IDs and execute it. (Just a couple of ideas among many other solutions). Again if you want a pure C# solution, you should be able to use EF Core's DBContext.Find() method to only pull in the rows that need to change, and then update them. (Probably the best middleground solution.)

2. "do reads create locks or degrade the database performance?": Yes they can affect performance of the server by causing resource contention if you are frequently reading a lot of data. Reading from a table also acquires locks on that table that can block write queries, and cause contention. See the afore-linked DBA.StackExchange answer for more in depth information on locking.

3. "It creates a lot of unnecessary updates, which is very wasteful in my eyes, since only 25 percent of the information changes, so why update all columns.": I don't fully understand your statement here, not all columns are being updated based on the SQL solution your colleague suggested. And assuming he means only send the IDs of the records that need to be updated in a temp table, then only the exact number of rows that need to be updated are being updated.

4. "My colleague counters, that I can remedy that by adding a where statement...": This indicates to me that your previous statement is under the assumption all the rows should always be passed into the temp table. As per my previous response, you should only pass in the rows that need to be updated.

5. "I don’t think that this reduces the penalty u pay, I think its still the same effect.": The addition of the WHERE statement your colleague suggested is unnecessary if your temp table only contains the IDs of the rows that need to be updated. (If your temp table did contain every row in the table, then it's hard to say if it would be more efficient without seeing the execution plan of the query. Possibly it would still be more efficient than the process in EF Core of pulling in all the records into the application and then updating them.) Also if you went a different route that didn't use a temp table, and involved filtering in SQL that was logically equivalent to what you wanted to accomplish (e.g. update products set buyboxprice = 'someValue' where someOtherField = 'someOtherValue') then that filtering would be more efficient (assuming you have proper indexing) than your current process all in EF Core.

6. "Another major worry is that large updates create locks": Apples to apples, if you have the same query plan in both cases, then the same amount of locking is going to occur due to your updates. (Though the amount of rows that need to be updated at one time can affect the query plan.) You can play around with chunking (batches) but generally won't really be necessary until you're updating about 100,000+ records at a time. But batching is a design choice irrespective of C# and SQL, you can accomplish this in either place, so it's a moot point.

7. "Third point, when SQL has tummy trouble, it throws symptoms all over...I have very little visibility of what’s going on, but in C# whenever something crashed is right there clear for me.": This is not an issue with SQL rather inexperience with the framework and tools available (I don't mean this negatively, just a matter of fact, as someone who was in the same shoes a few years ago). There was likely a time when you didn't know how to use the debugger in Visual Studio right?...and breakpoints...same for using try/catch? Up until you became proficient with the tools for C#, you probably had a harder time debugging crashes and bugs. The same is true for SQL, furthermore performance issues != crashing and bugs. They require a different kind of mindset and process for debugging. If you had a non-database related performance issue in your C# application (for example if your app was slow when saving a file to disk), you wouldn't have as much clarity debugging the issue as a crash using the tools you're familiar with debugging crashes. There are many good tools and scripts available for debugging performance issues in SQL Server, please see the list below:

   • Brent Ozar's sp_Blitz
   • Get Running Queries - I fine tuned this query (I forget where I originally found it) and find it immensely helpful for on the spot debugging performance issues as it tells you all running queries on the server, where they originated from, how long they've been running and wait stats and blocking info. (I have it saved in a stored procedure which can be scheduled to routinely run and log the results to a table.)
   • Query Store
   • Extended Events
   • SQL Profiler (Deprecated but worth noting.)
   • Activity Monitor
   • Microsoft's Docs on Performance Monitoring
   • Helpful Queries from PASS
   • SentryOne's SQL Sentry
   • RedGate's SQL Monitor

In summary, as a C# developer for 8 years, and a DBA for about 3 years, it has been my experience that managing and manipulating data is most efficient when structured well with good processes that maximize the relational capabilities of the database (as opposed to trying to accomplish the same task in a functional language).

Answer 2

is it more performant to do the updates via reads and EF core, or via SQL

You should answer this question for yourself with testing.

Is it more work to do it one way or the other? Is there a meaningful performance difference?