Perf regression (V9): Generated SQL from ternary expression on nullable property causes slow execution on SQL Server

[mycroes]

Bug description

While testing a complex query in templated form to find the most performant query I noticed EF Core 8 would produce a very suboptimal query with a ternary on a null-condition, while the not-null ternary was behaving fine. This resulted in the not-null ternary executing in about ~6s, while the null-conditioned ternary would run in ~33s.

Upgrading the codebase to EF Core 9 (in order to file this bug report) I noticed the not-null conditioned ternary had regressed to the same performance as the null conditioned ternary due to changes in the generated SQL.

Your code

I'm using the following predicate as part of the template:

r => Template.SupplyOrderId != null
    ? r.SourcePallet.SupplyOrderId == Template.SupplyOrderId
    : r.SourcePallet.Id == Template.ActivePalletId

In EF Core 8, this resulted in the following SQL (subquery only):

SELECT [w].[NetWeight], 1 AS [Key]
FROM [WasteRegistrations] AS [w]
INNER JOIN [SourcePallet] AS [s3] ON [w].[SourcePalletId] = [s3].[Id]
WHERE CASE
    WHEN [s].[SupplyOrderId] IS NOT NULL THEN CASE
        WHEN [s3].[SupplyOrderId] = [s].[SupplyOrderId] AND [s3].[SupplyOrderId] IS NOT NULL THEN CAST(1 AS bit)
        ELSE CAST(0 AS bit)
    END
    ELSE CASE
        WHEN [s3].[Id] = [p3].[ActivePalletId] AND [p3].[ActivePalletId] IS NOT NULL THEN CAST(1 AS bit)
        ELSE CAST(0 AS bit)
    END
END = CAST(1 AS bit)

Here p3.ActivePalletId is int?, s.SupplyOrderId is int? as well.

in EF Core 9, the subquery is as follows:

SELECT [w].[NetWeight], 1 AS [Key]
FROM [WasteRegistrations] AS [w]
INNER JOIN [SourcePallet] AS [s4] ON [w].[SourcePalletId] = [s4].[Id]
WHERE CASE
    WHEN [s].[SupplyOrderId] IS NOT NULL THEN ~CAST([s4].[SupplyOrderId] ^ [s].[SupplyOrderId] AS bit)
    ELSE ~CAST([s4].[Id] ^ [p3].[ActivePalletId] AS bit)
END = CAST(1 AS bit)

The EF8 query results in a filter on the SourcePallet table, which in turn requires a Clustered Index Seek on the WasteRegistrations table to find the accompanying waste registrations. With EF9 however, the subquery causes a CLustered Index Scan, because (my assumption) it can no longer digest what source it should use to match the s4.SupplyOrderId part of the query. I also have my doubts how this behavior will affect filtered indexes, but I think it renders them moot due to the way the query is now built.

Stack traces

NA.

Verbose output

NA.

EF Core version

9.0.6

Database provider

Microsoft.EntityFrameworkCore.SqlServer

Target framework

.NET 8.0

Operating system

Windows 11

IDE

Visual Studio 2022 17.4

[roji]

@mycroes thanks for reporting this. What you're reporting seems quite odd, since the the main change here is in the WHERE clause, which doesn't reference WasteRegistrations at all; the only reference I'm seeing to that table is in the INNER JOIN, where except for the alias change things looks unchanged:

-- BEFORE:
INNER JOIN [SourcePallet] AS [s3] ON [w].[SourcePalletId] = [s3].[Id]

-- AFTER:
INNER JOIN [SourcePallet] AS [s4] ON [w].[SourcePalletId] = [s4].[Id]

However, given that you've only posted very partial details (we don't even have the full SQL), it's difficult to know exactly what's going on. Would it be possible to try to put together a minimal, runnable repro we can run and see the query plan regression between the two EF versions?

The change in EF 9.0 to use bitwise NOT was done in #34213, /cc @ranma42.

[mycroes]

Hi @roji,

I'm not sure how easy it will be to reproduce this in a smaller example, but for starters I'll include the two full SQL queries and io,time statistics.

