对大型表进行自定义排序和分页

Question

我习惯从2005年版本的MS SQL Server脚本中获得ROW_NUMBER函数的好处。但是我注意到使用这个函数查询大型表有很大的性能劣势。

想象一个有四列的表(来自外部数据库的真实表有更多列，但为了避免示例的复杂性，我只使用了这些列):

DECLARE TABLE StockItems (
  Id int PRIMARY KEY IDENTITY(1,1),
  StockNumber nvarchar(max),
  Name nvarchar(max),
  [Description] nvarchar(max))

我写了一个程序来查询这个表，这个表有200000 +行，参数如下:

• @SortExpression -列的名称，我想排序
• @SortDirection -位信息(0=升序，1=降序)
• @startRowIndex -基于零的索引，我想检索行
• @maximumRows -要检索的行数

查询:

SELECT sortedItems.Id
    ,si.StockNumber
    ,si.Name
    ,si.Description
FROM (SELECT s.Id
         ,CASE WHEN @SortDirection=1 THEN
            CASE
               WHEN CHARINDEX('Name',@SortExpression)=1 THEN
                 ROW_NUMBER() OVER (ORDER by s.Name DESC)
               WHEN CHARINDEX('StockNumber',@SortExpression)=1 THEN
                 ROW_NUMBER() OVER (ORDER by s.StockNumber DESC)
            ELSE ROW_NUMBER() OVER (ORDER by s.StockNumber DESC)
            END
          ELSE    
            CASE
               WHEN CHARINDEX('Name',@SortExpression)=1 THEN
                  ROW_NUMBER() OVER (ORDER by s.Name ASC)
               WHEN CHARINDEX('StockNumber',@SortExpression)=1 THEN
                  ROW_NUMBER() OVER (ORDER by s.StockNumber ASC)
            ELSE  ROW_NUMBER() OVER (ORDER by s.StockNumber ASC)
            END
          END AS RowNo
       FROM stockItems s
     ) as sortedItems
INNER JOIN StockItems si ON sortedItems.Id=si.Id
ORDER BY sortedItems.RowNo

在行数快速增长的情况下，ROW_NUMBER变得无效，因为必须对所有行进行排序。

你能帮我避免这个性能劣势并加快查询速度吗?

Answer 1

查看执行路径。只要你有正确的索引，ROW_NUMBER()不会有很大的影响。您查询的问题不在ROW_NUMBER()中。使用动态代替，它将消除由ROW_NUMBER()引起的2分割。我在一个>4mil记录表上测试了这个，它在一瞬间返回:

DECLARE @SortExpression VARCHAR(32)  SET @SortExpression = 'StockNumber'
DECLARE @SortDirection BIT           SET @SortDirection  = 1
DECLARE @startRowIndex BIGINT        SET @startRowIndex  = 1000
DECLARE @maximumRows BIGINT          SET @maximumRows    = 5000
DECLARE @vsSQL AS NVARCHAR(MAX)
SET @vsSQL = ''
SET @vsSQL = @vsSQL + 'SELECT sortedItems.Id, sortedItems.StockNumber, sortedItems.Name, sortedItems.Description FROM ( '
SET @vsSQL = @vsSQL + 'SELECT s.Id, s.StockNumber, s.Name, s.Description, '
SET @vsSQL = @vsSQL + 'ROW_NUMBER() OVER (ORDER BY ' + @SortExpression + ' ' + CASE @SortDirection WHEN 1 THEN 'DESC' ELSE 'ASC' END + ') AS RowNo '
SET @vsSQL = @vsSQL + 'FROM StockItems s '
SET @vsSQL = @vsSQL + ') AS sortedItems '
SET @vsSQL = @vsSQL + 'WHERE RowNo BETWEEN ' + CONVERT(VARCHAR,@startRowIndex) + ' AND ' + CONVERT(VARCHAR,@startRowIndex+@maximumRows) + ' '
SET @vsSQL = @vsSQL + 'ORDER BY sortedItems.RowNo'
PRINT @vsSQL
EXEC sp_executesql @vsSQL

Answer 2

您可以将case表达式移动到order by子句:

