Query performance with ROW_NUMBER function

  • Hello All,

    Could any of you please let me know why the row_number paging function is having a performance issue when using the

    "WHERE results.ROWNUMBER BETWEEN 1 AND 50" clause (takes more than 2 mins),  but runs in 5 secs when i comment the where clause which will return the entire result set.

    I see the query is using the same indexes, but some how when the where clause is used the logical reads increases exponentially for 2 tables as below, I was trying to fix this from quite some time, any help greatly appreciated.

    Thanks

     

    when the where clause is used

    Table 'INTERCHANGEJDO'. Scan count 15416, logical reads 144336865, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Worktable'. Scan count 16, logical reads 70965478, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead

    When the where clause is commented

    Table 'INTERCHANGEJDO'. Scan count 16, logical reads 18501, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

     

     

    set statistics io on

    go

    WITH ALLRESULTS AS ( SELECT t5.*, ROW_NUMBER() OVER ( ORDER BY FUNCTIONALGROUPNAME DESC ) ROWNUMBER FROM

    ( SELECT t0.JDOID, t0.ACCEPTCODE, t0.ACCEPTCODE824,t0.ACKDATE, t0.UTILITY, t0.DIRECTION, t0.DOCUMENTREFERENCENUMBER,

    t3.DESCRIPTION FUNCTIONALGROUPNAME, t0.INTERCHANGE_JDOID, t0.MODIFIEDBY, t0.MODIFYDATE, t0.PROCESSDATE, t0.SENTDATE,

    t0.SETID, t0.STATUS, t0.TURNED,

    t0.PARTNER, t0.NETWORKID, t1.INTERCHANGECONTROLNUMBER, t1.TRADINGPARTNERPAIR_JDOID,

    TRANSLATIONJOBID = CASE WHEN (t0.TRANSLATIONJOBID IS NULL or t0.TRANSLATIONJOBID='') THEN t1.TRANSLATIONJOBID

    ELSE t0.TRANSLATIONJOBID END

    FROM DOCUMENTJDO AS t0 WITH(NOLOCK) INNER JOIN INTERCHANGEJDO t1 ON t0.INTERCHANGE_JDOID = t1.JDOID

    INNER JOIN TRADINGPARTNERPAIRJDO t3 ON t1.TRADINGPARTNERPAIR_JDOID=t3.JDOID WHERE t0.direction = 'I' AND

    t0.processDate >= '2019-07-18 00:00:00'

    AND t0.processDate <= '2020-09-18 23:59:59' AND ( t1.TRADINGPARTNERPAIR_JDOID IN

    (SELECT DISTINCT vw.TPPDS_JDOID FROM vwEDXMain vw WITH(NOLOCK) WHERE (vw.levelCode LIKE '32,J4Q%') AND

    vw.TPPDS_JDOID IS NOT NULL) ) ) AS t5 ),

    RESULT AS (SELECT results.*, ( SELECT COUNT(*) FROM ALLRESULTS ) AS TOTAL FROM ALLRESULTS AS results

    WHERE results.ROWNUMBER BETWEEN 1 AND 50

    ) ,

    DOCUMENTVIEWED AS (SELECT DISTINCT DOCUMENTID FROM DOCUMENTUSERJDO WITH(NOLOCK) WHERE VIEWED=1) ,

    DOCUMENTVIEWEDBYUSER AS (SELECT DISTINCT DOCUMENTID AS DOCUMENTID_USER, USERID, VIEWED FROM DOCUMENTUSERJDO

    WHERE USERID = 'com.-19422' AND VIEWED=1)

    SELECT t5.*

    , VIEWED = CASE WHEN t5.DOCUMENTID_VIEWED IS NULL THEN 0 WHEN t6.USERID IS NULL THEN 2 ELSE T6.VIEWED END

    FROM ( SELECT t3.*, t4.DOCUMENTID AS DOCUMENTID_VIEWED FROM RESULT AS t3

    LEFT JOIN DOCUMENTVIEWED AS t4 ON t4.DOCUMENTID = 'com.-'+ltrim(t3.JDOID) ) AS t5

    LEFT JOIN DOCUMENTVIEWEDBYUSER AS t6 ON t6.DOCUMENTID_USER = t5.DOCUMENTID_VIEWED ORDER BY T5.ROWNUMBER

    go

    set statistics io off

     

  • 144 million reads and 70 million reads??!!   How many rows are returned?  Have you looked at the execution plan?    This hits allresults twice, so doing rowcount twice

     

    SELECT results.*,

    (SELECT Count(*)

    FROM allresults) AS TOTAL

    FROM allresults AS results

    WHERE results.rownumber BETWEEN 1 AND 50

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Thanks for the reply,  the total number of records if  i remove the where clause "WHERE results.rownumber BETWEEN 1 AND 50" is 623.

    I also tried running the query by removing "

    (SELECT Count(*)

    FROM allresults) AS TOTAL" which is not making any difference, some how the "WHERE results.rownumber BETWEEN 1 AND 50"  is not liking this.

    Thanks appreciate any ideas

    Vijay

  • Perhaps paging using OFFSET/FETCH in the ORDER BY clause will work better than using ROW_NUMBER().

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • How many rows are in the table?

    I am pretty sure that your WHERE clause is the problem, not the ROW_NUMBER portion.  The query, without the WHERE doesn't need to do any comparisons, so it can run quickly.  As soon as that WHERE clause is in there, you now need to do a comparison on each and every row to see if the row number value is between 2 different values.  It is a lot of comparisons to look at and, I expect, a lot of data in the table.

    Depending on the data, you may get a performance boost by following Jeff's advice, or:

    1 - converting the CTE's to temp tables and putting indexes on them (may hurt performance too)

    2 - changing the WHERE to "WHERE results.rownumber <= 50" as then you are not comparing a range, just comparing 2 values and based on what you have, it should be functionally equivalent.

    3 - taking the full result (5 seconds to get the data), dump that to an indexed temp table and then filter that result set by the rownumber column

     

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply