Writing efficient queries with Entity Framework Code First (Part 3)

2013-02-18

In this series, we will explore the Social Recipes sample application. This is a simple application that is intended to show some of the common inefficient queries that can be generated using Entity Framework (EF) Code First. The application is built using ASP.NET MVC4 and Entity Framework 5.

The application is site that allows users to post, rate, and review recipes, create and join groups, and share recipes with those groups. For more information on the domain, refer to Part 1 - Eager Loading.

Loading too much data

In the last post, we explored using LINQ projections to generate SQL queries that only retrieve the data that is needed to display a particular page. Using LINQ projections, we were able to improve performance substantially. There is, however, one more problem to solve with this Groups page.

We know the Groups page now performs well when we have 100 Groups in the database. You know what’s cooler than 100 Groups? Let’s see what happens when we have 1,000 groups on the page.

The page is taking 800ms to render. The problem is that we are trying to display ALL the Groups at once. Really, this is a NOT great idea. As our user base grows, the website will get slower and slower.

Paging

Luckily, we can implement a strategy called paging using the Skip and Take extensions methods.

First, let’s change the controller action to add a page number parameter and use that parameter when we add the Skip and Take methods to our query.

public ActionResult Index(int pageNumber = 0)
{
    ViewBag.PageNumber = pageNumber;
    const int pageSize = 25;
    DateTime twoDaysAgo = DateTime.Now.AddDays(-2);
    var groupSummaries = _recipeContext.Groups.OrderBy(g => g.Name)
        .Select(g => new GroupSummaryModel{
            Id = g.Id,
            Name = g.Name,
            Description = g.Description,
            NumberOfUsers = g.Users.Count(),
            NumberOfNewRecipes = g.Recipes.Count(r => r.PostedOn > twoDaysAgo)
        }).Skip(pageSize * pageNumber)
          .Take(pageSize);

    return View(groupSummaries);
}

Next, we update the Groups page to have links to the Next and Previous pages. To keep the example simple, I won’t check to see if we should actually be showing the Previous and Next links.

<div>
    @Html.ActionLink("Previous", "Index", new { pageNumber = ViewBag.PageNumber - 1})
    @Html.ActionLink("Next", "Index", new { pageNumber = ViewBag.PageNumber + 1})
</div>

Now, we are back to rendering the Groups page in under 50ms. The nice thing is that no matter how many groups are in the database, the page will always take approximately the same amount of time to load.

When we look at the generated SQL, we see that it is using the TOP command to only load 25 rows at a time.

DECLARE @p__linq__0 DateTime2 = '2013-02-16T17:08:19'

SELECT TOP (25) 
[Project3].[Id] AS [Id], 
[Project3].[Name] AS [Name], 
[Project3].[Description] AS [Description], 
[Project3].[C1] AS [C1], 
[Project3].[C2] AS [C2]
FROM ( SELECT [Project3].[Id] AS [Id], [Project3].[Name] AS [Name], [Project3].[Description] AS [Description], [Project3].[C1] AS [C1], [Project3].[C2] AS [C2], row_number() OVER (ORDER BY [Project3].[Name] ASC) AS [row_number]
        FROM ( SELECT 
                [Project2].[Id] AS [Id], 
                [Project2].[Name] AS [Name], 
                [Project2].[Description] AS [Description], 
                [Project2].[C1] AS [C1], 
                [Project2].[C2] AS [C2]
                FROM ( SELECT 
                        [Project1].[Id] AS [Id], 
                        [Project1].[Name] AS [Name], 
                        [Project1].[Description] AS [Description], 
                        [Project1].[C1] AS [C1], 
                        (SELECT 
                                COUNT(1) AS [A1]
                                FROM  [dbo].[RecipeGroups] AS [Extent3]
                                INNER JOIN [dbo].[Recipes] AS [Extent4] ON [Extent4].[Id] = [Extent3].[Recipe_Id]
                                WHERE ([Project1].[Id] = [Extent3].[Group_Id]) AND ([Extent4].[PostedOn] > @p__linq__0)) AS [C2]
                        FROM ( SELECT 
                                [Extent1].[Id] AS [Id], 
                                [Extent1].[Name] AS [Name], 
                                [Extent1].[Description] AS [Description], 
                                (SELECT 
                                        COUNT(1) AS [A1]
                                        FROM [dbo].[GroupUsers] AS [Extent2]
                                        WHERE [Extent1].[Id] = [Extent2].[Group_Id]) AS [C1]
                                FROM [dbo].[Groups] AS [Extent1]
                        )  AS [Project1]
                )  AS [Project2]
        )  AS [Project3]
)  AS [Project3]
WHERE [Project3].[row_number] > 0
ORDER BY [Project3].[Name] ASC

What’s Next?

Get the source Social Recipes on GitHub

Loading too much data

Paging

What’s Next?

More Reading