如何优化SQL Server与ASP.NET MVC中慢过滤页面的性能？

我们有一张存储客户信息的表，包含近300万行数据、80多列，其中有大量nvarchar(max)类型字段。在ASP.NET MVC Web应用的某个页面中，用户可以对该表进行多条件筛选、排序及分页操作，支持对20个不同列进行筛选和排序，部分操作需要关联其他表执行子查询。所有筛选/排序查询由Entity Framework 6自动生成，仅查询主键（PK）列，后续再通过主键单独获取其他所需列。

多数筛选组合性能良好，但部分组合速度极慢（耗时超30秒，其他组合仅需不到1秒）。核心原因是SQL Server针对某些筛选组合选择了不合适的执行计划，没有使用正确的索引——比如本该用WHERE子句对应的索引，却用了ORDER BY子句的索引，导致执行索引扫描和键查找。

慢查询示例1 #

SELECT 
[Extent1].[Id] AS [Id]
FROM [dbo].[Clients] AS [Extent1]
WHERE ([Extent1].[Paid] <> 1) AND ((([Extent1].[ManagerId] IN (N'd3cbce41-1db3-4b6d-8a14-1d5704090b3d')) AND ([Extent1].[ManagerId] IS NOT NULL)) 
OR ( EXISTS (SELECT 
    1 AS [C1]
    FROM [dbo].[Partners] AS [Extent2]
    WHERE ([Extent1].[Id] = [Extent2].[ClientId]) AND ([Extent2].[PartnerId] IN (N'd3cbce41-1db3-4b6d-8a14-1d5704090b3d'))
)))
ORDER BY row_number() OVER (ORDER BY [Extent1].[Date] ASC)
OFFSET 600 ROWS FETCH NEXT 100 ROWS ONLY 

该查询本该使用ManagerId索引做索引查找，却用了Date索引执行扫描。

慢查询示例2 #

exec sp_executesql N'SELECT 
[Project11].[Id] AS [Id]
FROM ( SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Date1] AS [Date1]
    FROM [dbo].[Clients] AS [Extent1]
    WHERE ([Extent1].[Paid] <> 1) 
    AND ((([Extent1].[ManagerId] IN (N''73cdda41-0086-4104-a4c2-4dd59c306s14'', N''d9dfb477-6f56-47de-b73e-0a419f575a00'')) AND ([Extent1].[ManagerId] IS NOT NULL)) 
        OR (([Extent1].[SecondManagerId] IN (N''73cdda41-0086-4104-a4c2-4dd59c306s14'', N''d9dfb477-6f56-47de-b73e-0a419f575a00'')) AND ([Extent1].[SecondManagerId] IS NOT NULL))) 
    AND ( EXISTS (SELECT 
        1 AS [C1]
        FROM  (SELECT 
            [Extent2].[Code] AS [Code]
            FROM [dbo].[Codes] AS [Extent2]
            WHERE [Extent1].[Id] = [Extent2].[ClientId]
        INTERSECT
            SELECT 
            [UnionAll2].[C1] AS [C1]
            FROM  (SELECT 
                N''9.7'' AS [C1]
                FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
            UNION ALL
                SELECT 
                N''9.8'' AS [C1]
                FROM  ( SELECT 1 AS X ) AS [SingleRowTable2]
            UNION ALL
                SELECT 
                N''9.9'' AS [C1]
                FROM  ( SELECT 1 AS X ) AS [SingleRowTable3]) AS [UnionAll2]) AS [Intersect1]
    )) 
    AND ( EXISTS (SELECT 
        1 AS [C1]
        FROM  (SELECT 
            1 AS [C1]
            FROM  ( SELECT 1 AS X ) AS [SingleRowTable4]
        UNION ALL
            SELECT 
            3 AS [C1]
            FROM  ( SELECT 1 AS X ) AS [SingleRowTable5]) AS [UnionAll3]
        WHERE [Extent1].[Stage] = [UnionAll3].[C1]
    )) 
    AND ((convert (datetime2, convert(varchar(255), [Extent1].[Date1], 102) ,  102)) >= (convert (datetime2, convert(varchar(255), @p__linq__0, 102) ,  102))) 
    AND ((convert (datetime2, convert(varchar(255), [Extent1].[Date1], 102) ,  102)) <= (convert (datetime2, convert(varchar(255), @p__linq__1, 102) ,  102))) 
    AND ( EXISTS (SELECT 
        1 AS [C1]
        FROM [dbo].[Table2] AS [Extent3]
        WHERE ([Extent1].[Id] = [Extent3].[ClientId]) 
            AND (0 = [Extent3].[Status]) 
            AND ((convert (datetime2, convert(varchar(255), [Extent3].[Date2], 102) ,  102)) >= (convert (datetime2, convert(varchar(255), @p__linq__2, 102) ,  102))) 
            AND ((convert (datetime2, convert(varchar(255), [Extent3].[Date2], 102) ,  102)) <= (convert (datetime2, convert(varchar(255), @p__linq__3, 102) ,  102)))
    ))
)  AS [Project11]
ORDER BY row_number() OVER (ORDER BY [Project11].[Date1] ASC)
OFFSET 1000 ROWS FETCH NEXT 100 ROWS ONLY ',N'@p__linq__0 datetime2(7),@p__linq__1 datetime2(7),@p__linq__2 datetime2(7),@p__linq__3 datetime2(7)',@p__linq__0='2022-12-16 00:00:00',@p__linq__1='2022-12-18 00:00:00',@p__linq__2='2022-08-01 00:00:00',@p__linq__3='2022-12-15 00:00:00'

