8wDlpd.png
8wDFp9.png
8wDEOx.png
8wDMfH.png
8wDKte.png

Entity Framework Core 8 Where IN 与 Where IN OPENJSON

Nate 2月前

52 0

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 端的选项(但不想更改数据库的兼容级别)。

帖子版权声明 1、本帖标题:Entity Framework Core 8 Where IN 与 Where IN OPENJSON
    本站网址:http://xjnalaquan.com/
2、本网站的资源部分来源于网络,如有侵权,请联系站长进行删除处理。
3、会员发帖仅代表会员个人观点,并不代表本站赞同其观点和对其真实性负责。
4、本站一律禁止以任何方式发布或转载任何违法的相关信息,访客发现请向站长举报
5、站长邮箱:yeweds@126.com 除非注明,本帖由Nate在本站《linq》版块原创发布, 转载请注明出处!
最新回复 (0)
  • 我也遇到过这个问题。似乎使用 TOP 可以将列表中包含少量项目的查询与包含大量项目的查询区分开来,假设其中包含约 50 个项目可能非常糟糕。

    例如,可以使用 DbCommandInterceptor 修改 SQL。示例实现可能如下所示:

    /// <summary>Contains query optimize interceptor</summary>
    /// <remarks>
    /// A problem with contains queries translated into OPENJSON are that SQL server cannot determine how many rows the query will yield.
    /// We will therefore divide queries into groups of different sizes to provide SQL server with a clear max count to allow for better
    /// query paths to be taken
    /// </remarks>
    public partial class SqlServerContainsQueryOptimizeInterceptor : DbCommandInterceptor {
        private static readonly int[] sizeGroups = [1, 2, 5, 10, 20, 50];
    
        /// <inheritdoc/>
        public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result) {
            ManipulateCommand(command);
            return result;
        }
    
        /// <inheritdoc/>
        public override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result, CancellationToken cancellationToken = default) {
            ManipulateCommand(command);
            return new ValueTask<InterceptionResult<DbDataReader>>(result);
        }
    
        /// <inheritdoc/>
        public override InterceptionResult<object> ScalarExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<object> result) {
            ManipulateCommand(command);
            return result;
        }
    
        /// <inheritdoc/>
        public override ValueTask<InterceptionResult<object>> ScalarExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<object> result, CancellationToken cancellationToken = default) {
            ManipulateCommand(command);
            return new ValueTask<InterceptionResult<object>>(result);
        }
    
        /// <summary>Handle the actual command manipulation</summary>
        private static void ManipulateCommand(DbCommand command) {
            var sql = command.CommandText;
            var newSql = openJsonMatcher.Replace(sql, m => {
                try {
                    // Read the parameter value to determine the size of the array
                    var paramName = m.Groups[1].Value;
                    var paramValue = command.Parameters[paramName].Value?.ToString();
                    if (paramValue == null) return m.Value;
                    var items = JsonNode.Parse(paramValue)!.AsArray();
    
                    // Replace the select statement with a top statement to help the query planner
                    var group = sizeGroups.FirstOrDefault(x => x >= items.Count);
                    return group != 0
                        ? m.Value.Replace("SELECT", "SELECT TOP " + group)
                        : m.Value;
                }
                catch {
                    return m.Value;
                }
            });
    
            if (newSql != sql) {
                command.CommandText = newSql;
            }
        }
    
        private static readonly Regex openJsonMatcher = GetOpenJsonRegEx();
        [GeneratedRegex(@"IN\s+\(\s*SELECT\s+[^\s]+\s+FROM\s+OPENJSON\(([^)]+)\)(?: WITH \([^)]+\))?[^)]+\)")]
        private static partial Regex GetOpenJsonRegEx();
    }
    

    然后可以在设置 SQL 服务器时将拦截器添加到 opts 构建器中,例如

    optionsBuilder.UseSgsSql(connectionStr);
    optionsBuilder.AddInterceptors(new SqlServerContainsQueryOptimizeInterceptor());
    

    在我看来,这似乎是一个合理的解决方案,但我以前当然也犯过错。任何反馈都值得感激。

返回
作者最近主题: