EF Core Performance: How Do You Optimize Database Updates?

hi my name is Milan and today we're going to be talking about updating the database using any favor core we're going to see how we can optimize this and why any framework might not be the best tool for the job in some situations so stay tuned to find out more so we're starting out with an empty.net6 web API the first Winger we're going to do is add an entities folder we want to create entities for our awesome company so the first entity that we are going to need is going to be obviously the company so we're going to add it now we will use file scope namespaces because I like it more starting from.net6 we're going to add an integer field for the ID and we're also going to add a name for our company all right that looks good the other entity that we need is the employee of course so let's go ahead and add the employee we're going to use file scope namespaces in this class Also let's add the ID we also need a name for our employee so we're going to add that we're going to assign an empty string as the default value and let's also add the another field for the salary of the employee and this is what we are going to be using in updating the database you'll see in just a moment and one last thing that we're going to need is the company ID this is going to be our foreign key that's going to point to the companies table back in the company class we're going to introduce a nullable date time field this is going to be the last salary update date and time in UTC and we're also going to add a navigation property for the employees this we're going to use this with any framework to configure our relationship and also so that we can load the employees together when together with the company when fetching the company from the database now we're going to add some nougat packages that we are going to need we are going to be using SQL Server so that's the package that we're going to add okay another package that we are going to need is the Microsoft 1080 framework core tools we're going to need this for managing database migrations from the console so we are going to go ahead and add that one as well now the next thing that we want to do is add our database context so let's go ahead and do that file scope namespace of course and our database context is going to inherit from the DB context class coming from any framework core and we're going to do two things here we're first going to add a Constructor that's accepting a DB context options argument and we're going to pass it to the base Constructor and we're also going to override the on model creating method so that we can configure our two entities company and employee so first we're going to configure the company we're calling model builder entity and specifying the company as the generic argument and then we're going to write a Lambda method first we want to specify that the company entity should be stored in the company's table and then I want to configure the relationship with the employees so I do Builder dot has many and then I specify the employees then I say with one because each employee can only have one company and I go ahead and specify the foreign key which is the company ID all right now I want to configure the employee entity we're going to say model builder dot entity and specify employee as a generic argument and then we're going to create a Lambda method inside of the body of the Lambda we're going to say Builder to table and here we want employees and that's all we need for configuring the employee entity now I want to go go back to the company configuration and I want to see some initial data so that we have something to work with when we start our application so we're going to say Builder dot has data and I'm going to create a new company with an ID of one and the name of awesome company now we'll do something similar for the employees we're using enumerable dot range method which allows us to create a range of integer values we're starting from the value of 1 and we want 1000 values then we are going to chain a call to select which allows us to access each value from 1 to 1000 and we're going to create a new employee instance and we're going to enumerate this enumerable so the ID of the employee will be a number from one to one thousand the name is going to be an interpolated string the salary for everyone is going to start out at 100 and the company ID will be 1 because that's what we said when we were seeding the company and of course we have to say Builder has data and specify the employees all right this is looking good now what we have to do is configure Entity framework to connect to our database so we go back to program.cs and on the Builder we have to call services dot add dbcontext we specify our database context that we just created and we want to configure our database context we're going to pass in a Lambda and say use SQL server and we're going to pass in the connection string to our database all right now then we have everything in place we can go ahead and create our database migration we're going to open up the console and write add migration I'm going to give it the name add company and employee and we press enter all right the migration is created as you can see there's commands for creating the tables and seating the data and now I'm going to update the database and I'll speed this part up because it's going to take a while to insert all of those records now we have everything that we need in the database we added one company and a thousand employees so now we're gonna go ahead and do something interesting I want to create an API endpoint to which I can send a company ID and in this API endpoint I want to increase the salary of all the employees by 10 we're going to use the minimal apis that came with.net6 so I say app.map put I'm going to give it a route which will be increased salaries and I'm going to pass in an async delegate which takes in a company ID and a database context the company ID will come from the query string and the database context will be resolved from the services the asp.net runtime is smart enough to figure this out for for us so we don't have to specify anything explicitly so inside of our delegate the first thing we want to do is fetch our company from the database using any framework so we're going to say dbcontacts dot set specify company as the generic argument I also want to include the employees so that they are fetched from the database together with the company so add an include statement and I'm going to say first or default async and specify the condition I want to get the company with the ID that matches the company ID I got from the API so if the company is now we didn't find one in the database and I want to return a not fund result so I'm going to say return results dot not found and I'm going to write a descriptive error message now I want to iterate over all of the employees in our company so I'm going to go ahead and do that I'm going to say company dot employee dot for each and for every employee I want to increase the salary of that employee by 10 so I'm going to multiply it by 1.1 which will effectively increase the salary by 10 percent now I also want to update the company remember that we added a last salary update property so I'm going to set it to datetime.udc now because I want to be updating both the companies and the employees table in the same HTTP I call DB context save changes async and then I want to return a result I'm going to say return results dot no content all right everything looks good I think we can start our API now it is up and running all right let's head over to postman where I already created our HTTP request and let's go ahead and click Send this is going to hit our breakpoint that we added so I'm going to skip over the query and as you can see we loaded the company from the database and down here in the output window you can see the SQL query that energy framework generated for fetching the company so moving on I'm going to skip over the updating of the employees because there's a lot of them and I'm going to update the company's last salary update and now we can go ahead and call Save changes now take a look at the output window again when I call Save changes and notice uh what's going on any framework is going to go ahead and create a lot of update statements how many is a lot well in this case it's going to be 1000 update statements there's going to be one for each of the employees that we have on the company also notice that every update statement does the same thing it sets the salary to some new value for the employee with a specific ID now why is this a problem in this case energy framework is going to execute 1000 update statements for the employees and also one update statement for the company remember that we also updated the company so there's going to be 1001 update statements for just increasing the salary by 10 percent now is there a better way to do this well there is and I'm going to show you now I'm going to go ahead and copy our existing endpoint and use it as a base for creating the new one first I'll update the route I'll append SQL to it I'm going to go ahead and remove the statement for including the employees and the part that I want to change here is how we update the employee salaries so instead of using any framework for this we can do something different we can write a SQL command that will increase the salary of our employees by 10 so we're going to say dbcontacts.database dot execute SQL interpolated async now what this method allows us to do is write our own SQL command which will execute directly on the database so we're going to say update employees which is the table we want to update set the salary to whatever the salary was before times 1.1 which is a 10 increase and we're going to add a where statement which will say where Company ID is and we're going to pass in the ID of the company that we fetched using in any framework and the rest of the method Remains the Same this endpoint now behaves the same as a previous one so let's head over to postman where we're going to call our endpoint again we fetch the company from the database and now pay attention what happens when we get to our SQL command when I execute this line notice that in the output window it's going to Output the SQL statement that we just wrote and now if we move on and execute the save changes call it's also going to generate the SQL for updating the company so we have two SQL statements this time as opposed to 1001 that we had previously so this is a massive Improvement now there is one small issue that I want to show you let's go ahead to SQL Server management Studio I'm going to select all of the employees and notice that at this point in time they all have the same salary which is 133. now I'm going to go back to postman and hit our endpoint again we fetch the company we execute our update statement and now I go back to SQL server and execute the select statement again and notice that this time the salary is updated it says 146. but we didn't still call Save changes so our update statement executes regardless of the save changes call and this can be a problem imagine if we executed the update statement and it works and then we go to save changes and for some reason it crashes but the previous update already went to the database and completed successfully so now we have a problem our database is not in a consistent state so how can we go about and fix this luckily it's not that complicated all we need is just to wrap everything that should execute together into one database transaction so we're going to say dbcontacts dot database dot begin transaction and this will start our transaction and after we have completed all of the updates that we want to do we go ahead and call dbcontacts Dot database dot commit transaction and this will complete our transaction and save all of the updates to the database so let's start on the application again we're back in SQL Server you can see that the salaries are now 146. we're calling on our endpoint again and pay attention this time what happens so we're fetching the company from the database we are now starting our transaction we are executing the update statement for increasing the salaries now let's hop on over to SQL Server again and see what's going on here if we select all of the values again we can see that they're still the same as before it still says 146. again if we call Save changes and go back to SQL server and select all of the values again they're still 146. it is only after we call commit transaction that we can see the changes applied to the database in this way we ensure that all of the updates are running in the same transaction and this can protect us from unexpected database failures so I want to show you a bonus implementation which is going to be very similar to this one first let's go ahead and add one more nougat package we're going to be adding Dapper Dapper is an amazing library that we can use to complement any new framework and we're going to see how we can combine them to achieve the same behavior that we had all along I'm going to copy over the endpoint that uses the SQL update statement I'm going to update the route and now I want to change this part that executes our update command so in order to be able to use this with Tapper I'm going to say dbcontacts.database and I need to fetch the database connection somehow and I can do this using the getdb connection method on the database instance and now I have access to Dapper specific methods the one I want to use is executasing which is perfect for an update statement and we're going to go ahead and write the same SQL as we did before so update employee we're going to set the salary to whatever it was before times 1.1 and I'm going to add a where statement now notice this time that it's a little different Dapper uses placeholders as arguments and now we need to specify my arguments as a separate parameter so I'm going to create an anonymous object which will have a company ID property which will be the company dot ID of the company that we already fetched from the database and these two property names have to match for everything to be working correctly now Dapper will not run in the same transaction the 1080 framework is implicitly using so we have a similar problem that we had from our previous endpoint and we solved that one using transactions so we already have a transaction here it's started and committed after we apply all the updates the one thing that we need to do is to tell Dapper to use our transaction that we already created begin transaction actually returns a transaction object so we're going to add a variable to capture it and to pass a transaction to Dapper we need to use transaction and call the getdb transaction method which will return the proper object that Dapper is expecting and now that we have this in place everything will function the same as in the previous two endpoints I'm going to go ahead and add a breakpoint again start the application and we want to try out and see how this is working so we jump over to postman and we call our endpoint we hit our breakpoint we fetch the company from the database and we stop at our update statement this time using Dapper so let's jump on over to SQL Server notice that the salary is 161. and after we execute our update with Tapper and hop back over to SQL Server notice that the salary remains unchanged so it seems like it's working let's continue on and see what happens after calling save changes and committing the transaction we can see that it really is applied in the database only after committing the transaction now I want to briefly talk about performance we're going to call the first endpoint it completes in around 250 milliseconds which is not that great but remember that energy framework is creating 1001 update statements for this endpoint now let's check the performance of our second endpoint that's using SQL or updating the database and as you can see it's much faster it completes in just 25 milliseconds which is absolutely great we are basically increasing the performance by 10 times by just using the proper tool for the job which in this case is SQL and let's also take a look at the last endpoint that's using Dapper instead of calling SQL from any framework and as you can see it completes in about 21 milliseconds so it's just slightly faster now Dapper is in general always more performant than SQL Server which is why we see this slight performance Improvement but you have to ask yourself is it really worth it to introduce Dapper to only gain a slight performance Improvement but you have an increase in complexity because now you have to manually take care of creating the transaction and making sure that you specify it when calling the methods that Dapper exposes so that's just something to consider we saw how we can perform one-to-many updates using any framework we then saw how we can optimize on this using a SQL update statement and in the last example we introduced Dapper for executing our SQL update to gain a slight performance Improvement if you like this video make sure to give it a like and also consider subscribing to the channel so that you don't miss any of my future videos until next time stay awesome

Channel: Milan Jovanović

Views: 18,189

Keywords: ef core performance, ef core performance tuning, ef core performance tips, ef core performance vs dapper, ef core performance optimization, ef core 6 performance, ef core perfomance tuning, ef core perfomance tips, ef core perfomance vs dapper, ef core perfomance optimization, ef core 6 perfomance, dotnet, software engineering, c sharp, dapper, sql server

Id: enpblqLhzzo

Length: 20min 6sec (1206 seconds)

Published: Tue Aug 09 2022