EF Core 8 现在使用 WHERE IN 和 OPENJSON 的组合在 where 语句中内联值,而不是以前的 WHERE IN(...)。此更改已在文档中注明,并说明了原因……
EF Core 8 现在使用 , 的组合在 where 语句中内联值 WHERE IN
and OPENJSON
而不是以前的 WHERE IN(...)
.
文档中记录了 这一变化 ,并说明了原因如下:
此处内联值的方式使得 SQL 注入攻击不存在任何可能性。下文描述的使用 JSON 的更改完全是为了性能,与安全性无关。
不幸的是, OPENJSON
我们的 2017 SQL Server 实例的性能很差。
以下查询由 EF Core 8 生成, 运行 1.8 秒 400,000 次读取 :
DECLARE @__scheduleTagIds_0 nvarchar(4000) = N'[5835,5970,6563,6564,6565,6645,6835,6850,7034,7127]';
SELECT [s].[ScheduleTagId]
,[s].[MustStartProdBy]
FROM [ScheduleTagMustStartBy] AS [s]
WHERE [s].[ScheduleTagId] IN (
SELECT [s0].[value]
FROM OPENJSON(@__scheduleTagIds_0) WITH ([value] int '$') AS [s0]
)
如果我重构查询以使用标准 WHERE IN(...)
,则执行时间将下降到 120ms 和 29,000 次读取 :
SELECT [s].[ScheduleTagId]
,[s].[MustStartProdBy]
FROM [ScheduleTagMustStartBy] AS [s]
WHERE [s].[ScheduleTagId] IN (5835,5970,6563,6564,6565,6645,6835,6850,7034,7127)
我的应用程序中有数百个查询 .Where(x => [collection].Contains(x.Id))
,我非常担心在选择查询中看到的性能下降。
问题
我该怎么做才能缓解这个问题?我愿意选择 EF 或 SQL Server 端的选项(但不想更改数据库的兼容级别)。
根据文档,防止使用 OPENJSON 的唯一方法是将 DB 兼容级别更改为 120(低于 SQL Server 2016)。这不是理想的解决方案。还有其他方法可以限制/防止使用 OPENJSON 吗?
您不必将 DB 兼容级别更改为 120。您可以调用 UseCompatibilityLevel(120)
来告诉 EF 生成旧式 SQL,而不管兼容级别实际设置为何。
还可以选择避免这种全局设置,而只将有问题的特定查询常量化 。但我认为这种 EF.Constant
方法尚未进入正式版本,因此您需要使用每日构建。
SQL Server 上是否有一些配置可以提高 OPENJSON 的性能?从分析器数据可以清楚地看出,在使用 OPENJSON 时,SQL Server 无法正确使用索引。
它当然可以使用索引, OPENJSON
但可能选择不使用。
当你传递常量时它可以确定
由于 OPENJSON
无法判断任何情况,因此只能依靠猜测。这可能会导致不同的(更糟糕的)计划。