Entity Framework Performance: How Your Seeks Can Turn Into Scans

Standard

Entity Framework is pretty handy, but as we’ve seen before, you can sometimes end up with unexpected performance hits. This one took me by surprise a while back.

Let’s start with a simple Producs table in our database.

CREATE TABLE [dbo].[Products] (
    [Id] [INT] NOT NULL IDENTITY,
    [Name] [nvarchar](MAX),
    [SIZE] [nvarchar](MAX),
    [TYPE] [INT] NOT NULL,
    CONSTRAINT [PK_dbo.Products] PRIMARY KEY ([Id])
)

Suppose we have a method to return the number of products:

 public int GetProductCount()
 {
     var count = db.Products.Count();
     return count;
 }

No problem, right? Now, let’s say we want to be able to optionally filter the query to only include products of a certain type. We want this to be as fast as possible, so we’ll add an index to the Products table:

CREATE INDEX IDX_Products__Type 
  ON Products(TYPE) 
  INCLUDE (Id)

With the index in place, we’ll modify our GetProductCounts() method to support filtering on product type. We can do this easily by passing in a nullable int containing the product type to filter on, or null if we don’t want to do any filtering.

The simplest way to write this is to check if the type is null directly in the filter expression:

public int GetProductCount(int? type)
{
    var count = db.Products.Count(p => type == null
                                    || p.Type == type.Value);
    return count;
}

Still pretty straightforward, and it does exactly what you would expect.

Unfortunately, it turns out that calls to GetProductCount() are far, far slower than they should be. Why? To figure that out, we’ll have to take a look at the SQL captured by a SQL profiler after calling GetProductCount(2):

DECLARE @p__linq__0 INT = 2,
        @p__linq__1 INT = 2;
 
SELECT 
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT 
        COUNT(1) AS [A1]
        FROM [dbo].[Products] AS [Extent1]
        WHERE (@p__linq__0 IS NULL) OR ([Extent1].[TYPE] = @p__linq__1) OR (([Extent1].[TYPE] IS NULL) AND (@p__linq__1 IS NULL))
    )  AS [GroupBy1];

There are four separate conditions in the WHERE clause. This is due to the EF context’s UseDatabaseNullSemantics flag being set to false by default. Setting it to true reduces the WHERE clause to the two conditions you might expect, but it doesn’t actually solve the performance problem. Let’s have a look at the query execution plan to see what’s actually happening:

index-scan

Yikes! It turns out we’re doing an index scan, not a seek. What effect does this have? A big one:

index-scan-2
Fortunately, it’s easy to modify our code to work around this and eliminate the scan. We’ll remove the null check from the expression in the Count() method, and instead, check for a null type outside of the query. If the type isn’t null, we’ll add the where clause to the query.

public int GetProductCount(int? type)
{
    var query = db.Products.AsQueryable();
    if(type != null)
        query = query.Where(p => p.Type == type.Value);
 
    var count = query.Count();
    return count;
}

The generated SQL looks pretty good:

DECLARE @p__linq__0 INT = 2;
 
SELECT 
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT 
        COUNT(1) AS [A1]
        FROM [dbo].[Products] AS [Extent1]
        WHERE [Extent1].[TYPE] = @p__linq__0
    )  AS [GroupBy1];

And the query execution plan looks even better:

Index seek in the execution plan

You can see that compared to the Index Scan above, the Index Seek is literally an order of magnitude cheaper:

Index seek details

Now, this simple SELECT COUNT query is fast enough in and of itself that the difference isn’t very noticeable. But if you have a bigger, more complicated query, it can have a huge impact – one query I dealt with went from ~1 second to ~30 seconds due to this issue.

In conclusion, be careful when using expressions with parameters in your queries! They might have unintended performance consequences.

Entity Framework: Never call GroupBy().ToDictionary()

Standard

Entity Framework generally works pretty well, but every once in a while it’ll throw something strange at you. Here’s one I experienced a while ago that caused an unexpected performance hit.

ToDictionary And GroupBy Don’t Mix

