加速SQL Server和ASP.NET MVC中缓慢的页面过滤？

b0zn9rqh 于 2022-12-26 发布在 SQL Server

关注(0)|答案(5)|浏览(151)

bounty将在16小时后过期。回答此问题可获得+300声望奖励。kseen希望引起更多人关注此问题。

假设我们有一个包含客户信息的表，该表有近300万行和80多列，其中包含大量的nvarchar(max)。
在我们的ASP .NETMVCWeb应用程序中，我们有一个页面，允许用户使用各种过滤功能在此表中导航，排序和分页。可以对20个不同的列执行筛选和排序，其中一些列需要对其他表进行子查询。所有筛选/排序查询也是由EntityFramework 6自动生成的，并且只选择主键（PK）列（稍后通过PK，我们根据不同请求提取其他所需列）。
我们的过滤器UI如下所示：

大多数滤波组合工作良好，但其中一些速度非常慢（超过30秒，而其他过滤器不到1秒）。出现此行为的原因之一是，对于某些过滤器组合，MS SQL选择了具有不适当索引的错误执行计划（不是在WHERE子句上选择索引，而是为ORDER BY子句获取索引，并使用键查找执行索引扫描）。例如，此自动生成的查询使用Date索引并执行索引扫描，而不是使用ManagerId索引并执行索引查找。

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

再举一个例子，选择了6个不同过滤选项的查询可能如下所示：

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'

对于某些筛选选项，查询必须为其他表创建子查询，因为它们之间存在多对多关系。
我们已经为几乎每个属性创建了单独的索引（nvarchar(max)除外），但这还不够。显然，我们无法为每个筛选/排序组合创建覆盖索引，因此我们目前正在考虑创建非聚集列存储索引（NCCI），这将涵盖所有这些属性组合。但有一件事让我们担心这个想法是，该表被更新得相当频繁（每天大约8000 - 15000行）。
有没有其他的方法可以让我们的页面对于每种过滤器组合都更快？比如一些外部工具、库或方法。或者使用NCCI来处理我们的表格会不会很好？

sql-server

来源：https://stackoverflow.com/questions/74821427/speed-up-slow-filtering-page-in-sql-server-and-asp-net-mvc

5条答案

按热度按时间

k75qkfdt1#

简介

要回答你的问题应该对db进行分析，下面我会给你一些我的考虑。
首先你的句子：

首先

MS SQL选择了索引不适当的错误执行计划（它没有选择WHERE子句上的索引，而是为ORDER BY子句获取索引，并使用键查找执行索引扫描）。
MSSQL根据它所拥有的信息选择最佳计划。如果你有错误的索引统计信息，错误的索引碎片，等等，MSSQL不能做太多。
第二
我们目前正在考虑创建非聚集列存储索引（NCCI）
来自Microsoft文档：

行存储索引在查找数据的查询、搜索特定值或查询小范围的值时性能最佳。请将行存储索引用于事务性工作量，因为它们往往主要需要表查找，而不是表扫描。*
列存储索引可为扫描大量数据（尤其是大型表上的数据）的分析查询提供很高的性能增益。在数据仓库和分析工作负载（尤其是事实表）上使用列存储索引，因为它们往往需要完整的表扫描而不是表查找。*

从你的问题，你有行级过滤，而不是聚合（其中列存储是最好的），在我看来，创建一个列存储是毫无意义的。

注意事项

从你的问题：
但是有一件事让我们对这个想法感到担忧，那就是表被〉更新得相当频繁（大约每天8000 - 15000行）。
根据这句话，下面的一些解决方案可能不适用。

实体框架

你知道如何编写SQL查询吗？如果答案是肯定的，那么就使用它，用Dapper替换EF（可能用SqlKata或类似的fw）并编写自己的查询。
EF尽了最大的努力来编写查询，但它永远不会像你一样了解数据库。

索引

- 始终**检查查询计划。

不要添加太多的索引可能会适得其反，对于每一行添加到表中的索引都必须更新。
如果您有一列可以用来"分区"表，则可以考虑table partitioning
仔细检查您的statistics.查询是否有"好"索引和"坏"计划检查统计信息。

只读数据库

只读数据库的查询操作非常快（它们不必检查锁定）。
以Database snapshot为例。
考虑只读Replica

缓存

您可以使用.NET MVC caching
您可以使用Redis或类似的软件进行缓存。

