sql - SQL 中的分页 - 性能问题

Question

我正在尝试使用分页，我在 SO 中找到了完美的链接

https://stackoverflow.com/a/109290/1481690

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
          FROM      Orders
          WHERE     OrderDate >= '1980-01-01'
        ) AS RowConstrainedResult
WHERE   RowNum >= 1
    AND RowNum < 20
ORDER BY RowNum

我正在尝试将完全相同的查询与我的内部查询中的几个表的附加连接一起使用。

在以下场景中很少遇到性能问题

WHERE   RowNum >= 1
    AND RowNum < 20  ==>executes faster approx 2 sec


    WHERE   RowNum >= 1000
    AND RowNum < 1010      ==>  more time  approx 10 sec

    WHERE   RowNum >= 30000
    AND RowNum < 30010    ==> more time approx 17 sec

每次我选择 10 行但时间差很大。有什么想法或建议吗？

我选择这种方法作为动态绑定(bind)列并形成查询。有没有其他更好的方法可以在 SQl Server 2008 中组织分页查询。

有什么方法可以提高查询的性能吗？

谢谢

Answer 1

我总是检查我在查询中访问了多少数据，并尝试消除不必要的列和行。好吧，这些只是您可能已经检查过的明显点，但只是想指出以防万一。在您的查询中，性能缓慢可能是因为您执行“Select *”。从表中选择所有列不允许有好的执行计划。检查您是否只需要选定的列，并确保表 Orders 上有正确的覆盖索引。

由于 SQL 2008 版本中没有显式的 SKIPP 或 OFFSET 函数，我们需要创建一个，我们可以通过 INNER JOIN 创建。在一个查询中，我们将首先使用 OrderDate 生成 ID，该查询中将没有其他内容。我们在第二个查询中执行相同的操作，但如果您需要 ALL 列，我们还会从表 ORDER 或 ALL 中选择一些其他感兴趣的列。然后我们加入它以按 ID 和 OrderDate 查询结果，并为第一个查询添加 SKIPP 行过滤器，其中数据集处于所需的最小大小。试试这个代码。

    SELECT q2.*
    FROM
    (
        SELECT ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, OrderDate
        FROM      Orders
        WHERE     OrderDate >= '1980-01-01'
    )q1
    INNER JOIN 
    (
        SELECT ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
        FROM      Orders
        WHERE     OrderDate >= '1980-01-01'
    )q2
        ON q1.RowNum=q2.RowNum AND q1.OrderDate=q2.OrderDate AND q1.rownum BETWEEN 30000 AND 30020

To give you the estimate, i tried this with following test data and no matter what window you query the results are back in less than 2 seconds, and note that the table is HEAP (no index) Table has total 2M rows. test select is querying 10 rows from 50,000 to 50,010

The below Insert took around 8 minutes.

    IF object_id('TestSelect','u') IS NOT NULL
        DROP TABLE TestSelect
    GO
    CREATE TABLE TestSelect
    (
        OrderDate   DATETIME2(2)
    )
    GO

    DECLARE @i bigint=1, @dt DATETIME2(2)='01/01/1700'
    WHILE @I<=2000000
    BEGIN

        IF @i%15 = 0
            SELECT @DT = DATEADD(DAY,1,@dt)

        INSERT INTO dbo.TestSelect( OrderDate )
        SELECT @dt

        SELECT @i=@i+1
    END

Selecting the window 50,000 to 50,010 took less than 3 seconds.

Selecting the last single row 2,000,000 to 2,000,000 also took 3 seconds.

    SELECT q2.*
    FROM
    (
        SELECT  ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum 
                ,OrderDate
        FROM TestSelect
        WHERE OrderDate >= '1700-01-01'
    )q1
    INNER JOIN
    (
        SELECT  ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum 
                ,*
        FROM TestSelect
        WHERE OrderDate >= '1700-01-01'
    )q2
        ON q1.RowNum=q2.RowNum 
        AND q1.OrderDate=q2.OrderDate 
        AND q1.RowNum BETWEEN 50000 AND 50010