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.
DECLARE @UserId INT = 22656, --2788872
@PostId INT = 11227809,
@IsTrusted BIT = 0,
@SQL NVARCHAR(MAX) = N'';
SELECT @IsTrusted = CASE WHEN u.Reputation >= 10000
THEN 1
ELSE 0
END
FROM dbo.Users AS u
WHERE u.Id = @UserId;
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.
SET @SQL = @SQL + N'
SELECT p.Title, p.Score,
c.Text, c.Score,
v.*
FROM dbo.Posts AS p
LEFT JOIN dbo.Comments AS c
ON p.Id = c.PostId
LEFT JOIN dbo.Votes AS v
ON p.Id = v.PostId
AND 1 = @iIsTrusted
WHERE p.Id = @iPostId
AND p.PostTypeId = 1;
';
EXEC sys.sp_executesql @SQL,
N'@iIsTrusted BIT, @iPostId INT',
@iIsTrusted = @IsTrusted,
@iPostId = @PostId;
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.
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.
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.
If we clear the cache and run this for 22656 first, the plan runs relatively quickly, and looks a little different.
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!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
Related Posts
- SQL Server 2022 CTP 2.1 Improvements To Parameter Sensitive Plan Optimization
- SQL Server 2022 Parameter Sensitive Plan Optimization: Does Not Care To Fix Your Local Variable Problems
- SQL Server 2022 Parameter Sensitive Plan Optimization: Sometimes There’s Nothing To Fix
- Defeating Parameter Sniffing With Dynamic SQL In SQL Server
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
Ooh, I haven’t seen that before. I don’t write any C# code myself. Thanks!
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.
That’s pretty spiffy. Someone might shoulda oughtta blog about it.
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.
Yep — you know that and I know that — but in developer heavy shops where everything is code first first first, these things are solved in “clever” ways.
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.