order by (case when @SortDirection=1 and CHARINDEX('Name',@SortExpression)=1 then s.name end) desc,
         (case when @SortDirection=1 and CHARINDEX('StockNumber',@SortExpression)=1 then s.StockNumber end) desc,
         (case when @SortDirection=1 and (CHARINDEX('StockNumber',@SortExpression)<>1 and CHARINDEX('Name',@SortExpression)<>1) then va.match end) desc,
         (case when @SortDirection<>1 and CHARINDEX('Name',@SortExpression)=1 then s.name end) asc,
         (case when @SortDirection<>1 and CHARINDEX('StockNumber',@SortExpression)=1 then s.StockNmber end) asc,
         (case when @SortDirection<>1 and (CHARINDEX('StockNumber',@SortExpression)<>1 and CHARINDEX('Name',@SortExpression)<>1) then va.match end) asc

我注意到表达式有va.match，所以它在你的查询中不匹配任何表。所以，我只是把order by表达式。

是的，随着表变大，这将花费更多的时间。我不知道order by会比row_number()更有效率，但这是可能的。

如果您需要对行进行排序，那么您必须以某种方式进行排序(也许您可以使用索引代替)。如果你不关心顺序，你可以试试:

row_number() over (order by (select NULL))

在SQL Server中，我发现这分配了一个没有单独排序的序号。然而，这并不能保证(我还没有找到任何支持这种用法的文档)。而且，从一次运行到下一次运行，结果不一定是稳定的。

Answer 3

我找到了解决方案，如何在大型结果集上使用ROW_NUMBER()函数来避免性能损失。我在问题中没有写的一个目标是避免将查询声明为nvarchar变量并执行它，因为它可能会为SQL注入打开大门。

因此，解决方案是尽可能按所需排序顺序查询数据，然后查询结果集并切换排序，只获取当前页面的数据。最后，我可以将结果按相反的顺序排序，然后重新排序。

我定义了新的变量@innerCount来查询大多数内部结果集，并将其排序为查询客户端指定的@sortExpression和@sortDirection变量

SET @innerCount = @startRowIndex + @maximumRows
Select OppositeQuery.Id
,s.StockNumber
,s.Name
,s.Description
FROM (SELECT TOP (@maximumRows) InnerItems.Id
       FROM
            (SELECT TOP (@innerCount) sti.Id
               FROM stockItems sti
               ORDER BY
                CASE WHEN @SortDirection=1 THEN
                    CASE
                        WHEN CHARINDEX('Name',@SortExpression)=1 THEN sti.Name
                        WHEN CHARINDEX('StockNumber',@SortExpression)=1 THEN sti.StockNumber
                        ELSE sti.StockNumber
                    END
                END DESC
                CASE WHEN ISNULL(@SortDirection,0)=0 THEN
                    CASE
                       WHEN CHARINDEX('Name',@SortExpression)=1 THEN sti.Name
                       WHEN CHARINDEX('StockNumber',@SortExpression)=1 THEN sti.StockNumber
                       ELSE sti.StockNumber
                    END
                END ASC
             ) as InnerQuery
          INNER JOIN StockItems si on InnerQuery.Id=si.Id
          ORDER BY
            CASE WHEN @SortDirection=1 then
                CASE
                   WHEN CHARINDEX('Name',@SortExpression)=1 THEN si.Name
                   WHEN CHARINDEX('StockNumber',@SortExpression)=1 THEN si.StockNumber
                   ELSE si.StockNumber
               END
            END ASC
            CASE WHEN ISNULL(@SortDirection,0)=0 then
                CASE
                   WHEN CHARINDEX('Name',@SortExpression)=1 THEN si.Name
                   WHEN CHARINDEX('StockNumber',@SortExpression)=1 THEN si.StockNumber
                   ELSE si.StockNumber
                END
            END ASC
    ) AS OppositeQuery
INNER JOIN StockItems s on OppositeQuery.Id=s.Id
ORDER BY
CASE WHEN @SortDirection=1 THEN
    CASE
        WHEN CHARINDEX('Name',@SortExpression)=1 THEN s.Name
        WHEN CHARINDEX('StockNumber',@SortExpression)=1 THEN s.StockNumber
        ELSE s.StockNumber
     END
END DESC
CASE WHEN ISNULL(@SortDirection,0)=0 THEN
    CASE
        WHEN CHARINDEX('Name',@SortExpression)=1 THEN s.Name
        WHEN CHARINDEX('StockNumber',@SortExpression)=1 THEN s.StockNumber
        ELSE s.StockNumber
    END
END ASC

这种方法的缺点是我必须对数据进行三次排序，但是在对StockItems表进行多个内部连接的情况下，子查询比使用ROW_NUMBER()函数快得多。

感谢所有贡献者的帮助。