SQL分页-性能问题 [英] Pagination in SQL - Performance issue

问题描述

我正尝试使用分页，所以我在SO中获得了完美的链接

Am trying to use pagination and i got the perfect link in 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

完全相同的查询正试图与内部查询中的几个表的其他联接一起使用.

Exact same query am trying to use with additional join of few tables in my inner Query.

在以下情况下几乎没有性能问题

Am getting few performance issues in following scenarios

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行，但时差巨大.有任何想法或建议吗?

Everytime i select 10 rows but huge time difference. Any idea or suggestions ?

我选择这种方法是动态绑定列并形成Query.还有其他更好的方法可以在SQl Server 2008中组织分页查询.

I chose this approach as am binding columns dynamically and forming Query. Is there any other better way i can organize the Pagination Query in SQl Server 2008.

有没有办法提高查询性能?

Is there a way i can improve the performance of the query ?

谢谢

推荐答案

我总是检查查询中正在访问多少数据，并尝试消除不必要的列和行. 好吧，这些只是您可能已经检查过但很想指出的明显点(如果您还没有的话). 在查询中，性能下降可能是因为您执行选择*".从表中选择所有列都不允许提供良好的执行计划. 检查是否只需要选择的列，并确保表Orders上具有正确的覆盖索引.

I always check how much data I am accessing in query and try to eliminate un necessary columns as well as rows. Well these are just obvious points you might have already check yet just wanted to pointed out in case you haven’t already. In your query the slow performance might be because you doing "Select *". Selecting all columns from table does not allow to come with good Execution plan. Check if you need only selected columns and make sure you have correct covering index on table Orders.

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

Because explicit SKIPP or OFFSET function is not available in SQL 2008 version we need to create one and that we can create by INNER JOIN. In one query we will first generate ID with OrderDate and nothing else will be in that query. We do the same in second query but here we also select some other interested columns from table ORDER or ALL if you need ALL column. Then we JOIN this to query results by ID and OrderDate and ADD SKIPP rows filter for first query where data set is at its minimal size what is required. Try this code.

    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

为了给您提供估计，我尝试使用以下测试数据进行此操作，无论您在哪个窗口查询结果，返回的结果均小于2 秒，并注意该表是HEAP(无索引)表的总数为2M 行.测试选择是查询50,000到50010的10行

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

下面的插入内容大约花费了8分钟.

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

选择50,000到500,010的窗口用了不到3秒的时间.

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

选择最后一个2,000,000至2,000,000的行也需要3秒钟.

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

这篇关于SQL分页-性能问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！