The Entity Framework: WHERE IN vs. OPENJSON Problem

I ain’t got nothin’ but a hater’s heart, so I gotta take this performance issue with Entity Framework (9.x) to a whole new level of sarcasm. This query is a doozy, y’all, but it’s not just any simple WHERE clause. It’s selecting rows based on a indexed string column, and when executed, it seems like the index is entirely unutilized, making the query grind like a millstone.
I know the index is supposed to speed things up, but this query is taking ages to complete. It’s like the SQL Server gods have a vendetta against us, and they’re doing everything they can to make our lives miserable. It’s like they’re saying, “Hey, you’re indexing on a string column, but you’re still running this slow query? You’re not doing enough for us!”
But you know what? I’m not going to take it lying down. I’m gonna figure out a way to make this thing work. And that means using the magic of OPENJSON in Entity Framework 8+.
First up, let’s take a look at the original query. This query generates a WHERE IN clause, which is what we want. It tells the database to search for rows where the name

I’ve been investigating a performance issue with a slow query that is generated by Entity Framework (9.x) running on a v15 SQL Server database. The query is pretty simple. It’s just selecting rows based on one or more values that are in an indexed string column. But when executed, it doesn’t appear that the index is used at all – making the query run very slowly.

OPENJSON in Entity Framework 8+

This is an example of some Entity Framework code that would generate a WHERE IN clause, prior to the changes that were made in EF 8:

var names = new[] { "Blog1", "Blog2" };

var blogs = await context.Blogs
    .Where(b => names.Contains(b.Name))
    .ToArrayAsync();

And the generated SQL would look like this:

  SELECT [b].[Id], [b].[Name]
  FROM [Blogs] AS [b]
  WHERE [b].[Name] IN (N'Blog1', N'Blog2')

Starting with EF8, however, it generates something more like this:

DECLARE @__names_0 nvarchar(MAX) = N'["Blog1", "Blog2"]';  -- JSON array of IDs
SELECT [b].[Id], [b].[Name]
FROM [Blogs] AS [b]
WHERE [b].[Name] IN (
    SELECT [n].[value]
    FROM OPENJSON(@__names_0) WITH ([value] nvarchar(max) '$') AS [n]
)

Unfortunately for me, according to the Breaking changes in EF Core 8 page, regarding the change to using OPENJSON:

…while the new SQL is more efficient for most cases, it can be dramatically less efficient in a minority of cases, even causing query timeouts in some cases.

It seems that my application is in that minority of cases.

For the particular database/table/index combination I’m using, the OPENJSON version of the SELECT takes around 5 seconds to provide results. But changing that to a WHERE IN provides results in only a couple hundred milliseconds. Exactly what I would expect given the index on the column.

Mitigations

The Mitigations section of the “Breaking Changes” page provides a few ways to tell Entity Framework to use the WHERE IN style, instead of OPENJSON.

I experimented with disabling the OPENJSON syntax entirely, which is possible with EF 9 like this:

  protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder.UseSqlServer("", o => o.TranslateParameterizedCollectionsToConstants())

That change fixes the particular query I’ve been having problems with. However, interestingly, it caused some other queries to run considerably slower. But there’s another technique you can use instead, on a case-by-case basis:

var blogs = await context.Blogs
  .Where(b => EF.Constant(names).Contains(b.Name))
  .ToArrayAsync();

The use of EF.Constant() tells Entity Framework to use a WHERE IN clause instead of the OPENJSON style for that particular part of the WHERE clause.

By using a combination of WHERE IN and OPENJSON, I’ve been able to optimize my queries – greatly improving the performance of the existing queries without having to make any changes to my existing database structure.

The post The Entity Framework: WHERE IN vs. OPENJSON Problem appeared first on Atomic Spin.