Performance Degradation in MS SQL Database Queries After Upgrading to EF Core 8: OPENJSON - Contains

OlegoO · September 5, 2024, 3:44pm

Hello Virto Commerce Community,

After upgrading to EF Core 8 (EFC8), we’ve encountered several severe performance regressions where previously millisecond queries started timing out. This issue is particularly evident in our Catalog, Order and Cart database queries.

Analysis

The root cause appears to be related to the new translation of the Contains method to OPENJSON. The cardinality estimator assumes that OPENJSON will return huge amount of rows, which can dissuade SQL Server from seeking the index if the column being filtered is not very selective. This also affects the use of filtered indexes, which require constants.

In our case, many queries use .Where(x => col.Contains(x.SomeId)), where col typically contains just one element 99% of the time, with occasional cases of 0 or 2 elements.

Orignal EFCore8 Query

exec sp_executesql N'SELECT [c].[Id]
FROM [CustomerOrder] AS [c]
WHERE [c].[IsPrototype] = CAST(0 AS bit) AND [c].[Status] IN (
    SELECT [c0].[value]
    FROM OPENJSON(@__criteria_Statuses_0) WITH ([value] nvarchar(64) ''$'') AS [c0]
)
ORDER BY [c].[CreatedDate] DESC, [c].[Id]
OFFSET @__p_1 ROWS FETCH NEXT @__p_2 ROWS ONLY',N'@__criteria_Statuses_0 nvarchar(4000),@__p_1 int,@__p_2 int',@__criteria_Statuses_0=N'["Completed"]',@__p_1=0,@__p_2=10

During the investigation, I found GitHub Issue #32394

Below you can find the summary and my solution.

Workaround

To mitigate this issue, we had to apply the CompatLevel 120 hack, but this is not ideal as it limits our ability to use newer ToJson features.

            builder.UseSqlServer(connectionString, db => db.
                .UseCompatibilityLevel(120));

EFCore8 Query

exec sp_executesql N'SELECT [c].[Id]
FROM [CustomerOrder] AS [c]
WHERE [c].[IsPrototype] = CAST(0 AS bit) AND ([c].[Status] = N''Completed1'' OR [c].[Status] = N''Completed2'' OR [c].[Status] = N''Completed'3')
ORDER BY [c].[CreatedDate] DESC, [c].[Id]
OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY',N'@__p_0 int,@__p_1 int',@__p_0=0,@__p_1=10

Execution Plan

Future Solutions

There are two main approaches to address this issue without relying on the CompatLevel 120 workaround:

Wait for EF9/EF10 (scheduled for release in November):
- Pros: Minimal work, low risk.
- Cons: Long wait, many places to opt-in, potential for missing many query plan reuses.
Opt-out of parameterization per query:
- Pros: Immediate optimizations, fewer places to opt-out, good query plan reuse.
- Cons: Risky, no heuristic for which queries to opt-in or out, potential need for testing in production.

We haven’t measured the benefits of parameterization yet, so we’ll likely avoid risk and choose the first option. We may later identify frequently run WHERE-IN queries and try opting in for them.

Virto Team Workaround

In the meantime, we’ve implemented a custom workaround using C# code to transform Contains into multiple OR conditions, generating SQL queries without OPENJSON.

protected virtual Expression<Func<TEntity, bool>> ContainsIn<TEntity, TProperty>(
    Expression<Func<TEntity, TProperty>> propertyExpression, IList<TProperty> values)
{
    var parameter = propertyExpression.Parameters[0];
    var property = propertyExpression.Body;
    var outerPredicate = PredicateBuilder.False<TEntity>();

    foreach (var value in values)
    {
        Expression<Func<TProperty>> valueSelector = () => value;
        var param = valueSelector.Body;
        var equalsExpression = Expression.Equal(property, param);
        outerPredicate = outerPredicate.Or(Expression.Lambda<Func<TEntity, bool>>(equalsExpression, parameter));
    }

    return outerPredicate;
}

Sample Usage:

query = query.Where(ContainsIn<CustomerOrderEntity, string>(x => x.Status, criteria.Statuses));

Generated SQL Code by EF Core 8:

exec sp_executesql N'SELECT [c].[Id]
FROM [CustomerOrder] AS [c]
WHERE [c].[IsPrototype] = CAST(0 AS bit) AND ([c].[Status] = @__value_0 OR [c].[Status] = @__value_1)
ORDER BY [c].[CreatedDate] DESC, [c].[Id]
OFFSET @__p_2 ROWS FETCH NEXT @__p_3 ROWS ONLY',N'@__value_0 nvarchar(64),@__value_1 nvarchar(64),@__p_2 int,@__p_3 int',@__value_0=N'Completed',@__value_1=N'Completed3',@__p_2=0,@__p_3=10