Optimize .NET Database Updates — Full Breakdown & Transcript

[00:02] bulk update in a SQL database? Is it using EF Core, maybe raw SQL with Dapper, common table expressions, or bulk copy in some way? Well, you don't have to guess anymore because I'm going to give you the answers in this video.

[00:17] So, a couple of weeks ago, I made a video about how to optimize updates with a SQL database. I showed you five approaches, and in this video I want to extend it with two more approaches. We'll see how they compare in terms of

[00:31] performance, and I also want to address a couple of issues that you might run into when using some of these approaches. So, what's the scenario? We're using a Postgres database. We set the record count to be 10,000 records,

[00:45] which means we're going to set up the database with 10,000 records. These are going to be just mock orders that we are processing behind the scenes. So, inside the table and the schema if they don't exist. We are seeding some rows that

[01:01] and then we're going to iterate through the examples. So, we've got five approaches, one through five. Now, let me just quickly walk you through them so that you understand what it is that we are working with. So, approach one is

[01:15] your naive approach with something like Dapper or even EF Core, where you essentially do one round trip to the database for each update that you want to perform. Now, why this is somewhat tricky to some folks is because we have

[01:27] unique values for each row that we want to update in the database. So, let's say we're processing orders. We need to match the specific order by the ID, set when it was processed, and each order is processed at a different point in time,

[01:41] unique across all of the updates. So, with this approach, we've got one round trip for each update. This would be equivalent to loading this into memory, making the update, and then calling save changes for each record we want to

[01:54] update. The second approach is an improvement on this because it uses EF Core's ability to batch SQL statements and send them to the database in bulk. By default, the batch size is 42, although you can configure this when

[02:08] setting up your database context. However, the drawback with this approach using EF Core is we first have to load all the records into memory to be able to update them. However, we still only call save changes once, so this

[02:20] represents a significant performance improvement. The next approach iterates on this idea, where we construct a custom SQL statement that uses a temporary table with the values clause and passes in all the updates as rows in

[02:35] this temporary table. We can then do a select from this table and use that to perform the updates inside of our database. Now, this effectively gives us one round trip to the database at the cost of a bit more complexity when

[02:49] setting up the query, and it also comes with a very big limit, which is the maximum number of parameters that your SQL database supports. In this case, we're using Postgres, which has a maximum parameter limit of around 65,000

[03:03] something. I'll place the exact number on the screen. However, this is still a limitation. Now, there are ways to go around this. You can create a batch of updates up to the number of parameters that are supported, and then split the

[03:16] updates into the respective batches until you've processed all of them. going to end up doing multiple round trips to the database, so less efficient. The next approach is the same thing, just using EF Core to perform the

[03:29] update using the raw SQL approach, and you'll see the performance implications of this in just a moment. And then, the last one, probably my favorite approach, is using the unnest function in SQL, which allows me to pass in arrays that

[03:43] unnest is going to split into tuples or rows that I can then use to perform my update. So, it's essentially a shorthand of the above two approaches with the improvement that we only have two parameters in this example, an array of

[03:56] IDs and an array of processed at values, and here we don't run into the limitation of the maximum number of parameters, so this is going to be more scalable. However, here we have the limitation of the maximum array length

[04:09] or size that we can pass in as a parameter, which I believe with Postgres is around 1 GB, which is a huge amount of data. So, let's run this example, and I'm going to just drop this down to 1,000 records so that this completes

[04:24] faster, and we're going to use this as our baseline when we introduce two more approaches into our demo. So, it might be a bit slow to start, that's because we're doing some work behind the scenes, but the updates themselves are going to

[04:36] right now, the first approach gives us 380 ms, and we've got EF Core, which is by far the worst at 800 ms. Batching the updates significantly improves performance at 20 ms. Using EF Core has

[04:51] some overhead, so it's slower even though we're doing one round trip to the database. Then we've got the unnest approach, which is Postgres native, similar functions, and this is the fastest approach at 14 ms. So, let's see

[05:06] if the next two approaches that I want to show you are going to be faster. So, the first one I want to show you is using common table expressions. So, I'm going to create a function. Let's make it static async. It's going to return a

[05:18] task, and I'm going to call this Dapper CTE, which is short for common table connection string, and we're going to need the list of order updates. So, inside of here, I need to do some boilerplate. I'll say await using var

[05:33] boilerplate. I'll say await using var connection, new Npgsql connection. We'll pass in the connection string. I need to make sure that the connection is open, and then we're going to open up a transaction. So, say await using var

[05:47] transaction, and we'll say connection.BeginTransactionAsync. If you're not familiar, common table expressions are named result sets in SQL that we create using the with clause, and they only exist during the execution

[06:01] still going to be using the values clause. So, I'm going to populate these values using parameters, and here's how I'm going to construct them by using a string join and then projecting my updates list into a parameter tuple, and

[06:15] we're going to begin with our common table expression. So, I'll say with updates, and I can now define what my update looks like. I'll say ID and then processed at, and then I'll say as, and I want to pass in the values here, which