Let’s say I have a table containing a bunch of products, and I want to find out how many products of each size there are. This is trivial in SQL – it’s a group by, selecting the count and the name. Now let’s say I want to stick the results into a Dictionary<string, int> for further processing. The code might look something like this:

 var productSizeCounts = db.Products.GroupBy(p => p.Size)
                         .ToDictionary(p => p.Key, p => p.Count());

Not too bad, right? Here’s the generated SQL:

SELECT 
 [Project2].[C1] AS [C1], 
 [Project2].[Size] AS [Size], 
 [Project2].[C2] AS [C2], 
 [Project2].[Id] AS [Id], 
 [Project2].[Name] AS [Name], 
 [Project2].[Size1] AS [Size1]
 FROM ( SELECT 
 [Distinct1].[Size] AS [Size], 
 1 AS [C1], 
 [Extent2].[Id] AS [Id], 
 [Extent2].[Name] AS [Name], 
 [Extent2].[Size] AS [Size1], 
 CASE WHEN ([Extent2].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2]
 FROM (SELECT DISTINCT 
 [Extent1].[Size] AS [Size]
 FROM [dbo].[Products] AS [Extent1] ) AS [Distinct1]
 LEFT OUTER JOIN [dbo].[Products] AS [Extent2] ON ([Distinct1].[Size] = [Extent2].[Size]) OR (([Distinct1].[Size] IS NULL) AND ([Extent2].[Size] IS NULL))
 ) AS [Project2]
 ORDER BY [Project2].[Size] ASC, [Project2].[C2] ASC;

Huh? What the heck is that? Where’s our GROUP BY? Where’s the COUNT? Calling ToDictionary definitely did NOT do what we expected it to.

Let’s try again. This time we’ll project the values we want onto an anonymous type, and then call ToDictionary on that, instead of on our grouping:

 var productSizeCounts = db.Products.GroupBy(p => p.Size)
                     .Select(g => new { g.Key, Count = g.Count()})
                     .ToDictionary(p => p.Key, p => p.Count);

That gives us the following SQL:

SELECT 
    1 AS [C1], 
    [GroupBy1].[K1] AS [Size], 
    [GroupBy1].[A1] AS [C2]
    FROM ( SELECT 
        [Extent1].[Size] AS [K1], 
        COUNT(1) AS [A1]
        FROM [dbo].[Products] AS [Extent1]
        GROUP BY [Extent1].[Size]
    )  AS [GroupBy1];

Now that’s more like it!

But why?

ToDictionary() is an extension method on the Enumerable class in System.Linq. It doesn’t know anything about Entity Framework – all it can do is enumerate an IEnumerable. So when you call ToDictionary on your IQueryable<IGrouping<,>> that the GroupBy method returned, it simply materializes the query.

But why does Entity Framework generate such a strange query in the first place?

If you call GroupBy(…) and then enumerate the GroupBy(), Entity Framework has no way of knowing what you are going to do with the results. For all it knows, you are going to end up extracting the entities themselves from the GroupBy.

// I'm so sneaky... I'm gonna trick EF...or am i?
var productSizeCounts = db.Products.GroupBy(p => p.Size).ToList();
var firstProductWithEachSize = productSizeCounts.
                                  Select(p => p.FirstOrDefault());

If it had tried to be smart, and turned the query into the GROUP BY you were expecting, it wouldn’t have the necessary information available to materialize the entities you are asking for!

Explicitly selecting the fields you want lets EF generate the GROUP BY query you would expect, because it knows that’s all the information you’ll need.

ERROR_CANNOT_CONVERT_VIRTUAL_DIRECTORY_TO_APPLICATION

Standard

While trying to deploy an instance of BlogEngine.NET to an Azure virtual directory, I ran into a bizarre error:

