performance - SQL Azure - 如果包含具有大尺寸 nvarchar 的列，则使用 row_number() 的查询执行速度会很慢

Question

我有以下查询(由 Entity Framework 使用标准分页生成。这是内部查询，我添加了 TOP 438 部分):

SELECT TOP 438 [Extent1].[Id] AS [Id], 
                  [Extent1].[MemberType] AS [MemberType], 
                  [Extent1].[FullName] AS [FullName], 
                  [Extent1].[Image] AS [Image], 
                  row_number() OVER (ORDER BY [Extent1].[FullName] ASC) AS [row_number]
        FROM [dbo].[ShowMembers] AS [Extent1]
        WHERE 3 =  CAST( [Extent1].[MemberType] AS int)

ShowMembers 表大约有 11K 行，但只有 438 行，且 MemberType == 3。“Image”列的类型为 nvarchar(2000)，用于保存 CDN 上图像的 URL。如果我在查询中包含此列(仅在 SELECT 部分)，查询会以某种方式阻塞并生成 2-30 秒范围内的结果(在不同的运行中有所不同)。如果我注释掉该列，查询将按预期运行得很快。如果我包含“图像”列，但注释掉 row_number 列，查询也会按预期快速运行。

显然，我对 URL 的大小过于自由，所以我开始调整大小。我发现如果我将 Image 列设置为 nvarchar(884)，那么查询将按预期运行得很快。如果我将其设置为 885，它又会变慢。这并不绑定(bind)到某一列，而是绑定(bind)到 SELECT 语句中所有列的大小。如果我只是将大小增加一，性能差异就很明显。

我不是数据库专家，所以欢迎任何建议。

PS 在本地 SQL Server 2012 Express 中不存在性能问题。

PPS 使用 OFFSET 0 ROWS FETCH NEXT 438 ROWS ONLY 运行查询(当然没有 row_count 列)也很慢。

Answer 1

Row_number 必须对所有行进行排序，才能按照您想要的顺序获得内容。在结果集中添加较大的列意味着所有列都已排序，因此速度会慢得多/执行更多 IO。顺便说一句，如果在调试此类问题时在 SSMS 中启用“setstatisticsioon”和“setstatisticstimeon”，则可以看到这一点。它将使您深入了解查询中运行时发生的 IO 数量和其他操作: https://learn.microsoft.com/en-us/sql/t-sql/statements/set-statistics-io-transact-sql?view=sql-server-2017

就如何使此查询运行得更快而言，我鼓励您考虑一些可能会稍微改变数据库架构设计的事情。首先，考虑您是否确实需要按特定顺序对行进行排序。如果您不需要按顺序排列内容，那么在没有 row_number 的情况下迭代它们会更便宜(通过可测量的量)。因此，如果您只想在概念上对每个条目进行一次迭代，则可以通过按仍然单调的更静态的内容(例如标识列)进行排序来实现此目的。其次，如果您确实需要对事物进行排序，那么请考虑它们是否经常/不经常更改。如果不常见，则可以只计算一个列值并将其保留到具有所需相对顺序的每一行中(并在每次修改表时更新它)。在此模型中，您可以为新列建立索引，然后按该顺序请求内容(按查询中的顶级顺序 - 不需要 row_number)。如果您确实需要像您正在做的那样进行动态计算，并且始终需要按照精确的顺序进行计算，那么您的最终选择是将 URL 移至第二个表并在 row_number 之后与其连接。这将避免在计算 row_number 时出现“宽”排序。

祝你好运