I fully agree with your statement about the WasteRegistrations reference, to the extent that I'm doubting my observations. Maybe I overlooked another part, but let's figure this out together.

-- EF Core 8
SELECT (
    SELECT TOP(1) [p4].[ProcessingLineType]
    FROM [ProcessingLines] AS [p4]
    LEFT JOIN [ProcessingLineConfigurations] AS [p5] ON [p4].[ActiveConfigurationId] = [p5].[Id]
    WHERE EXISTS (
        SELECT 1
        FROM [ProcessingConfigurationFlows] AS [p6]
        WHERE [p5].[Id] IS NOT NULL AND [p5].[Id] = [p6].[ConfigurationId] AND [p6].[FlowId] = [p1].[FlowId])), [p3].[ConsumedCount], (
    SELECT CAST(COALESCE(SUM([s2].[NetWeight]), 0.0E0) AS real)
    FROM [CurrentSourcePalletWeights] AS [c]
    INNER JOIN [SourcePallet] AS [s1] ON [c].[SourcePalletId] = [s1].[Id]
    INNER JOIN [SourcePalletWeightRegistrations] AS [s2] ON [c].[SourcePalletWeightRegistrationId] = [s2].[Id]
    WHERE CASE
        WHEN [s].[SupplyOrderId] IS NOT NULL THEN CASE
            WHEN [s1].[SupplyOrderId] = [s].[SupplyOrderId] AND [s1].[SupplyOrderId] IS NOT NULL THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
        END
        ELSE CASE
            WHEN [s1].[Id] = [p3].[ActivePalletId] AND [p3].[ActivePalletId] IS NOT NULL THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
        END
    END = CAST(1 AS bit)), [t0].[NumberOfItems], [t0].[UnloadNumberOfItems], [t0].[c], [t1].[NetWeight], [t1].[NumberOfRegistrations], [t1].[c]
FROM [ProcessingLines] AS [p]
LEFT JOIN [ProcessingLineConfigurations] AS [p0] ON [p].[ActiveConfigurationId] = [p0].[Id]
INNER JOIN [ProcessingConfigurationFlows] AS [p1] ON [p0].[Id] = [p1].[ConfigurationId]
INNER JOIN [ProductSupplyFlows] AS [p2] ON [p1].[FlowId] = [p2].[Id]
INNER JOIN [PalletProcessingQueues] AS [p3] ON [p2].[ProcessingQueueId] = [p3].[Id]
LEFT JOIN [SourcePallet] AS [s] ON [p3].[ActivePalletId] = [s].[Id]
OUTER APPLY (
    SELECT TOP(1) COALESCE(SUM([t].[NumberOfItems]), 0) AS [NumberOfItems], COALESCE(SUM(CASE
        WHEN [t].[SourcePalletStatus] = 5 THEN [t].[NumberOfItems]
    END), 0) AS [UnloadNumberOfItems], 1 AS [c]
    FROM (
        SELECT [s0].[NumberOfItems], [s0].[SourcePalletStatus], 1 AS [Key]
        FROM [SourcePallet] AS [s0]
        WHERE CASE
            WHEN [s].[SupplyOrderId] IS NOT NULL THEN CASE
                WHEN [s0].[SupplyOrderId] = [s].[SupplyOrderId] AND [s0].[SupplyOrderId] IS NOT NULL THEN CAST(1 AS bit)
                ELSE CAST(0 AS bit)
            END
            ELSE CASE
                WHEN [s0].[Id] = [p3].[ActivePalletId] AND [p3].[ActivePalletId] IS NOT NULL THEN CAST(1 AS bit)
                ELSE CAST(0 AS bit)
            END
        END = CAST(1 AS bit)
    ) AS [t]
    GROUP BY [t].[Key]
) AS [t0]
OUTER APPLY (
    SELECT TOP(1) CAST(COALESCE(SUM([t2].[NetWeight]), 0.0E0) AS real) AS [NetWeight], COUNT(*) AS [NumberOfRegistrations], 1 AS [c]
    FROM (
        SELECT [w].[NetWeight], 1 AS [Key]
        FROM [WasteRegistrations] AS [w]
        INNER JOIN [SourcePallet] AS [s3] ON [w].[SourcePalletId] = [s3].[Id]
        WHERE CASE
            WHEN [s].[SupplyOrderId] IS NOT NULL THEN CASE
                WHEN [s3].[SupplyOrderId] = [s].[SupplyOrderId] AND [s3].[SupplyOrderId] IS NOT NULL THEN CAST(1 AS bit)
                ELSE CAST(0 AS bit)
            END
            ELSE CASE
                WHEN [s3].[Id] = [p3].[ActivePalletId] AND [p3].[ActivePalletId] IS NOT NULL THEN CAST(1 AS bit)
                ELSE CAST(0 AS bit)
            END
        END = CAST(1 AS bit)
    ) AS [t2]
    GROUP BY [t2].[Key]
) AS [t1]

