Coming in Entity Framework 9: Better Query Parameterization
Hallelujah. With current versions of Entity Framework, when developers add a mix of parameters and specific values to their query like this:
|
1 2 3 4 5 |
async Task<List<Post>> GetPosts(int id) => await context.Posts .Where( e => e.Title == ".NET Blog" && e.Id == id) .ToListAsync(); |
See how part of the filter is hard-coded (“.NET Blog”) while the other part of the filter is dynamically generated, an ID the user is looking for? That causes Entity Framework to generate a query that is partially parameterized:
|
1 2 3 4 5 |
info: 2/5/2024 15:43:13.789 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) Executed DbCommand (1ms) [Parameters=[@__id_0='1'], CommandType='Text', CommandTimeout='30'] SELECT [p].[Id], [p].[Archived], [p].[AuthorId], [p].[BlogId], [p].[Content], [p].[Discriminator], [p].[PublishedOn], [p].[Title], [p].[PromoText], [p].[Metadata] FROM [Posts] AS [p] WHERE [p].[Title] = N'.NET Blog' AND [p].[Id] = @__id_0 |
This is the worst of both worlds for SQL Server. If the query was fully parameterized, it’d get plan reuse. If the query wasn’t parameterized at all, we could turn on Forced Parameterization and get plan reuse. However, Forced Parameterization won’t do anything for the above query because SQL Server looks at it and says, “That query’s already parameterized – see, it has a parameter for @__id_0 right there! I’ll just skip it.”
In Microsoft’s example above, ‘.NET Blog’ is a hard-coded string, but the situation is much worse when that is dynamically generated. For every variation of the parameter, SQL Server sees a “new” query coming in. End result: increased CPU to compile “new” query plans, unpredictable plans, plan cache bloat, problems with monitoring tools and Query Store, and more.
Good news! The What’s New in Entity Framework 9 rundown shows a new EF.Parameter method to force parameterization:
|
1 2 3 4 5 |
async Task<List<Post>> GetPostsForceParameter(int id) => await context.Posts .Where( e => e.Title == EF.Parameter(".NET Blog") && e.Id == id) .ToListAsync(); |
Which uses a parameter now instead of a hard-coded string:
|
1 2 3 4 5 |
info: 2/5/2024 15:43:13.803 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) Executed DbCommand (1ms) [Parameters=[@__p_0='.NET Blog' (Size = 4000), @__id_1='1'], CommandType='Text', CommandTimeout='30'] SELECT [p].[Id], [p].[Archived], [p].[AuthorId], [p].[BlogId], [p].[Content], [p].[Discriminator], [p].[PublishedOn], [p].[Title], [p].[PromoText], [p].[Metadata] FROM [Posts] AS [p] WHERE [p].[Title] = @__p_0 AND [p].[Id] = @__id_1 |
Yay! Lower CPU for plan compilation, more reused plans, less memory consumed by redundant plans, and better monitoring tools.
It is a bummer that we have to wait until an estimated November 2024 for EF9 (according to that same What’s New doc), and that developers will have to touch code in order to fix it. I can’t really complain about that, though, because I’m just happy that Microsoft is adding it. EF’s query generation keeps gradually getting better, and that’s awesome.
22 Comments. Leave new
I wish EF would do this automagically.
Disclaimer: I am not a heavy EF user, but would it not be better for that to be an option that can be enabled globally to force all hardcoded values to be parameterized with a local option to turn it off rather than having to make a code change in each instance to gain the benefit of the feature? Would there be any downside to that (after initial regression testing and verification, of course).
Long time lurker, but always enjoy your content!
Ben – would I like a pony? Yes. Did Microsoft give me a pony? No. 😉
Ha! The key thing here, then, is that I am not losing my mind – or at least this one is not symptomatic.
I’ve not actually tried it but surely this would work?
async Task<List> GetPostsForceParameter(int id, string title = “.NET Blog”)
=> await context.Posts
.Where(
e => e.Title == title && e.Id == id)
.ToListAsync();
I’m not a developer, so your best bet would be to try it yourself, but my guess is that Microsoft’s solution avoids declaring extra variables.
As it’s using a default value (= “.NET Blog”) it means you don’t need to change anything that calls the method.
I don’t use EF, prefer Dapper, just thought I’d comment as it means you wouldn’t need EF 9 to get the same behaviour assuming EF parameterises string parameters the same way it does for int parameters.
Reading this, I’m mildly terrified that this sort of auto-interpretation will default to Unicode strings rather than the actual column data type. That seems to be the way most of these things go.
Well, at least it looks like it uses the max size for the column rather than the string length… Size = 4000
Wondering if you could make your own
FakeEFParamatizer(string mytext) { return mytext;) and just use that until EF9 comes out. Or would the compiler optimizer just magic it away.
I was just wondering if there is any open issue for this problem on https://github.com/dotnet/efcore/issue?
And you decided that the most efficient way to find out would be to post a comment on a blog post? Interesting technique. 😉
I don’t know enough of EF, but what’s the problem of the first example with a fixed filter? (if all you do is filter on ‘.NET Blog’
WHERE [p].[Title] = N’.NET Blog’ AND [p].[Id] = @__id_0
WHERE [p].[Title] = N’.NET Blog’ AND [p].[Id] = @__id_0
Results in the same query
if title is dynamic, why don’t you make it a parameter?
WHERE [p].[Title] = @_title_0 AND [p].[Id] = @__id_0
WHERE [p].[Title] = @_title_0 AND [p].[Id] = @__id_0
That’s totally a valid question. However, as a consultant, I often find that the question of “Why don’t you do it the right way instead?” doesn’t seem to be productive.
WINK WINK
[…] Coming in Entity Framework 9: Better Query Parameterization (Brent Ozar) […]
are not very many queries a mix from hardcoded and parameter? Stuff as flag_active = 1 or source_table = ‘tbl1’ combined with e.g. user_id = @id, where it would make sense to read just those rows that fits the hardcoded stuff?
And ideally there is a filtered index to provide this functionallity, which would no longer be used, if you replace the filter by a flag_active = @flag_active. Same for cardinality estimation / statistics (filtered or unfiltered).
There may be cases, where it helps to fully parameterize a query, but more often it will hurt more than it helps.
And to be honest: when your server is so stressed, that it struggles over a few additional recompiles / plans it will struggle on worser query plans too.
The filtered index & query execution time isn’t the problem – it’s the plan cache bloat that’s a problem. (We discuss this in the plan caching module of the Mastering Server Tuning class, and I give a few examples of there where it’s killed servers.)
SQL Server should fix Forced Parameterization and add parameters even if the query already has some.
I agree wholeheartedly there, too.
Found a new one today, somehow EF is generating
(@p9 int)DELETE FROM [myschema].[mytable] OUTPUT 1 WHERE [Id] = @p9
(@p5 int)DELETE FROM [myschema].[mytable] OUTPUT 1 WHERE [Id] = @p5
(@p6 int)DELETE FROM [myschema].[mytable] OUTPUT 1 WHERE [Id] = @p6
(@p7 int)DELETE FROM [myschema].[mytable] OUTPUT 1 WHERE [Id] = @p7
causing multiple plans in query store (due new parameter name?)
Yeah, that’s fairly common – it’s when the app is asking for several single-row deletes. (sigh)
Query parameterization is a real problem for our applications as we are firing SELECTs every 200ms during user input of a filter within our Entity Framework frontends. You could argue that this behavior is more like a D-DOS attack on the SQL server but it’s very convenient for the user seeing records filtered during typing.
However, after I could not find any documentation or GitHub issue about parameterization I fired up VS an got things clarified by the EF team.
EF SQL translations get parameterized if the expression contains a variable. They are not parameterized if the expression contains a constant string like e.Title == “.NET Blog”. Thus, you could easily parameterize the GetPosts() method by introducing a string variable like string s = “.NET Blog” and changing the linq expression to e.Title == s.
Details you can find in https://github.com/dotnet/efcore/issues/34000