Common Parameter Sniffing In Entity Framework

Still Not A Developer

I’m going to use a funny example to show you something weird that I see often in EF queries.

I’m not going to use EF to do it, because I have no idea how to. Please use your vast imagination.

In this case, I’m going to figure out if a user is trusted, and only if they are will I show them certain information.

Here goes!

Trust Bust

The first part of the query establishes if the user is trusted or not.

I think this is silly because no one should ever trust users.

The second part will query and join a few tables, but one of the joins (to the Votes table) will only run if a user is trusted.

See where 1 = @iIsTrusted? That determines if the join runs at all.

Needless to say, adding an entire join in to the query might slow things down if we’re not prepared.

First I’m going to run it for user 2788872, who isn’t trusted.

This query finishes rather quickly (2 seconds), and has an interesting operator in it.

Henanigans, S.
Pump the brakes

The filter has a startup expression in it, which means it’s sort of a gatekeeper, here. If the parameter is 0, we don’t touch Votes.

If it’s 1… Boy, do we touch Votes. This is another case of where cached plans can lie to us.

Rep Up

If we run this for user 22656 (Jon Skeet) afterwards, we will definitely need to touch the Votes table.

I grabbed the Live Query Plan to show you just how little progress it makes over 5 minutes.

Dirge

The cached plan will look identical. And looking at the plan, it’ll be hard to believe there’s any way it could run >5 minutes.

CONFESS

If we clear the cache and run this for 22656 first, the plan runs relatively quickly, and looks a little different.

Bag of Ice

Running it for an untrusted user has a similar runtime. It’s not great, but it’s the better of the two.

Fixing It?

It’s difficult to control EF queries with much granularity.

You could branch the application code to run two different queries based on if a user is trusted.

In a perfect world, you’d never even consider that join at all, and avoid having to worry about it.

On the plus side (at least in this case), the good plan for trusted users runs in the same time as the good plan for untrusted users, even though they’re different.

If you’re feeling extra confident, you can try adding an OPTIMIZE FOR hint to your code, or implementing a plan guide.

Thanks for reading!

7 thoughts on “Common Parameter Sniffing In Entity Framework”

  1. Hi,

    It is possible to modify the query with a Entity Framework DB Command Interceptor in run-time and add a ‘option(recompile)’ to the query of affected DbContext instance. If the recompalition is not too expensive than it can be more maintainable workaround than cloning query logic into different branches.

    EF 6 Parameter Sniffing
    https://stackoverflow.com/a/45170243/2255675

  2. You hit on probably the best option of them all – don’t even include the extra join in the query if the user is not trusted.

    However, if doing that is not an option, there is another possible solution in EF that is maybe the best of both worlds. It is possible to use the DynamicLinq library to add an extra (dynamic) predicate with the hardcoded value of @iIsTrusted to the WHERE clause (i.e. “WHERE 0 = 0” or “WHERE 1 = 1”). The query generated by EF will still be proprerly parameterized and use the @iIsTrusted and @iPostId parameters. This query pattern will result in 2 different plans for the “query” – each one optimized for a different value of @iIsTrusted. The advantage here is that you avoid the CPU hit each time that the query is run when using an OPTION(RECOMPILE) hint.

    This pattern only works well when dealing with parameters that have a known and finite number of parameter values. Otherwise, this pattern will quickly cause query plan cache bloat, just as if the query was non-parameterized.

  3. I still fail to understand why articles like the stackoverflow one mentioned in the first comment refer to parameter sniffing as a problem. It is a fact. If the optimizer could not look at parameter values then it could not build effective plans.

    And while OPTION(RECOMPILE) may seem to alleviate pain the result of costing a bit more CPU is not the problem. Most often the issue is code that does not fully understand the problem.

    I realize that I am in the minority here but this is a classic example of solving the wrong problem via EF. It would be simple to have 2 stored procedures (on for @IsTrusted = 1, another for @IsTrusted = 0) that have independent JOIN structures and, hence, much more ideal plans for both sides. In addition, future changes that only affect @IsTrusted = 1 are isolated from the other case and don’t cause you to negatively impact performance of the other case.

  4. Isn’t command Interceptor a sledgehammer that captures EVERY query coming out of the EF engine??

    Conditional joins are bad no matter the cause. The proper solution is to simply not include them when not needed. If you remove an unnecessary part of a query that is actually an INFINITE performance improvement (some work divided by no work)! 😀

    Addressing the poster who was worried about plan compiles and their CPU burn:modern CPUs do 3-5 BILLION OPERATIONS PER SECOND PER CORE! I will jump through EXTRAORDINARY hoops to trade CPU ticks for almost ANYTHING else – especially avoiding a disastrously bad query plan.

Leave a Reply

Your email address will not be published. Required fields are marked *