Don't SUCK With Entity Framework - N + 1 Query Problem - Performance Tips Part 1

hi guys my name is Eva from my tested asp.net with today's video I'm going to start a new series about entity framework performance and during maybe five or six videos I'm going to show the most common mistakes people do with entity framework and of course we're going to see how to fix them I have created a simple project which has a CAD model with a couple of columns owner model which yes a cup of comms and both of them are connected with I want to many relationship in which each owner has a collection cats and each cat has a collection not collection story has one owner I'm using sequel server and a music entity framework or most of the mistakes I'm going to show are the same with entity framework six and with eight entity framework or and if there is a difference I'm going to tell you about it and I'm going to show it for the performance part I'm going to use the sequel server profiler here and I'm going to use the basic stopwatch from system dot Diagnostics if you want a better two to measure performance you can use benchmark net which is quite cool and quite a lot of projects are using it okay great the first issue I'm going to show is a very famous one and most of you should know it but in my opinion I should mention it nevertheless it's the N+ 1 query problem and that's when we want to get information from two tables and we make our queries in such a way that we make not a single join but a lot of queries for example if we want to get all the owners which name starts with one not start which name contains the number one and then we want all the cats which are related to these owners and we want to filter the cats again we will have n plus one if we don't write our query in a way that it's more efficient with entity framework or it's a little bit more difficult to do and plus one queries so most of the time you will be safe but I'm going to show what an N plus one is and how you can do it if you're not careful with the legacy entity framework six you are you can do n plus one without even think about it and it can lead to a lot of performance issues I'm going to show how I can do n plus one by using lazy loading technique in entity framework six in the next video but for now let's see how to create an n plus one if we are not careful with entity framework or so actually I'm getting all the owners here and then I need to get all the cats for these owners and the easiest way to do that is by fetching all the owners which satisfy the condition and then for each owner I'm going to load the cats I'm using DV dot entry collection laws that's explicitly loading the cats collection and then filtering kids one by one this will make n plus one queries because for each owner which for example is maybe I have 100 owners which satisfy this condition for each owner I'm going to make another query which is to get the cats of that owner and essentially that creates n +1 if I skip this call and don't call don't call the database load method I'm going to receive a no reference exception because I'm not initializing correctly the cat collection let's run it I receive an argument no exception because the cats is the cats collection is no one way to fix that is to use this kind of query deleted Android collection load but unfortunately these these lines load all the cats which are related to the owner and then we're featuring them so for example if the owner has 100,000 cats we will load them all which is not quite the best thing to do if you want to be a little bit more performant but still doing the n plus one we could do something like VAR cat's equals DB dot cats so get all the cats from the table which satisfy the condition the condition name contains one and the owner ID of the cat equals the ID of the owner we are currently using let's make this correct and if I run this query I didn't run the profile let's run it first if I wrong only the first N+ 1 query I'm going to see that I'm making quite a lot of queries here we can see that the first query gets the owner which satisfies the condition with the string 1 and then I'm calling the cats table for each owner here for owner ID 1 for all the owner ID to honor ID 3 and so on I'm calling I'm making a different query for each owner essentially that makes n plus 1 queries which is exactly the mistake we're talking about if I run it just to see how much time to stop the stopwatch will show I will see that it takes 4 seconds on this machine to execute all the queries to get the owners and then get their cats having this query so how to fix that issue with entity framework it's very easy we have two options the first one is using include and the second one is using select I prefer the select one but we're going to talk about why encode is not that great in a different video but to solve the n plus 1 the first way to do it is to use include and say give me all owners which contain number one in the name and all the cats this will essentially make only one query in which it will get both the owners and cats and then when we forage the owners and filter the cats we're not going to make additional queries the problem here is that the cats are not filtered so essentially we're holding all cats of each owner which satisfy this condition and then we filter the cats that's not the best scenario we're looking for but it will be performant enough it will be better than the n plus one let's see how it performs I'm going to restart the profiling tool if we see we have quite a lot of performance improvement from four seconds for the same data we reduced it to half a second which is great and let's check the profile if we see the n plus one still doing quite a lot of queries but the last query is a better query because it selects owners and gets with a single join and all owners are filtered in database so essentially we're having quite a lot of improvement here by not doing more than 100 100 queries but only doing a single one and the last and the best way to fix the n plus 1 issue is to use select by using select I can say okay I want to filter all owners and then project these owners to a new object which has the cats filtered here this test is not necessary actually it has a little bit it has difference if we make this tourist call I will explain it always prefer to use selects and get the data from the select directly that's the better way to do things in entity framework using select will save you a lot of queries and will give you the better performance in terms of not loading all the cats but loading only the cats you need so let's see what's the improvement saturator in quote and the Select should be similar in terms of time you can see there is not a lot of improvement here but if we take a look at the queries in the one query where we are using include we have a single where which calls only the owner's name and gets all the cats for each owner and then we filter the cats on the plant on the console in memory but with the Select will receive the filtered data directly in the database and we get the filtered data completely done so there is no need to filter it on the client as you can see we have a where clause in the cats table and a where clause in the owner state so essentially all the filtering is done on the database which is the better option we may not win in terms of time but we win in terms of memory when we use the Select because we don't need to get all the cats we need only we get only the cats we want to see so essentially that's everything we need to know about n plus 1 what an N plus 1 problem is calling having one table or wearing another and filtering right and doing quite a lot of queries that are actually not necessary these queries can be fixed with a single join the include which is a good solution but if you don't need all the cats it it's not the best one you will make only one query you need to filter or do anything on the cats on the client and not in the database which in some cases it will have a performance hit and using select in the select we have the best scenario because we will filter all the data on the database and we will get only the cats we require and not all of them and then filter them in the console app I'm going to explain the difference between the two lists go here but first I would like to thank my sponsors as you may know I have open source projects about the asp.net core framework if you don't know take a look at them they're quite cool because they allow you to test and assert the asp.net web applications you can test a lot of components for example control services models whatever you like it works perfectly for both MVC and API scenarios my sponsors are soft to me smart IT and noble heart we have a new sponsor stand thank you guys for joining us so two new smart IP and Nova have you guys Rock if you want to become a sponsor or backer you can see how to do it in my github repository we have the option with patreon open collective paypal and buy me a coffee guys now let's continue and I'm going to explain the two lists here in entity framework or 3.0 which is the version I'm using here there is no difference between the two lists code or not having catalyst call hello in an inner query because they were written Lync provider and now it translates the sequel differently but if you're using can see framework or two for example two point one to point to this code will make a difference if you don't apply the two lists here if you don't apply the two lists here the result will be the same result as we received earlier you have a single joint if you apply two lists like this in entity framework or two you will have two queries which is kind of fine but one of the queries will execute this code and the other queries will execute this code so essentially we have more than one query actually it may be five for six or seven depending on the owners but if you are if you write two lists here you may have additional queries as a matter of fact the entity framework core 3.0 these two lists go behaves exactly the same like it behaved in the entity framework six so I prefer it because when I use select I will always get a single query which is kind of fine of course the best scenario is if you have doubts and if you are not sure whether your query is good enough check it with the profiler you will see all the problems okay that was for now and I'll see you in the next video if you like this one hit the thumbs up button leave a comment below explain performance issue or entity framework if you like

Channel: Code It Up with Ivo

Views: 19,812

Rating: 4.8922157 out of 5

Keywords: dotnet, dot, net, csharp, core, dotnetcore, entity, framework, entity framework, query, queries, performance, tips

Id: Iv0FAFJoXbI

Length: 18min 13sec (1093 seconds)

Published: Thu Oct 17 2019