Video: Entity Framework Performance (Bulgarian)

Nikolay Kostov (Nikolay.IT)

Topics covered:

  • SQL Profilers
  • The N+1 Query Problem
  • Incorrect Use of ToList()
  • Incorrect use of SELECT *
  • Deleting objects faster with native SQL

Video (in Bulgarian)

Presentation contents:

What is SQL Profiler

  • SQL Profilers intercept the SQL queries executed at the server side
    • Powerful tools to diagnose the hidden Entity Framework queries
  • SQL Server has “SQL Server Profiler” tool
    • Part of Enterprise / Developer edition (paid tool)
  • A free SQL Profiler exists for SQL Server:

The N+1 Query Problem

  • What is the N+1 Query Problem?
    • Imagine a database that contains tables Products, Suppliers and Categories
      • Each product has a supplier and a category
    • We want to print each Product along with its Supplier and Category:
foreach (var product in context.Products)
{
  Console.WriteLine("Product: {0}; {1}; {2}",
    product.ProductName, product.Supplier.CompanyName,
    product.Category.CategoryName);
}
  • This code will execute N+1 SQL queries:
foreach (var product in context.Products)
{
  Console.WriteLine("Product: {0}; {1}; {2}",
    product.ProductName, product.Supplier.CompanyName,
    product.Category.CategoryName);
}
One query to retrive the products

Additional N queries to retrieve the category for each product

Additional N queries to retrieve the supplier for each product
  • Imagine we have 100 products in the database
    • That’s ~ 201 SQL queries -> very slow!
    • We could do the same with a single SQL query

Solution to the N+1 Query Problem

  • Fortunately there is an easy way in EF to avoid the N+1 query problem:
foreach (var product in context.Products.
  Include("Supplier").Include("Category"))
{
  Console.WriteLine("Product: {0}; {1}; {2}",
    product.ProductName, product.Supplier.CompanyName,
    product.Category.CategoryName);
}
Using Include(…) method only one SQL query with join is made to get the related entities

No additional SQL queries are made here for the related entities

Incorrect Use of ToList

  • In EF invoking ToList() executes the underlying SQL query in the database
    • Transforms IQueryable to List
    • Invoke ToList() as late as possible, after all filtering, joins and groupings
  • Avoid such code:
    • This will cause all order details to come from the database and to be filtered later in the memory
List orderItemsFromTokyo =
  northwindEntities.Order_Details.ToList().
  Where(od => od.Product.Supplier.City == "Tokyo").ToList();

Deleting Entities

  • Deleting entities (slower):
    • Executes SELECT + DELETE commands
NorthwindEntities northwindEntities = new NorthwindEntities();
var category = northwindEntities.Categories.Find(46);
northwindEntities.Categories.Remove(category);
northwindEntities.SaveChanges();
  • Deleting entities with native SQL (faster):
    • Executes a single DELETE command
NorthwindEntities northwindEntities = new NorthwindEntities();
northwindEntities.Database.ExecuteSqlCommand(
  "DELETE FROM Categories WHERE CategoryID = {0}", 46);
Share