{"id":321151,"date":"2023-03-02T15:51:12","date_gmt":"2023-03-02T14:51:12","guid":{"rendered":"https:\/\/blog.jetbrains.com\/?post_type=dotnet&p=321151"},"modified":"2023-03-02T15:51:15","modified_gmt":"2023-03-02T14:51:15","slug":"optimizing-entity-framework-core-database-queries-with-dynamic-program-analysis","status":"publish","type":"dotnet","link":"https:\/\/blog.jetbrains.com\/en\/dotnet\/2023\/03\/02\/optimizing-entity-framework-core-database-queries-with-dynamic-program-analysis","title":{"rendered":"Optimizing Entity Framework Core Database Queries With Dynamic Program Analysis"},"content":{"rendered":"\n

Whether you use Object-Relational Mappers (ORMs) like Entity Framework Core (EF Core) or Dapper to work with data and databases in your applications or you use the underlying ADO.NET and SqlConnection<\/code> directly, it\u2019s easy to run into situations where performance could be improved by changing how you access and query the database<\/strong>.<\/p>\n\n\n\n

Profiling your application regularly can help find performance issues and bottlenecks. Even small optimizations can reduce database load and help make your application faster. \u201cBut who has time to profile regularly? We need to ship \u2013 now!\u201d<\/em><\/p>\n\n\n\n

What if you could get meaningful tips to optimize your application just by running it<\/strong>? This is where Dynamic Program Analysis (DPA) in ReSharper and JetBrains Rider comes in.<\/p>\n\n\n\n

In this post, let\u2019s have a look at what Dynamic Program Analysis<\/strong> is and how it can help you find and optimize common issues in database access code.<\/p>\n\n\n\n

What is Dynamic Program Analysis again?<\/h2>\n\n\n\n

Dynamic Program Analysis, or DPA for short, automatically monitors your application for common memory allocation and database issues. It is available in both ReSharper and Rider.<\/p>\n\n\n\n

When a method call allocates more memory than the specified threshold, DPA will mark it as a potential issue in the editor:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Similarly, when a command retrieves too many results from the database, you\u2019ll see a warning to that effect in the editor:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

DPA checks for various memory allocation issues, such as closures and allocations to the large object heap (LOH) and the small object heap (SOH). In addition, it monitors how you\u2019re querying your database and helps discover long queries, queries that return too many results, and other potential issues.<\/p>\n\n\n\n

In ReSharper and Rider, code is highlighted when an issue is discovered. In addition, the DPA icon in the status bar will be either green (no issues detected) or red (potential issues that need your attention). You can navigate to the list of issues and, from there, view the stack trace, jump to the corresponding code in the editor, and investigate the issues further.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Tip: To learn more about memory profiling, check out our tutorial about <\/em>how to profile application code in Rider<\/em><\/a> (including using DPA).<\/em><\/p>\n\n\n\n

In a previous post, we discussed using Dynamic Program Analysis to detect memory issues<\/a>. In the remainder of this post, let\u2019s look at how DPA can help detect some common database issues and how to fix them. We\u2019ll use Entity Framework Core (EF Core), but keep in mind DPA works with Dapper, ADO.NET, and more.<\/p>\n\n\n\n

Queries returning too many records<\/h2>\n\n\n\n

One common issue with database access is fetching too much data. A very straightforward example would be the following code, which requests an airport from the Airports<\/code> table using the airport IATA code:<\/p>\n\n\n\n

var airports = await database.Airports.ToListAsync();\nvar brusselsAirport = airports.First(it => it.NormalizedIata == \"BRU\");\n\nConsole.WriteLine($\"{brusselsAirport.Country} ({brusselsAirport.Timezone})\");<\/pre>\n\n\n\n
When you run the application, DPA will highlight the first line of code in this snippet in the editor. Looking at the Dynamic Program Analysis tool window, you\u2019ll find more details about this (potential) issue:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
This particular query returns over 12,000 records! Looking at the SQL statement, you can see we forgot the WHERE<\/code> clause, which could have limited the number of records returned. We can solve this in code by allowing the database to filter the records before returning the result to the application:<\/p>\n\n\n\n
var brusselsAirport = await database.Airports.First(it => it.NormalizedIata == \"BRU\");\n\nConsole.WriteLine($\"{brusselsAirport.Country} ({brusselsAirport.Timezone})\");<\/pre>\n\n\n\n
The example in this section may look contrived, but you\u2019d be surprised at how common the mistake of filtering an unnecessary amount of data in memory can be! Being aware of the data you retrieve \u2013 as opposed to the data you use \u2013 can help mitigate performance issues later. Thanks to Dynamic Program Analysis, you can catch these issues early and often.<\/p>\n\n\n\n
Beware of N+1 performance<\/h2>\n\n\n\n
\u2018N+1\u2019 problems are common when lazy loading is enabled. With many developers migrating from EF 6 to EF Core, some have had to keep the feature enabled as they take small steps to pay down technical debt. While it\u2019s not recommended to enable this feature, you can add lazy loading using the \u201cMicrosoft.EntityFrameworkCore.LazyLoading\u201d package on NuGet. Let’s look at an example of when \u2018N+1\u2019 problems can degrade performance.<\/p>\n\n\n\n
Our database contains Airports<\/code> and Countries<\/code>, and each Airport<\/code> has a related Country<\/code> through a foreign key. Here\u2019s how you could write the number of airports per country to the console:<\/p>\n\n\n\n
var countries = await database.Countries\n   .OrderBy(country => country.IsoCode)\n   .ToListAsync();\n\nvar countriesAndAirports = countries\n   .Select(country => new\n   {\n       Country = country,\n       Airports = database.Airports\n           .Where(airport => airport.Country == country)\n           .ToList()\n   })\n   .ToList();\n\nforeach (var countryAndAirports in countriesAndAirports)\n{\n   Console.WriteLine(\n       $\"{countryAndAirports.Country.Name} has {countryAndAirports.Airports.Count} known airports.\");\n}<\/pre>\n\n\n\n
In the editor, this particular example will light up like a Christmas tree!<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Two lines of C# result in 237 database commands, and one of those commands returns 3,673 records. While it may look like a single query, it\u2019s definitely not! As we loop through the countries, we are making an additional query for airports in that particular country. In other words, for every Country<\/code> entity (N<\/strong>), we are making a separate call to the database for the list of Airport<\/code> entities (+1<\/strong>).<\/p>\n\n\n\n
In this case, Country<\/code> has a navigation property Airports<\/code>. Entity Framework Core can load this related data as part of the query, using the .Include()<\/code> method:<\/p>\n\n\n\n
var countriesAndAirports = await database.Countries\n    .Include(country => country.Airports)\n    .OrderBy(country => country.IsoCode)\n    .ToListAsync();<\/pre>\n\n\n\n
If you don\u2019t need all properties of Country<\/code> and every Airport<\/code>, you can also use a projection. If all you need in this application is the country name and the number of airports in that country, this snippet could be rewritten as follows:<\/p>\n\n\n\n
var countriesAndAirports = await database.Countries\n    .Select(country => new\n    {\n        Country = country.Name,\n        NumberOfAirports = country.Airports.Count\n    })\n    .ToListAsync();<\/pre>\n\n\n\n
Using this projection, the database will handle the aggregation (count) of airports in every country for us and only return the data that the application really needs.<\/p>\n\n\n\n
Cartesian explosion and AsSplitQuery<\/h2>\n\n\n\n
After optimizing a query, always run the application again and see if the optimized query is gone from the list of potential issues. You just might find that a particular optimization results in another database performance issue.<\/p>\n\n\n\n
Let\u2019s try and write the number of airports per country to the console. We already learned that N+1 queries are not ideal, so let\u2019s include the Airports<\/code> navigation property:<\/p>\n\n\n\n
var countriesAndAirports = await database.Countries\n    .Include(country => country.Airports)\n    .OrderBy(country => country.IsoCode)\n    .ToListAsync();\n\nforeach (var countryAndAirports in countriesAndAirports)\n{\n    Console.WriteLine(\n        $\"{countryAndAirports.Name} has {countryAndAirports.Airports.Count} known airports.\");\n}<\/pre>\n\n\n\n
This example will also be flagged by DPA. Once again, it looks like we\u2019re retrieving too many records from the database!<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
The reason for this excessive amount of records being retrieved is because of cartesian explosion<\/em>. Entity Framework Core translated the LINQ query to a SQL LEFT JOIN<\/code>, as highlighted in the screenshot above. If you run the resulting SQL query against the database, you\u2019ll see the data for every country is repeated over and over again, together with the airport data.<\/p>\n\n\n\n
...\n20,BE,Belgium,BE,BELGIUM,297,Antwerp,20,ANR,51.18940,4.46028,Antwerp International Airport (Deurne),ANTWERP,ANR,ANTWERP INTERNATIONAL AIRPORT (DEURNE),Europe\/Brussels\n20,BE,Belgium,BE,BELGIUM,298,Beauvechain,20,\\N,50.75860,4.76833,Beauvechain Air Base,BEAUVECHAIN,\\N,BEAUVECHAIN AIR BASE,Europe\/Brussels\n20,BE,Belgium,BE,BELGIUM,299,Kleine Brogel,20,\\N,51.16830,5.47000,Kleine Brogel Air Base,KLEINE BROGEL,\\N,KLEINE BROGEL AIR BASE,Europe\/Brussels\n20,BE,Belgium,BE,BELGIUM,300,Brussels,20,BRU,50.90140,4.48444,Brussels Airport,BRUSSELS,BRU,BRUSSELS AIRPORT,Europe\/Brussels\n...<\/pre>\n\n\n\n
This is quite wasteful: A lot of data is going over the wire, and Entity Framework Core has to enumerate these rows and map them into objects.<\/p>\n\n\n\n
A better way of writing this particular query is using the .AsSplitQuery()<\/code> method. Doing so will instruct Entity Framework Core to split the query into multiple partial queries, each of which retrieves a particular entity from the database.<\/p>\n\n\n\n
var countriesAndAirports = await database.Countries\n    .Include(country => country.Airports)\n    .OrderBy(country => country.IsoCode)\n    .AsSplitQuery();\n    .ToListAsync();<\/pre>\n\n\n\n
By running this LINQ query in split-query mode, Entity Framework will retrieve all countries once, and all airports once, and then link them together in application memory.<\/p>\n\n\n\n
While this too sounds wasteful, remember that the previous query required Entity Framework Core to process a lot of duplicate data from a set of 12,519 records. If you really want to know which one is better for your situation, this is a perfect opportunity to use a full-blown profiler like dotTrace<\/a>.<\/p>\n\n\n\n
Note: The default threshold for the \u201cDB records\u201d inspection is 100. There are just over 250 countries in the world, so the threshold may need to be slightly changed even when using .AsSplitQuery()<\/code>. You can do this in the <\/em>Thresholds<\/em><\/strong> tab of the Dynamic Program Analysis tool window:<\/em><\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
If you want to suppress a specific warning or set a different threshold for a specific method, you can do so with the SuppressMessage<\/code> attribute on the method that makes a specific query. The MessageId<\/code> property of this attribute can be used to change the threshold:<\/p>\n\n\n\n
[SuppressMessage(\"ReSharper.DPA\", \"DPA0007: Large number of DB records\",\n    MessageId = \"count: 250\")]<\/pre>\n\n\n\n
Detecting slow queries<\/h2>\n\n\n\n
Slow queries in Entity Framework Core can be caused by a number of factors, including the complexity of the query itself, the amount of data being returned, and the performance of the database server.<\/p>\n\n\n\n
No matter the reason, DPA will let you know when a query exceeds a specific threshold (defaults to 500 ms):<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Even though Dynamic Program Analysis is not a database profiler, it can help highlight issues like these and indicate that further investigation might be needed. You may want to start by inspecting the query execution plan<\/a> of this particular query, or even look into using a full-blown database profiler.<\/p>\n\n\n\n
In this case, an index on the Iata<\/code> column would help speed up the query. You can add one in the database context\u2019s OnModelCreating<\/code><\/a> method:<\/p>\n\n\n\n
public class Database : DbContext\n{\n   \/\/ ...\n  \n   protected override void OnModelCreating(ModelBuilder builder)\n   {\n       \/\/ Airport indices\n       builder.Entity<Airport>().HasIndex(nameof(Airport.Iata));\n   }\n}<\/pre>\n\n\n\n
You can then create and apply a new database migration to add the index to your database.<\/p>\n\n\n\n
Too many connections<\/h2>\n\n\n\n
In general, it\u2019s OK to open multiple database connections in Entity Framework Core. The framework is designed to manage connections to the database for you, so you don’t have to worry about manually opening and closing connections.<\/p>\n\n\n\n
When using multiple database connections with Entity Framework Core, make sure to consider:<\/p>\n\n\n\n