Query performance with ROW_NUMBER function

Question

Query performance with ROW_NUMBER function

vijayd

SSC Enthusiast

Points: 175
More actions
November 23, 2020 at 2:09 pm

#3813063

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

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

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

Mike01 SSChampion Points: 11395 More actions · Answer 1

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/

vijayd SSC Enthusiast Points: 175 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 3

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)

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 4

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.