Web deployment task failed. (The server experienced an issue processing the request. Contact the server administrator for more information. Learn more at: http://go.microsoft.com/fwlink/?LinkId=221672# ERROR_CANNOT_CONVERT_VIRTUAL_DIRECTORY_TO_APPLICATION.)

Ok, no problem; I’ll just Google the error and… no results! Go figure. ERROR_CANNOT_CONVERT_VIRTUAL_DIRECTORY_TO_APPLICATION simply doesn’t exist according to Google. Bing was no help either, as it decided to ignore the underscores and search for the individual words, even when the entire string was quoted.

It turned out to be an easy fix, though. In the website’s Configuration page in your Azure portal, head down to the ‘virtual applications and directories’ section. All you have to do is check the ‘Application’ check box for your virtual directory.

Azure virtual directory Application checkbox

 

Save your changes, and redeploy the web site. This time it’ll work!

iOS App Postmortems: Workout Tip Jar

Standard

Workout Tip Jar is now available on the app store.

Overview:

A workout tip jar is a jar that you deposit money into every time you work out. Once you reach your goal ($50, $100, etc.), you take the money out and treat yourself to something The Workout Tip Jar app is that jar, in app form!

Workout Tip Jar was a collaboration between myself and my wife. You can read about her thoughts on the app creation process over at fitnessprogrammer.com. Working with someone was a nice change from my other, primarily solo apps.

Development Time:

Probably around 8 hours. Half of that was spent writing the app, and the other half was spend creating launch images, screenshots, and icons….

Review Time (time spent in the iOS app submission queue):

8 days. To be fair, this was over the New Year’s Eve and New Year’s Day.

New technologies I learned and used:

This was my first app that involved audio. Fortunately, playing a small sound is an incredibly easy process.

What went right:

The best thing about Workout Tip Jar is that it was an untapped market (albeit a small one). It’s only been a few days since it was released, but it’s already #1 in a Google search for ‘workout tip jar’.

From a development perspective, it’s hard to pinpoint anything that went surprisingly ‘right’ because the application was so small and straightforward.

What went wrong:

We decided to release Workout Tip Jar as an iPhone-only application. In retrospect, I wish we would’ve taken the extra time to add an iPad version too. It probably wouldn’t have been that big of a deal.

Future plans:

Conceptually, Workout Tip Jar is pretty small in scope, so there’s not too much else to be done here in terms of new features. There are a couple of things that we still have to do though: adding an iPad version is #1 on the list, and improving the graphics and UI design is #2. We might also explore some ‘social’ features like a ‘Share on Facebook’ button when you reach your goal.

If there’s something you’d like to see in Workout Tip Jar, let me know!

iOS 7’s Subtle UITableView View Hierarchy Changes

Standard

While updating one of my oldest apps to iOS 7, I noticed that I was getting an exception when trying to grab a UITableViewCell from a UITableView. The code looked something like this:

- (IBAction)OnSomeReallyImportantStepperChanged:(id)sender
{
    UIStepper* stepper = sender;
 
    UITableViewCell* cell = (UITableViewCell*)[[stepper superview] superview];
    UITableView* table = (UITableView *)[cell superview];
    NSIndexPath* pathOfTheCell = [table indexPathForCell:cell]; // ***BOOM***

This code worked in iOS 6 and below, but threw an exception in iOS 7:

[UITableViewCell indexPathForCell:]: unrecognized selector sent to instance

What’s going on here? Well, a quick debugging session made it quite clear. cell is actually a UITableViewCellScrollView, not a UITableViewCell, and table is really a UITableViewCell, so naturally it didn’t have an indexPathForCell method.

The moral of the story is – don’t make too many assumptions about the view hierarchy, because it may change!

Announcing ReminderHero.com – Automatic Email and SMS Reminders

Standard

You may have noticed that the volume of posts – and in particular, iOS-related posts – has slowed down over the past month or so. I’ve been busy working on a side project which is now in the public beta phase, and I’d love your feedback!

Reminder Hero is a web app that lets you create intelligent reminders simply by sending an email or a text message. Just describe what you want to be reminded of and when – in plain english. Reminder Hero will email or text you back a reminder when it’s time!

Here are some examples of the kind of things you might ask Reminder Hero to remind you about:

Call Bob in 2h

Pay my credit card bill Tuesday night

Take out the trash every Friday at 8am

So, head on over to http://reminderhero.com, check out the site, and subscribe to the Beta mailing list! Don’t forget to send us your feedback!