T-sql: how to perform optimised Paging?

Question

I wrote the following code, it works fine, but it takes like 3 sec to complete if the table is containing a million record. Is there a way to optimize the following code.

DBCC DROPCLEANBUFFERS; 
DBCC FREEPROCCACHE;

DECLARE @Page_Size int;
DECLARE @Page_Number int;
DECLARE @Lower_Bound int;
DECLARE @Upper_Bound int;

SET @Page_Size = 30;
SET @Page_Number = 30000;
SET @Lower_Bound = (@Page_Number - 1) * @Page_Size;
--SET @Upper_Bound = @Page_Number * @Page_Size;


WITH Customers AS--(Row_Numbr, Record_Id, First_Name, 
        Middle_Name, Last_Name, Email, Telephone) AS 
(

    SELECT ROW_NUMBER() 
        OVER 
         (ORDER BY Account.Customer.Record_Id) AS Row_Numbr, * 
    FROM Account.Customer 
)

SELECT top(@Page_Size) * 
FROM Customers 
WHERE Row_Numbr > @Lower_Bound-- 
    AND Row_Numbr <= @Upper_Bound -- This is suppose to be faster
--SELECT * FROM Customers 
--WHERE Row_Numbr > @Lower_Bound  
--   AND Row_Numbr <= @Upper_Bound

Answer 1

First, why DBCC DROPCLEANBUFFERS; ? This is a hard cold reset of the buffer pool. Unless you want to measure and tune your hard drives IO performance, nobody cares about the performance of a cold cache. This is not how your system will work. Caching pages in the buffer pool is the most critical performance aspect in databases, and you take that out. Its like showing up in a Ferrari without the engine and asking why is so slow. For performance measurements you should do exactly the opposite: run he query 4-5 times to warm up the cache, then measure.

Second, what is your table structure? Is the table Account.Customer table cluster index order by Record_id? If no, you will never get the performance you want, no matter how you express your T-SQL.

And last but not least, what system do you have? Does it have enough RAM to cache the entire database in memory? If no, buy more RAM. Are there other processes that compete for memory, like IIS/Asp? If yes, kick them out to their own server, you should never ever run the database on the same host as the web server if performance is important.

For an alternative fast paging consider keyset driven solutions:

/* moving up */
SELECT top(@Page_Size) * 
FROM Account.Customer  
WHERE Record_Id > @lastPageRecordId
ORDER BY Record_Id;

/* moving down */
SELECT top(@Page_Size) * 
FROM Account.Customer  
WHERE Record_Id < @firstPageRecordId
ORDER BY Record_Id DESC;

A keyset driven solution can seek straight to the last position and then range scans the next/previous page, using the clustered index key position. The paging logic (state) must remember the last and first keys on the page being displayed in order to continue from there, instead of remembering the page number.

Rowcount based solutions (as well as LIMIT in MySQL) are less efficient than keyset based ones because they always have to count the records to position themselves, instead of seeking straight to the position as keysets can.

Answer 2

I use this stored procedure :

CREATE PROCEDURE sp_PagedItems
    (
     @Page int,
     @RecsPerPage int
    )
AS

-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON


--Create a temporary table
CREATE TABLE #TempItems
(
    ID int IDENTITY,
    Name varchar(50),
    Price currency
)


-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (Name, Price)
SELECT Name,Price FROM tblItem ORDER BY Price

-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)

-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT *,
       MoreRecords =
    (
     SELECT COUNT(*)
     FROM #TempItems TI
     WHERE TI.ID >= @LastRec
    )
FROM #TempItems
WHERE ID > @FirstRec AND ID < @LastRec


-- Turn NOCOUNT back OFF
SET NOCOUNT OFF

Answer 3

If someone is using SQL Server 2012 - a new feature was added in the ORDER BY clause, to query optimization of a set data, making work easier with data paging for anyone who writes in T-SQL as well for the entire Execution Plan in SQL Server. Reference here.