该查询包含6种筛选条件，涉及多表关联子查询。

我们已经为筛选涉及的几乎所有属性（除nvarchar(max)外）创建了单独索引，但问题依旧。显然无法为所有筛选/排序组合创建覆盖索引，目前考虑创建包含所有相关属性的非聚集列存储索引（NCCI），但顾虑是该表更新频繁（每日约8000-15000行）。

疑问 #

1. 还有哪些方法可以优化该页面所有筛选组合的性能？比如外部工具、库或其他方案。
2. 使用NCCI的方案是否可行？

优化方案建议 #

1. 修复EF生成查询的不合理转换 #

第二个示例中的日期转换convert (datetime2, convert(varchar(255), [Extent1].[Date1], 102) , 102)会让Date1上的索引失效——函数包裹列值后，SQL Server无法使用索引。应改成直接比较日期：在EF中调整Linq逻辑，用Date1.Date >= startDate.Date替代字符串转换方式，让索引能够生效。

2. 引导SQL Server选择正确执行计划 #

• 查询提示：针对确定的慢查询，在EF中通过WithHint方法指定索引（比如WithIndex("IX_Clients_ManagerId")），强制SQL Server使用指定索引。缺点是需要针对不同筛选组合维护提示，较为繁琐。
• 执行计划强制：捕获慢查询的最优执行计划，通过sp_create_plan_guide创建计划指南，强制SQL Server复用该计划，适合重复出现的慢查询组合。

3. 重构查询逻辑，减少子查询复杂度 #

EF自动生成的冗余子查询（比如多层UNION ALL、INTERSECT）可简化：把IN条件直接写成WHERE ... IN (...)，避免生成单表UNION ALL；多对多关联的筛选用JOIN替代EXISTS，减少嵌套层级，降低查询复杂度。

4. NCCI可行性分析 #

每日8000-15000行更新在NCCI的可接受范围内。SQL Server对列存储索引的更新优化已较为成熟（如delta store处理、批量更新支持），只要不是高频单条更新，对写入性能影响有限。NCCI的优势是能高效支持任意列的筛选和排序，非常适合这种多维度查询场景。可先创建包含常用筛选、排序列的NCCI（排除nvarchar(max)），测试查询和更新性能。

5. 分页逻辑优化 #

当前ROW_NUMBER() + OFFSET/FETCH的分页方式，在偏移量大时（如OFFSET 1000）性能会显著下降。可改用键集分页：以上次分页的最后一条记录的排序键（如Date1+Id）作为条件，避免扫描前面的行：

SELECT Id, Date1
FROM Clients
WHERE Date1 > @LastDate OR (Date1 = @LastDate AND Id > @LastId)
ORDER BY Date1, Id
FETCH NEXT 100 ROWS ONLY

EF中可通过Linq实现该逻辑，大幅提升大偏移量的分页性能。

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