[00:01] somewhere close to 90% of your time optimizing database queries or reads. updates? That's what we're going to answer in today's video where I'm going
[00:13] to give you a step-by-step progression from a naive update approach to an optimized one that yields the most performance. So, what is going to be our goal in optimizing our database updates? We have to start from the database,
[00:27] obviously, where we have this simple schema, one table called orders, and a couple of columns inside. An ID, a customer name, a status, and when this order was processed. And what we're trying to implement is some sort of
[00:41] batch processing scenario where we pull a number of orders into memory or we query them from the database, and then we have to update all of them in ideally one round trip to the database. This would be the most optimal scenario, but
[00:54] even then there are multiple ways to implement this which are going to give when I start this console app, we're going to connect to an instance of Postgres, which I have running locally. And in case you want to spin up a
[01:07] Postgres instance yourself, you can do so with Docker by typing in the Docker run command, giving a name to our container, and I'm going to set some dummy Postgres password. I'm going to create an initial database called
[01:19] this on the default port, and I will be running the Postgres 17 image. Once I Postgres in a container on my local machine, and I can connect to it from my
[01:31] console app. After that, we're going to connect to our database by opening up an Npgsql connection. I've got a connection string hardcoded at the top here, just our database that we configured, and username and password. So, back to our
[01:46] setup database method. Here, we're going to drop the table if it doesn't exist just to make this example repeatable. Then we're going to recreate it. Then we're going to do a bulk insert using the copy command, which is a very
[01:58] efficient way to insert a large number of records into a SQL table. So, once this completes, we should have a thousand records to test out our update scenarios, and I'm going to walk you through them one by one. A couple of
[02:10] other things that are happening is building out our update queue, which is essentially just querying the database to fetch the IDs of all the orders. And then, what we're trying to simulate is having to update these records in our
[02:22] database by matching each row with its unique ID, and then setting some time when this row was processed. So, when we're trying to update this record, we have to match it by the identifier, and we also have to set the value for the
[02:36] processed at column. In between each run of the five scenarios that I'm going to show you, I'm going to update the table to just reset the processed at column and set the status back to pending. So, that's what our setup consists of,
[02:49] populating the table, fetching the records that we have to update, and a database state for the next test. So, here's the first approach. I called it the Dapper naive approach, and this is something that you might commonly see if
[03:04] you are new to writing SQL. And the problem with this example, even though we are using SQL, which is generally more performing than using an ORM, is that we are running this inside of a loop. So, for every order update that we
[03:17] want to apply to our database, we are going to send one command over our existing connection to request an update to the database. Now, you might be thinking, "Why don't you just pass in an array representing the update
[03:29] operations?" And while this might work for something like an insert statement, an update statement requires a bit more work. And even in the case of an insert, Dapper isn't optimized to batch the records that you pass in as a parameter.
[03:43] them and basically end up doing one insert command per record, which still database. This is going to be our baseline scenario, and it's kind of the worst-case approach. Now, the second example, I'm going to use EF Core. And
[03:58] EF Core has an optimization where if you update a lot of records within the change tracker session, EF can batch those updates and send them together to the database to be executed. So, we're going to rely on this when we configure
[04:13] our database context, and by default I think the batch size is 42, which should be the answer to the meaning of life, but we're going to set it to let's say a minimum batch size of half the record count and up to a maximum batch size
[04:26] which is equal to our record count. In your testing, you can figure out which scenario gives you the optimal results. But, what makes this example different is, yes, we're using EF Core, so we have to create a database context instance,
[04:39] and we're going to select the IDs of the orders that we want to update. And then, this is the part that's costly because we have to fetch the orders into memory. And EF Core, because it uses change tracking, needs to have the context of
[04:52] what is the current state in the database to be able to track any changes that you make in memory. So, this is why this query is required. Then we can iterate over the orders, and we just call save changes, and this results in
[05:04] sending a number of update statements to the database to update the required rows. So, this might be a more optimal approach in terms of how the updates are applied to the database with batching of the update statements, but it's still
[05:17] not going to give us ideal performance because we have to query the orders into memory, and this is costly, which is why I'm going to show you our next approach, and this is where we fix the drawbacks of our previous implementation using
[05:30] Dapper. So, the goal here is going to be to send one update statement to the database containing all the rows that we want to update. And we're doing this by passing in the values as an argument, which is a long parameter string that
[05:45] this can be used as a sort of a temporary table which we can select values from and then use it to match the rows that we want to update and also So, let me just walk you through what this looks like. So, we're still opening
[06:00] all of that is pretty standard, and the key is going to be our update template here. So, we're going to update the orders table where we want to set the processed at column and the status of the order. And then, this part key is
[06:14] here. We say from, and then values, and then we have a parameter inside of our string that we can call string format on and pass in the parameter value. And what this is going to contain is the parameters representing our order IDs
[06:28] and when they were processed. This allows us to do a where statement to match the orders table ID column with the ID value passed in through a parameter, and then for that match, we can set the processed at column in our
[06:42] database table and perform the update. So, how we construct the parameters is by doing some string manipulation. We can select the order updates which we already have in memory, and then for each record, we want to select the ID
[06:54] parameter and the processed at value. So, this just constructs our SQL So, this just constructs our SQL command, and the end result is something is going to look like. We're going to construct a SQL command that's basically
[07:08] we're going to replace this parameter here with an array of values containing processed. Now, you have to keep in mind that these are query parameters. So, depending on which database you're using, there will be a limit to how many
[07:22] parameters you can pass in in a single query, and if you are above that limit, down into multiple commands. I believe Postgres by default supports somewhere around 65,000 parameters, so we should be well within the safe zone here. So,
[07:36] finally, we're going to construct our SQL command, and then use the dynamic parameters type from Dapper to populate our parameter values. And we just have to make sure to match the parameter name to the ones that we have specified in
[07:48] our SQL statement. And then, we can just call execute async, pass in the parameters, and all of this gets executed in one round trip, giving us a significant performance boost. You're going to see just how much in a moment.
[08:01] For comparison, I implemented a similar example using EF Core and its ability to execute raw SQL queries, which we can do with the execute SQL raw async method. are doing raw SQL queries, but as long as you're passing in your parameters
[08:17] properly, you should be on the safe side. So, this is very similar to the previous example, so I won't spend a lot of time here, and I want to show you one final example, which we can consider the cleaned-up version of the previous two.
[08:30] And this uses a special function from Postgres called unnest, which allows you to expand an array or multiple arrays into a set of rows that you can use for your update statement. You can see the syntax is a lot simpler, and what we're
[08:45] passing in as the parameters is the individual IDs and the processed at values. Now, we have to make sure that these are in the correct order, otherwise you're going to get a mismatch between the ID and the corresponding
[08:58] processed at value, but I just love how simple this syntax is. So, what unnest is going to do is take the IDs and the processed at values, and it's going to turn the arrays that we are passing in as parameters into individual rows
[09:11] containing the ID and the processed at value. And we do the same thing. We match the order by its ID, and we set the processed at column in the orders table. Now, depending on which database you're using, this function may or may
[09:24] definitely is, and I think it's pretty powerful compared to the other up, and I'm just going to add a breakpoint. I believe here will be appropriate just to show you what the SQL looks like before we send it to the
[09:39] database, and I'm not going to show you the performance difference between these complete the example, and then we're going to talk about the numbers. So, here's our update template. It contains our update statement, but we still have
[09:53] to construct the parameters part, which looks something like this. So, you can see, it's a long string containing our individual parameters. And then, when we replace it inside of our template, it looks something like this. So, we've got
[10:05] update from orders, setting the order columns, and then we say from and values, and we pass in the individual parameters. So, this is the final structure that we're going to send to our Postgres instance, and we're going
[10:17] to show you that. I'm going to stop this here, and then let's run this, and I'm going to zoom in a little. And it's going to take a moment or two to set up to get the results pretty quickly, and we can discuss the differences between
[10:32] them when they execute. So, the results are in. Let me zoom in on this so we can see it clearly. And then, here are the performance numbers measured by using a simple stopwatch. So, first, we've got our naive Dapper approach with the
[10:45] individual update statements, and this takes around 326 milliseconds for 1,000 takes around 326 milliseconds for 1,000 records. Then we've got EF Core with 590 here for that database query that we have to execute to fetch the orders into
[11:00] memory before being able to update them. Then you can see the batched approach, where we send just one update, and it's basically more than 10 times faster than the naive approach because it's just one update statement. Now, what's
[11:12] interesting to note here is that the EF Core version of our batched update is and this is where we're paying the price of the overhead introduced by EF Core.
[11:24] So, if you're looking for maximum performance, you probably want to fall down to Dapper to squeeze out as much as you can from your database. And lastly, we've got our unnest approach, which is almost 50% faster than the batched
[11:37] Dapper update approach, and you can see that unnest is really showing its worth here by completing the update in 12 milliseconds. Now, just to make this more interesting, let me go ahead and update the record count to 10,000, and I
[11:51] don't think I have to do anything else to make this work. So, let's run the benchmarks again, and we're going to see what the results are going to be. And here are the numbers. We've got our first approach with Dapper at 3 seconds
[12:04] to update 10,000 records. EF Core jumping ahead this time, and here the batching of the update statements is really showing its value, now becoming faster than the naive Dapper approach. Our batch updates are somewhat similar
[12:17] at 104 and 136 milliseconds, and the unnest approach still on top of the pack at 40 milliseconds to perform an update on 10,000 records. If you're looking to update a large number of rows in your database, and you're also using
[12:31] Postgres, you should give unnest a try and see if it gives you a performance boost. If you want to learn more about database performance, here's an awesome video where I'll demonstrate how adding a correct database index isn't enough to
[12:45] squeeze out the maximum performance. Instead, you also have to be careful how Check out the video, and you'll see what I mean. If you enjoyed this video, go ahead and smash the like button. Thanks a lot for watching, and until next time,
[12:58] a lot for watching, and until next time, stay awesome.