locked
Server Side Paging ( Best General Practice ) RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I just searched : "Server Side Paging"  on this forum and multiple existing threads pulled up with nice suggestions. However I came here in particular with following two approaches in my mind I tried to observe the execution plans but that is not my cup of tea to be honest, so I am requesting y'all to get me started with making decisions instead asking. ( Give a man a fish, and you feed him for a day; show him how to catch fish, and you feed him for a lifetime. )

    Basically I need to decide the best query from performance point of view.

    I've tried to use Northwind database instead our Legacy DB so there might a (some) filter(s) that might not make sense but I am trying to "reproduce" quite similar query as I can.

    SQL Server 2008 R2

    Query 1 :

    USE Northwind;

DECLARE @page_size INT;
DECLARE @current_page INT;

SET @page_size = 20;
SET @current_page = 3;

WITH Paging
AS (
	SELECT rn = ROW_NUMBER() OVER (
			ORDER BY OrderID
			)
		,*
	FROM dbo.Orders
	WHERE Freight > 0
	)
SELECT TOP (@page_size) PG.*
FROM Paging PG
WHERE PG.rn > ((@current_page * @page_size) - (@page_size))

    Query 2 :

    USE Northwind;

DECLARE @page_size INT;
DECLARE @current_page INT;

SET @page_size = 20;
SET @current_page = 3;

SELECT TOP (@page_size) *
FROM dbo.Orders
WHERE Freight > 0
	AND OrderID NOT IN (
		SELECT TOP ((@page_size) * ((@current_page) - 1)) OrderID
		FROM dbo.Orders
		)
    Thanks !


    Friday, January 3, 2014 5:20 AM

Answers

  • Question
    Sign in to vote
    2
    Sign in to vote

    I believe the the Query using a CTE is efficient. I looked at the IO Statistics and looks like the Query with CTE results in much less reads. Here is my comparison.

    Without CTE

      
    --Run 1

(20 row(s) affected)
Table 'Orders'. Scan count 2, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

--Run 2
(20 row(s) affected)
Table 'Orders'. Scan count 2, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

--Run 3
(20 row(s) affected)
Table 'Orders'. Scan count 2, logical reads 43, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)
    With CTE
    --Run 1
(20 row(s) affected)
Table 'Orders'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

--Run 2
(20 row(s) affected)
Table 'Orders'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)
--Run 3
(20 row(s) affected)
Table 'Orders'. Scan count 1, logical reads 22, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

    Satheesh
    My Blog


    • Proposed as answer by Fanny Liu Wednesday, January 8, 2014 8:01 AM
    • Marked as answer by Fanny Liu Tuesday, January 14, 2014 9:03 AM
    Friday, January 3, 2014 5:59 AM

All replies