[06:31] I'm going to provide through the value clauses that we have above. So, let me add those. And now that we've got our common table expression, we can use it to update our orders table, where we want to set the processed at column

[06:46] using the value from the named update set. So, we'll say updates. Processed at. We're also going to set the status to processed, and we're going to select the respective values from the updates result set, and we want to match this so

[07:02] that the orders ID is equal to the individual updates ID. So, not a huge number of changes from our previous example that was using the Dapper approach that you can see here, and I can reuse these parts here for

[07:15] populating the parameters dynamically. So, I'm going to drop that below, and common table expression. That was something I didn't cover in the previous video. However, this still suffers from running into the maximum number of

[07:30] parameters limit, which is why I want to show you one more approach that doesn't have this problem, and this approach is going to use temporary tables. So, I'm going to call this approach the temp table copy, and you'll see why in a

[07:45] moment. So, let me define signature, our connection string, and a list of order updates, and then we're going to reuse the existing part from the previous example. So, creating a new connection, opening the connection, and creating a

[07:59] database transaction. So, what's the idea here? I want to create a temporary table. This temporary table is going to be alive for the duration of our transaction, and I want to fill this table with the updates that we want to

[08:13] perform, so we won't run into a limitation with the number of parameters because we can do a join between our table and the temp table and perform the updates within the database. So, the first thing we need to do is to create

[08:26] our temporary table. We'll say create temp table, give it a name, and we just want to pass in the ID and the processed at value. Now, using on commit drop is remove the temporary table as soon as we commit the transaction. Now, the next

[08:41] thing we need to do is once we have the temporary table, we need to fill it with the updates we want to perform. And here, we can use an efficient approach using the copy command in Postgres, which is identical to bulk copy in SQL

[08:54] Server. And here's the syntax for this. You say connection, and then begin binary import and you invoke the copy command, specify the temporary table name, where we are going to copy the ID and processed at

[09:07] values from standard input. Here, we are using a binary format, which is very efficient, and how you do this with Npgsql is you take the binary importer start row, and now you can write the column values for the individual row.

[09:23] So, we first want to write the ID and then the processed at column, and once we complete everything, we say complete async, and this is going to finish our copy command. And then, once we've got the data in the database, we can just

[09:35] say await connection.ExecuteAsync, and we need to pass in our SQL command. So, we're going to say update orders. We want to set the processed at column to a want to set the processed at column to a value that's coming from our temp table.

[09:50] We're going to also set the status to be processed, and then where are the updates coming from? Well, they're coming from our temporary table called temp updates and we can do the same filter as above just matching on the

[10:04] order ID and I should pass in the transaction here and in the end I can transaction here and in the end I can say await transaction commit async. So, how do you think this approach is going to perform compared to our existing

[10:16] approaches? Let's run the demo and find out. Here are the results with our two new approaches using common table expressions and temporary tables and you can see that common table expressions are on par with batched updates using

[10:31] the values clause and this is mostly what I expected as this is basically syntactic sugar that it just improves the readability of our query. However, the temp table approach came in the fastest at 12 milliseconds compared to

[10:44] the previously best unnest function at 14 milliseconds. So, these approaches are more or less on par with unnest still being my favorite as it's a lot tables approach. Now, let's make this more interesting. I'm going to bump this

[11:01] to 100,000 records. Now, if I try to just run this as is, we're going to run into an exception as soon as we hit a method that hits the maximum number of parameters in Postgres, which is exactly 65,535

[11:17] parameters. So, I won't be attempting to fix this. Instead, I'm going to go into the methods that run into this limit, which is the Dapper batched values approach and here I'm going to say if the record count is let's say greater

[11:33] than 50,000, then I just want you to return and this will effectively just not execute our code, which is completely fine for this demo. This is also going to apply to the EF Core approach with raw SQL, Dapper unnest

[11:46] should work and unfortunately common table expressions will not work. So, let's rerun this with these three examples turned off and you can see that these three examples come in at 0 milliseconds so we can effectively

[11:58] ignore them for this run. So, let's analyze what we do have. The naive Dapper approach, as expected, coming in worst at 25,000 milliseconds to update 100,000 records. Then we've got EF Core with the batching of the update

[12:11] statements at almost 5,000 milliseconds. So, significantly faster but still terribly slow. Dapper unnest coming in at the fastest time with 444 milliseconds and we've got temporary tables with the binary copy command at

[12:26] tables with the binary copy command at 539 milliseconds. So, this is still not as fast as the unnest approach. I suspect mainly because we've got multiple round trips to create a temp table, to populate it and then perform

[12:38] the update. However, this might change if we were passing a bigger number of parameters and I suspect the temp table approach might be more intuitive to some developers. So, there you have it. These are the seven approaches to perform an

[12:51] optimized database update with my favorite one being the unnest approach, run into a problem with the maximum number of parameters and it has excellent performance. Now, if you want to see how you can optimize your insert

[13:06] operations, make sure to take a look at this video next where I show you how you can do bulk inserts. If you enjoyed this video, consider gently tapping the like button to let me know. Thanks a lot for watching and until next time, stay

[13:20] watching and until next time, stay awesome.