如何使用EF core提高SQL读取性能

Question

查询时不时会抛出超时异常。 SqlCommand.TimeOut 默认设置为 30 秒。

Microsoft.Data.SqlClient.SqlException (0x80131904)：执行超时 已到期。超时时间在完成之前已过 操作或服务器没有响应。

System.ComponentModel.Win32Exception (258): 等待操作计时 出来。

在 Microsoft.Data.SqlClient.SqlCommand.<>c.b__169_0（任务1 结果）在 System.Threading.Tasks.ContinuationResultTaskFromResultTask2.InnerInvoke()
在 System.Threading.ExecutionContext.RunInternal（ExecutionContextexecutionContext，ContextCallback 回调，对象状态）
--- 先前位置的堆栈跟踪结束 ---

在 System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, 线程 threadPoolThread)
--- 先前位置的堆栈跟踪结束 ---
在Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync（RelationalCommandParameterObject参数对象，CancellationToken取消令牌）
在Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync（RelationalCommandParameterObject参数对象，CancellationToken取消令牌）
在 Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable1.AsyncEnumerator.InitializeReaderAsync（DbContext _，布尔结果，CancellationToken CancelationToken） 在 Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState，TResult]（TState 状态，Func4操作， Func4 verifySucceeded、CancellationToken CancellationToken) 在 Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable1.AsyncEnumerator.MoveNextAsync() ClientConnectionId:2daf9373-898b-481d-a7ef-8688c1e1efed 错误 数量：-2，状态：0，类别：11

根据 docs SqlCommandTimeout 是

此属性是累积超时（对于所有网络数据包） 在方法调用期间读取）所有网络读取 在命令执行或结果处理期间。超时可以 返回第一行后仍然发生，并且不包括用户 处理时间，仅网络读取时间。

例如，如果读取需要两个网络，则超时时间为 30 秒 数据包，那么它有 30 秒的时间来读取这两个网络数据包。如果你 再次调用 Read ，它将再有 30 秒的时间来读取任何数据 它需要。

我已经多次验证了索引。 order 表确实有数百万 (6M+) 条记录。接收此异常的代码如下所示。

    public async Task<Order> GetByID(int orderID)
    {
        var start = DateTime.UtcNow;
        var wo = await _dbContext.Orders
            .Where(x => x.OrderID == orderID)                
            .Select(x => new WorkOrder()
            {
                OrderID = x.OrderID,
                ItemID = x.ItemID,
                ItemName = x.ItemName,
                OrderStatusID = x.OrderStatusID,
                OrderStatus = x.OrderStatus,
                Template = new Template()
                {
                    TemplateModel = x.Template.TemplateModel
                },
                Responses = x.Responses.Select(r => new Response()
                {
                    ResponseID = r.ResponseID,
                    ResponseStatusID = r.ResponseStatusID,
                    ResponseStatus = r.ResponseStatus,
                    //some more property assignments
                }).ToList()
            })
            .SingleOrDefaultAsync()
            .ConfigureAwait(false);

        var end = DateTime.UtcNow;
        
        _logger.LogInformation($"EF-{end.Substract(start}");

        return wo;
    }

这是单次执行的两个日志。 （请注意，这不是抛出超时异常的实例。）

EF 日志

Executed DbCommand ("1"ms) [Parameters=["@__orderID_0='?' (DbType = Int32)"], CommandType='Text', CommandTimeout='30']"  ""SELECT [t].[OrderID], 

我的自定义日志显示总时间

EF-00:00:00.2109107

。在此特定实例中，执行 SQL 只需要 1 毫秒，完成 EF 调用只需要 21 毫秒。所以从日志中我注意到执行SQL的实际时间非常少，并且读取结果和构建结果集需要更长的时间。

问题

1. 我还可以检查和尝试哪些其他内容？ （Web Server 和 SQL Server 是两个不同的 EC2 服务器）

2. 有没有办法提高 SQL 读取和结果集构建性能？

3. EF 可以生成带有 READONLY 子句的查询吗？

更新1
我还尝试过 EF Core 的 .AsSplitQuery() 选项，但性能没有或较低。此加载集合是单独的数据库查询。

Answer 1

首先要检查的是捕获发送到服务器的确切 SQL。我的猜测是，您遇到问题的查询将返回结果列表。使用探查器运行该查询并查看所花费的时间、数据库读取数量等。查看执行计划并查看索引利用率。使用 ORM 进行高效开发与通过视图和存储过程进行的传统数据访问有些不同。通常，您需要构建 Linq 查询和投影，并针对数据库运行它们以检查索引利用率等内容，同时考虑类似现实世界的使用模式，以优先考虑从索引中获得的最佳“效果”，并识别较繁重的操作就像您可能希望采用排队机制之类的报告来防止一次性启动太多昂贵的查询来“接触”大量数据，从而触发锁延迟和死锁。

您的特定问题的解决方案很可能通过查看正在运行的查询来揭示。

围绕查询性能需要考虑和研究的一些一般事项：

当使用 EF 和投影来查看模型/DTO 时，第一步是将视图模型降至所需的最少数据。如果您可以从该结果中扩展一个选择，请考虑使用异步 Ajax 调用来按需加载该扩展。我要注意的警告标志是在加载数据集合时看到返回的那些项目包含子数据集合。对于搜索结果等内容，应该不惜一切代价避免这种情况。用户可能检查其中一些相关集合，但这对于可能永远不会被看到的东西来说是巨大的开销。

如果返回的数据可能超过合理的屏幕范围（即 25-100 行），请始终确保对结果进行分页。很多时候，我在开发过程中看到样本集很小的测试，却发现一旦系统在生产中投入使用，它就会停止运行，因为用户可以通过搜索返回整个数据源的 25%。还要在所有搜索条件中采取措施，以确保搜索不会以无用的条件开始。 （即搜索字符串的最小长度）