赞(0）回复(0）举报 2022-12-26

bvuwiixz2#

最好的方法是使用带有查询字段参数的存储过程（SP）。这些参数还可以用于：

控制输出，例如排序顺序、顶部等。
使用used-defined data types提交多个值

主要优点是：

您或您的db-dev/dba可以完全控制所执行的查询
在SP中自由使用子查询
无需更新ASP.NETMVCWeb应用程序即可完成维护和优化

在我遇到的代码中，我看到了这样的内容：

CREATE PROCEDURE [yourSchema].[GetYourData]
   @DateTimeFrom DATETIME = NULL,
   @DateTimeTill DATETIME = NULL,
   @SortAscending AS BIT = 0, -- control the output
   @ApplicationName VARCHAR(64) = NULL,
   @MachineName VARCHAR(256) = NULL,
   @UserName VARCHAR(256) = NULL,
   @MaxRowCount INT = 1000, -- control the output
   @KeyValueTable AS CustomTableType.KeyValueTable READONLY, -- submit a table to filter
   @SeverityTable AS CustomTableType.SeverityTable READONLY  -- submit a table to filter
AS
/* etc... */

您可以从EF调用此SP，但也可以使用@Max之前建议的Dapper。

赞(0）回复(0）举报 2022-12-26

7nbnzgx93#

300万行应该不是什么大问题，所以肯定出了问题。我会集中注意力在以下几件事上：
1.不使用自动生成的查询
1.移除强制类型转换-这些强制类型转换是不可索引的

ORDERBYrow_number（）OVER（ORDERBY [Project 11].[Date 1] ASC）--为什么不按日期1排序呢？Row_number通常需要完全具体化，这有点不必要
1.检查你的索引，也许有些字段可以添加为INCLUDE，特别是如果你总是搜索X，但往往也搜索Y。
1.尝试OPTION（RECOMPILE）强制重新生成计划
1.在SET STATISTICS IO ON的情况下运行查询。是否对意外的表执行了许多“操作”，可能是哪里出了问题
1.看看是否可以创建一个可索引视图，其中包含一些预先创建的连接，这可能会简化WHERE条件
1.分析没有使用索引的组合。用实际值而不是动态参数化的SQL来测试这些组合，结果是否“更好”？这可能导致#9。
1.删除参数化查询，只输入“原始”值。我知道人们经常建议参数化查询更好，但由于您有如此多样的查询，选择性可能会略有不同，所以参数化可能会导致糟糕的查询计划。当然，您必须净化数据，因为它来自网络。注意，不要尝试这个，除非你绝对不能让它快速工作，否则，实际上有适当的能力来消毒的东西，或者你可能会泄漏数据/结束了dailywtf

赞(0）回复(0）举报 2022-12-26

qvk1mo1f4#

你可以用EXCEPT、INTERSECT或UNION组合的一系列轻查询来替换所有这些东西。
这些轻查询将具有如下结构：

SELECT Id
FROM   dbo.clients
WHERE  ... --> only one member predicate always "sargable" (avoid NOT, <> and OR...)

以查询1为例：

(
SELECT Id
FROM   dbo.Clients 
WHERE  ManagerId IN (N'd3cbce41-1db3-4b6d-8a14-1d5704090b3d')

INTERSECT

SELECT Id
FROM   dbo.Clients
WHERE  ManagerId IS NOT NULL 

UNION ALL

SELECT  Id
FROM    dbo.Clients AS C
WHERE EXISTS (SELECT 1
              FROM dbo.Partners AS P
              WHERE C.Id = P.ClientId AND P.PartnerId IN (N'd3cbce41-1db3-4b6d-8a14-1d5704090b3d'))
)

EXCEPT

SELECT Id
FROM   dbo.Clients 
WHERE  Paid = 1;

为什么？
使用非优化 predicate 时，基数估计可能非常糟糕。因此替换为：

〈〉by =使用基于EXCEPT集合的操作
基于UNION集合的运算执行OR

那么优化器的所有统计信息都将是准确的，最终的执行计划也将足够好...

赞(0）回复(0）举报 2022-12-26

fumotvh35#

在你的例子中你有很多的子查询，子查询肯定会减慢进程。尝试使用连接，这将大大减少过滤时间。

赞(0）回复(0）举报 2022-12-26

我来回答

加速SQL Server和ASP.NET MVC中缓慢的页面过滤？

5条答案

简介

首先

注意事项

实体框架

索引

只读数据库

缓存

相关问题

热门标签

最新问答