多表关联后对1:N关联表日期列排序时查询极慢的优化求助

一、索引针对性优化（解决DTU耗尽核心问题） #

当前索引覆盖不全，导致大量回表和键查找，直接拉高IO和CPU消耗，调整如下：

1. OrderNode表核心覆盖索引
   查询中多次按OrderId、Deleted过滤，同时需要大量字段，创建包含所有所需列的索引，彻底避免回表：

CREATE NONCLUSTERED INDEX IX_OrderNode_OrderId_Filtered ON OrderNode (OrderId, Deleted)
INCLUDE (ArrivalPlan, IsMain, IsLast, OrderIndex, IsFull, Reference, ShowDescription, Description, CountryCode, Zip, City, Name1, LicensePlateTruck, LicensePlateTrailer);

替换现有冗余的OrderNode索引，避免索引过多维护开销。

2. 修正并扩展Order表索引
   原索引拼写错误（IX_Order_StausId应为StatusId），同时扩展覆盖列包含所有查询用到的Order字段：

DROP INDEX IF EXISTS IX_Order_StausId ON [Order];
CREATE NONCLUSTERED INDEX IX_Order_StatusId_Deleted ON [Order] (StatusId, Deleted)
INCLUDE (OrderId, Version, DriverId, ContainerLength, ContainerNumber, ExportImportType, Info, ContainerTara, ContainerContentWeight, ContainerWeight, CustomsInfo, IsContentDanger, PlannedStart, PlannedEnd, TruckAppId, ContainerTypeId, CustomerContactName, CustomerContactPhone, CustomerContactEmail, CustomerOperatorRef, ContainerContent, ContainerContentCountry, CustomsNumber, MaerskWorkingOrder, SealType, SealNumber, CustomerOrderRef);

3. OrderContract表索引优化
   针对多次按OrderId关联并过滤OrderIndex、StatusId的场景，创建覆盖索引：

CREATE NONCLUSTERED INDEX IX_OrderContract_OrderId_Filtered ON OrderContract (OrderId)
INCLUDE (Id, OrderIndex, StatusId, AssignedTo, AssignedFrom);

二、查询逻辑重构（减少冗余计算） #

EF Core生成的SQL存在冗余子查询和关联，手动重构降低执行开销：

1. 去掉OrderNode冗余子查询
   原查询中t、t0、t1都是从OrderNode过滤Deleted=0的临时表，改成直接三次关联OrderNode并分别过滤IsMain=1、OrderIndex=1、IsLast=1，让数据库直接利用索引匹配：

INNER JOIN OrderNode AS t ON o.OrderId = t.OrderId AND t.Deleted = 0 AND t.IsMain = 1
INNER JOIN OrderNode AS t0 ON o.OrderId = t0.OrderId AND t0.Deleted = 0 AND t0.OrderIndex = 1
INNER JOIN OrderNode AS t1 ON o.OrderId = t1.OrderId AND t1.Deleted = 0 AND t1.IsLast = 1

2. 将标量子查询改为APPLY关联
   原SELECT中的三个标量子查询会对每一行重复执行，改用OUTER APPLY批量处理，减少重复IO：

OUTER APPLY (
    SELECT TOP(1) LicensePlateTruck 
    FROM OrderNode o6 
    WHERE o6.Deleted = 0 AND o.OrderId = o6.OrderId AND o6.LicensePlateTruck IS NOT NULL
    ORDER BY o6.OrderIndex DESC
) AS TruckPlate
OUTER APPLY (
    SELECT TOP(1) LicensePlateTrailer 
    FROM OrderNode o7 
    WHERE o7.Deleted = 0 AND o.OrderId = o7.OrderId AND o7.LicensePlateTrailer IS NOT NULL
    ORDER BY o7.OrderIndex DESC
) AS TrailerPlate
OUTER APPLY (
    SELECT COUNT(*) AS NodeCount
    FROM OrderNode o8 
    WHERE o8.Deleted = 0 AND o.OrderId = o8.OrderId
) AS NodeCount

之后在SELECT中直接引用TruckPlate.LicensePlateTruck、TrailerPlate.LicensePlateTrailer、NodeCount.NodeCount即可。

3. 简化Translation表JOIN条件
   原条件N'tag_status' + CAST(o.StatusId AS nvarchar(100)) = t4.Tag的字符串拼接会导致索引失效，改成固定长度转换并给Translation表加索引：

CREATE NONCLUSTERED INDEX IX_Translation_Tag_LanguageId ON Translation (Tag, LanguageId)
INCLUDE (Text);

同时将CAST改为CAST(o.StatusId AS nvarchar(10))，减少不必要的字符串长度。

三、EF Core层面调优 #

1. 禁用客户端评估
   在DbContext配置中添加以下代码，确保所有过滤、排序逻辑都在数据库端执行，避免客户端拉取海量数据：

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.ConfigureWarnings(warnings => warnings.Throw(RelationalEventId.QueryClientEvaluationWarning));
}

2. 避免自动生成冗余SQL
   如果EF Core持续生成低效SQL，直接使用原生SQL执行优化后的查询：

var orders = context.Orders.FromSqlRaw(@"优化后的原生SQL").ToList();

3. 分页逻辑优化
   当前分页使用OFFSET 0 FETCH NEXT 100 ROWS，确保排序字段t.ArrivalPlan被包含在OrderNode的索引中（已在第一步的索引中覆盖），让数据库直接通过索引排序，避免内存排序开销。

四、Azure SQL环境优化（不增加DTU） #

1. 更新统计信息
   过期的统计信息会导致糟糕的执行计划，更新核心表的统计信息：

UPDATE STATISTICS [Order] WITH FULLSCAN;
UPDATE STATISTICS [OrderNode] WITH FULLSCAN;
UPDATE STATISTICS [OrderContract] WITH FULLSCAN;

2. 利用Query Store分析等待类型
   通过Azure Portal的Query Store查看查询的等待类型：

• 若为IO等待：说明索引优化仍有空间；
• 若为CPU等待：进一步简化SELECT中的CASE表达式逻辑。

3. 路由到只读副本
   如果该查询是只读业务，将查询路由到Azure SQL的只读副本，分担主库的DTU压力。

内容的提问来源于stack exchange，提问作者ICantSeeSharp