-- EF Core 9
SELECT (
    SELECT TOP(1) [p4].[ProcessingLineType]
    FROM [ProcessingLines] AS [p4]
    LEFT JOIN [ProcessingLineConfigurations] AS [p5] ON [p4].[ActiveConfigurationId] = [p5].[Id]
    WHERE EXISTS (
        SELECT 1
        FROM [ProcessingConfigurationFlows] AS [p6]
        WHERE [p5].[Id] IS NOT NULL AND [p5].[Id] = [p6].[ConfigurationId] AND [p6].[FlowId] = [p1].[FlowId])), [p3].[ConsumedCount], (
    SELECT CAST(COALESCE(SUM([s3].[NetWeight]), 0.0E0) AS real)
    FROM [CurrentSourcePalletWeights] AS [c]
    INNER JOIN [SourcePallet] AS [s2] ON [c].[SourcePalletId] = [s2].[Id]
    INNER JOIN [SourcePalletWeightRegistrations] AS [s3] ON [c].[SourcePalletWeightRegistrationId] = [s3].[Id]
    WHERE CASE
        WHEN [s].[SupplyOrderId] IS NOT NULL THEN ~CAST([s2].[SupplyOrderId] ^ [s].[SupplyOrderId] AS bit)
        ELSE ~CAST([s2].[Id] ^ [p3].[ActivePalletId] AS bit)
    END = CAST(1 AS bit)), [s6].[NumberOfItems], [s6].[UnloadNumberOfItems], [s6].[c], [s7].[NetWeight], [s7].[NumberOfRegistrations], [s7].[c]
FROM [ProcessingLines] AS [p]
LEFT JOIN [ProcessingLineConfigurations] AS [p0] ON [p].[ActiveConfigurationId] = [p0].[Id]
INNER JOIN [ProcessingConfigurationFlows] AS [p1] ON [p0].[Id] = [p1].[ConfigurationId]
INNER JOIN [ProductSupplyFlows] AS [p2] ON [p1].[FlowId] = [p2].[Id]
INNER JOIN [PalletProcessingQueues] AS [p3] ON [p2].[ProcessingQueueId] = [p3].[Id]
LEFT JOIN [SourcePallet] AS [s] ON [p3].[ActivePalletId] = [s].[Id]
OUTER APPLY (
    SELECT TOP(1) COALESCE(SUM([s1].[NumberOfItems]), 0) AS [NumberOfItems], COALESCE(SUM(CASE
        WHEN [s1].[SourcePalletStatus] = 5 THEN [s1].[NumberOfItems]
    END), 0) AS [UnloadNumberOfItems], 1 AS [c]
    FROM (
        SELECT [s0].[NumberOfItems], [s0].[SourcePalletStatus], 1 AS [Key]
        FROM [SourcePallet] AS [s0]
        WHERE CASE
            WHEN [s].[SupplyOrderId] IS NOT NULL THEN ~CAST([s0].[SupplyOrderId] ^ [s].[SupplyOrderId] AS bit)
            ELSE ~CAST([s0].[Id] ^ [p3].[ActivePalletId] AS bit)
        END = CAST(1 AS bit)
    ) AS [s1]
    GROUP BY [s1].[Key]
) AS [s6]
OUTER APPLY (
    SELECT TOP(1) CAST(COALESCE(SUM([s5].[NetWeight]), 0.0E0) AS real) AS [NetWeight], COUNT(*) AS [NumberOfRegistrations], 1 AS [c]
    FROM (
        SELECT [w].[NetWeight], 1 AS [Key]
        FROM [WasteRegistrations] AS [w]
        INNER JOIN [SourcePallet] AS [s4] ON [w].[SourcePalletId] = [s4].[Id]
        WHERE CASE
            WHEN [s].[SupplyOrderId] IS NOT NULL THEN ~CAST([s4].[SupplyOrderId] ^ [s].[SupplyOrderId] AS bit)
            ELSE ~CAST([s4].[Id] ^ [p3].[ActivePalletId] AS bit)
        END = CAST(1 AS bit)
    ) AS [s5]
    GROUP BY [s5].[Key]
) AS [s7]

