EF Core 8 query causing intensive performance drop due to the breaking "contians" change

[stevenxi]

File a bug

Technically it's a query optimization issue, but was not expecting such dramatic performance drop when using the new change:

[Column] IN (
    SELECT [t1].[value]
    FROM OPENJSON(@__typeIds_2) WITH ([value] int ''$'') AS [t1]
)

intead of

[Column] IN ( 0, 1, 2, 3)

Include your code

internal static class EfCoreTest
{
	[Table("MeterGroup")]
	public class Group
	{
		[Key]
		public int GroupId { get; protected set; }

		public int TenantId { get; set; }

		public int? ParentGroupId { get; internal set; }
		public virtual Group ParentGroup { get; internal set; }

		public int GroupTypeId { get; set; }

		public string GroupName { get; set; }

	}

	[Table("MeterGroupRouteIndex")]
	public class GroupRouteIndex
	{
		[Key, Column(Order = 0), DatabaseGenerated(DatabaseGeneratedOption.None)]
		public int ParentGroupId { get; set; }
		public virtual Group ParentGroup { get; set; }

		[Key, Column(Order = 1), DatabaseGenerated(DatabaseGeneratedOption.None)]
		public int ChildGroupId { get; set; }
		public virtual Group ChildGroup { get; set; }

	}

	public class TestContext : CustomDataContextBase
	{ 
		public TestContext(DbContextOptions<TestContext> options) 
			: base(options)
		{
		}

		protected override void OnModelCreating(ModelBuilder modelBuilder)
		{
			base.OnModelCreating(modelBuilder);

			modelBuilder.Entity<GroupRouteIndex>().HasKey(x => new { x.ParentGroupId, x.ChildGroupId });
			modelBuilder.Entity<GroupRouteIndex>().HasIndex(x => x.ChildGroupId);
			modelBuilder.Entity<GroupRouteIndex>().HasIndex(x => x.ParentGroupId);


			modelBuilder.Entity<Group>().HasKey(x => x.GroupId).IsClustered(false);

			modelBuilder.Entity<Group>().HasIndex(x => x.TenantId).IsClustered(true);

			modelBuilder.Entity<Group>().HasIndex(x => new { x.TenantId, x.ParentGroupId, x.GroupTypeId, x.GroupName }).IsUnique(true).HasFilter(null);
			modelBuilder.Entity<Group>().HasIndex(x => new { x.ParentGroupId, x.GroupTypeId, x.GroupName });
		}

		public DbSet<GroupRouteIndex> GroupRouteIndexes { get; set; }
		public DbSet<Group> Groups { get; set; }
	}



	public static async Task RunTest()
	{

		var optionBuilder = new DbContextOptionsBuilder<TestContext>();
		optionBuilder.UseSqlServer("abcdefg"
			,o => o.UseCompatibilityLevel(120) //Comment this line in test case 2 below
			);


		using var context = new TestContext(optionBuilder.Options);

		var id = 160178;
		var tenantIds = new[] { 2, 5, 6, 7, 12, 1, -1 };
		var typeIds = new[] { 40, 47, 61, 62, 63, 64, 65, 66, 71, 72, 73, 74, 75, 76, 77, 89, 90, 91, 92, 93, 94, 136, 137, 138, 160, 163, 213, 215, 223, 230, 231, 232, 233, 238, 239, 240, 241 };

		var parentGroups = await context.GroupRouteIndexes.Where(r => r.ChildGroupId == id & r.ParentGroupId != id)
		  .Join(context.Groups.Where(x => tenantIds.Contains(x.TenantId)), gt => gt.ParentGroupId, g => g.GroupId, (gt, g) => g)
		  .Where(g => typeIds.Contains(g.GroupTypeId))
		  .Select(g => new { g.TenantId, g.GroupId, g.GroupName, g.GroupTypeId })
		  .ToListAsync();



	}
}

1. When without set UseCompatibilityLevel to 120, it generates query as below:

exec sp_executesql N'SELECT [t0].[TenantId], [t0].[GroupId], [t0].[GroupName], [t0].[GroupTypeId]
FROM [MeterGroupRouteIndex] AS [m]
INNER JOIN (
    SELECT [m0].[GroupId], [m0].[GroupName], [m0].[GroupTypeId], [m0].[TenantId]
    FROM [MeterGroup] AS [m0]
    WHERE [m0].[TenantId] IN (
        SELECT [t].[value]
        FROM OPENJSON(@__tenantIds_1) WITH ([value] int ''$'') AS [t]
    )
) AS [t0] ON [m].[ParentGroupId] = [t0].[GroupId]
WHERE CASE
    WHEN [m].[ChildGroupId] = @__id_0 THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END & CASE
    WHEN [m].[ParentGroupId] <> @__id_0 THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END = CAST(1 AS bit) AND [t0].[GroupTypeId] IN (
    SELECT [t1].[value]
    FROM OPENJSON(@__typeIds_2) WITH ([value] int ''$'') AS [t1]
)',N'@__tenantIds_1 nvarchar(4000),@__id_0 int,@__typeIds_2 nvarchar(4000)',@__tenantIds_1=N'[2,5,6,7,12,1,-1]',@__id_0=160178,@__typeIds_2=N'[40,47,61,62,63,64,65,66,71,72,73,74,75,76,77,89,90,91,92,93,94,136,137,138,160,163,213,215,223,230,231,232,233,238,239,240,241]'

This query took nearly 30 seconds to execute, with exeution plan as below:

2. When with set UseCompatibilityLevel to 120, it generates query as below:

exec sp_executesql N'SELECT [t].[TenantId], [t].[GroupId], [t].[GroupName], [t].[GroupTypeId]
FROM [MeterGroupRouteIndex] AS [m]
INNER JOIN (
    SELECT [m0].[GroupId], [m0].[GroupName], [m0].[GroupTypeId], [m0].[TenantId]
    FROM [MeterGroup] AS [m0]
    WHERE [m0].[TenantId] IN (2, 5, 6, 7, 12, 1, -1)
) AS [t] ON [m].[ParentGroupId] = [t].[GroupId]
WHERE CASE
    WHEN [m].[ChildGroupId] = @__id_0 THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END & CASE
    WHEN [m].[ParentGroupId] <> @__id_0 THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END = CAST(1 AS bit) AND [t].[GroupTypeId] IN (40, 47, 61, 62, 63, 64, 65, 66, 71, 72, 73, 74, 75, 76, 77, 89, 90, 91, 92, 93, 94, 136, 137, 138, 160, 163, 213, 215, 223, 230, 231, 232, 233, 238, 239, 240, 241)',N'@__id_0 int',@__id_0=160178

This query took less than 1 seconds to execute, with exeution plan as below:

Include stack traces

NA

Include verbose output

NA

Include provider and version information

EF Core version: 8.0.5
Database provider: 16.0.4085.2
Target framework: NET 8.0
Operating system: Windows 11
IDE: Visual Studio 2022 17.10.3

[ajcvickers]

Duplicate of #32394