time,io statistics for EF8 query:

Started executing query at Line 1
SQL Server parse and compile time:
CPU time = 46 ms, elapsed time = 733 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.
(30 rows affected)
Table 'Worktable'. Scan count 227, logical reads 842, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'SourcePalletWeightRegistrations'. Scan count 0, logical reads 87, physical reads 5, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'CurrentSourcePalletWeights'. Scan count 0, logical reads 87, physical reads 5, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'SourcePallet'. Scan count 72, logical reads 223915, physical reads 3, page server reads 0, read-ahead reads 3606, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'ProcessingLineConfigurations'. Scan count 0, logical reads 56, physical reads 1, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'ProcessingLines'. Scan count 37, logical reads 130, physical reads 2, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'ProcessingConfigurationFlows'. Scan count 52, logical reads 104, physical reads 2, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'WasteRegistrations'. Scan count 29, logical reads 120, physical reads 14, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'PalletProcessingQueues'. Scan count 0, logical reads 60, physical reads 1, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'ProductSupplyFlows'. Scan count 0, logical reads 60, physical reads 1, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
(1 row affected)
SQL Server Execution Times:
CPU time = 5672 ms, elapsed time = 5804 ms.
Total execution time: 00:00:06.584

time,io statistics for EF9 query:

Started executing query at Line 1
Warning: Null value is eliminated by an aggregate or other SET operation.
(30 rows affected)
Table 'Worktable'. Scan count 227, logical reads 842, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'CurrentSourcePalletWeights'. Scan count 23, logical reads 28773, physical reads 1, page server reads 0, read-ahead reads 2289, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'SourcePalletWeightRegistrations'. Scan count 23, logical reads 95243, physical reads 2, page server reads 0, read-ahead reads 5530, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'SourcePallet'. Scan count 72, logical reads 666601, physical reads 6, page server reads 0, read-ahead reads 45274, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'ProcessingLineConfigurations'. Scan count 0, logical reads 56, physical reads 1, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'ProcessingLines'. Scan count 37, logical reads 130, physical reads 2, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'ProcessingConfigurationFlows'. Scan count 52, logical reads 104, physical reads 2, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'WasteRegistrations'. Scan count 23, logical reads 2806713, physical reads 392, page server reads 0, read-ahead reads 178690, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'PalletProcessingQueues'. Scan count 0, logical reads 60, physical reads 1, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'ProductSupplyFlows'. Scan count 0, logical reads 60, physical reads 1, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
(1 row affected)
SQL Server Execution Times:
CPU time = 77391 ms, elapsed time = 79145 ms.
Total execution time: 00:01:19.270

As you can see from the above it doesn't only affect WasteRegistrations, SourcePallet for one is also affected which might also be aversely affecting the rest of the execution.

[ranma42]

Apparently SQLServer is quite good at looking through (nested) CASE expressions, so the attempt to avoid casting back and forth between boolean expressions and BIT values seems to make things significantly worse.

It should be fairly straightforward to keep the simpler expressions in non-predicate contexts and use the CASE-based ones in predicates.

@roji note that the PR that most likely caused the regression is #34168 (i.e. the one that translates some == and != as (negated) XOR).

Another option would be to revert the change and always translate using CASE, but that would re-introduce bugs around NULL (/UNKNOWN) boolean results (OTOH this was buggy before EFCore 9 and is not yet fully solved; currently tracked in #34001).

[mycroes]

Apparently SQLServer is quite good at looking through (nested) CASE expressions, so the attempt to avoid casting back and forth between boolean expressions and BIT values seems to make things significantly worse.

Well... I have 5 templates leading to the same result set. They're all equivalent for my data model, but I'm not 100% confident SQL Server should be able to deduct that if ActivePallet has a SupplyOrder and I'm getting all SourcePallets for the SupplyOrder, that it can match that SourcePallet by Id as well. Sounds complicated (but isn't really), but I can make that deduction and as such can use the C# expression r => r.SourcePallet.Id == Template,ActivePalletId || (Template.SupplyOrderId != null && r.SourcePallet.SupplyOrderId == Template.SupplyOrderId). What also works is r => r.SourcePallet.Id == Template.ActivePalletId || r.SourcePallet.SupplyOrderId == Template.ActivePalletId ?? -1. Both will yield the same result set, but these will actually complete in 2ms and 4ms respectively. So all in all, I have 5 templates to do this match, which currently execute in 2ms, 4ms, ~5.5s, ~33s and 40+s (same dataset, same hardware, same advantage of priming the cache with a non-timed identical query first). So while SQL Server honestly is amazingly smart, there's still room for improvement.

Also, this might look awkward, but if the pallet has no SupplyOrder I still want the details for the single pallet, which works equally well with this query.

It should be fairly straightforward to keep the simpler expressions in non-predicate contexts and use the CASE-based ones in predicates.

Sounds like a good plan. I was honestly amazed this slipped through and didn't get noticed yet.

Thanks for looking into this issue as well, I understand I'm not really delivering this on a silver plate with the huge queries and lack of runnable code.

[roji]

It should be fairly straightforward to keep the simpler expressions in non-predicate contexts and use the CASE-based ones in predicates.

Sounds like a good plan. I was honestly amazed this slipped through and didn't get noticed yet.

I'm not sure varying our translation strategy based on whether they're in predicate or non-predicate is a good idea - that feels like it would introduce yet more complexity (we don't usually take this kind of context into account).

@mycroes thanks for the full SQLs, but ideally we'd still have an actual minimal repro. It's not that I don't believe what you're reporting, but we usually want to have an actual repro and getting to the bottom of it before making a non-trivial SQL translation change such as the one being ddiscusse here.

[ranma42]

This snippet reproduces the two different translations:

// @nuget: Microsoft.EntityFrameworkCore.SqlServer -Version 9.0.0
using System;
using System.Linq;
using Microsoft.EntityFrameworkCore;

using var db = new BloggingContext();

var query = db.Blogs
    .Where(x => x.Id > 12 ? x.A == 1 : x.A == 2)
    .ToQueryString();

Console.WriteLine(query);

public class BloggingContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseSqlServer();
}

public class Blog
{
    public int Id { get; set; }
    public int A { get; set; }
}

Specifically, on EFCore 8 it outputs:

SELECT [b].[Id], [b].[A]
FROM [Blogs] AS [b]
WHERE CASE
    WHEN [b].[Id] > 12 THEN CASE
        WHEN [b].[A] = 1 THEN CAST(1 AS bit)
        ELSE CAST(0 AS bit)
    END
    ELSE CASE
        WHEN [b].[A] = 2 THEN CAST(1 AS bit)
        ELSE CAST(0 AS bit)
    END
END = CAST(1 AS bit)

while on EFCore 9 it outputs:

SELECT [b].[Id], [b].[A]
FROM [Blogs] AS [b]
WHERE CASE
    WHEN [b].[Id] > 12 THEN ~CAST([b].[A] ^ 1 AS bit)
    ELSE ~CAST([b].[A] ^ 2 AS bit)
END = CAST(1 AS bit)

Unfortunately this does not reproduce the most interesting part of the issue, i.e. the change in the execution plan used by the DB.
I am afraid that in order to reproduce that, a more complex setting is needed (so far I have been unable to reproduce it exactly).