SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server 2005 Paging – The Holy Grail


SQL Server 2005 Paging – The Holy Grail

Author
Message
jcraddock
jcraddock
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1232 Visits: 99
These are all well and good, but I rarely need such results in a T-SQL window. Where I need them is in an application where I want to paginate using ASP.net etc.

In those cases, the best I can come up with is two calls. To be clear - I use dynamic sql to build all queries in my list windows. There I need the total count and the page of data required.



R Michael
R Michael
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3523 Visits: 275
jcraddock (3/11/2009)
These are all well and good, but I rarely need such results in a T-SQL window. Where I need them is in an application where I want to paginate using ASP.net etc.

In those cases, the best I can come up with is two calls. To be clear - I use dynamic sql to build all queries in my list windows. There I need the total count and the page of data required.


This is exactly the scenario I was imagining when putting this solution together, with the idea being that you can return both the total count and page n in a single call/read.

There has been some very interesting comments and ideas in this thread. As far as this idea goes, it works well in some cases, and not so well in others (as highlighted by other posters).

I built a paging proc based on this principle and, after extensive testing, found it to be the best approach. I saw only a few ms of cpu & elapsed time overhead. I would be very interested to know the size of some of the datasets peso and others are using. This would be very useful in determining the best solution for a given problem.

Thank you, everyone, for you comments and insight!

SQL guy and Houston Magician
R Michael
R Michael
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3523 Visits: 275
Actually, rereading the thread, I see that Peso did post the size of his dataset; 2,000,000 rows.

It would be interesting to determine a relative cut off point where the row_number trick is the most efficient and where the two-bite approach becomes best.

For my purposes, the biggest cost was the query itself. The underlying tables hold hundreds of millions of records, but the data is pretty well filtered down by the time we get to the record set to page. To avoid running that query twice was a big win.

I deliberately avoided including time statistics in my comparisons because they can be very subjective (unless they demonstrate a clear performance difference as a few posters noted).

SQL guy and Houston Magician
jcraddock
jcraddock
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1232 Visits: 99
Um, how are you going to declare the variables and such inside one statement from an ASP.Net page/class/etc? This looks like you have to use a proc, and procs are out with the queries I generate.



jackie.williams
jackie.williams
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 118
jcraddock (3/11/2009)
Um, how are you going to declare the variables and such inside one statement from an ASP.Net page/class/etc? This looks like you have to use a proc, and procs are out with the queries I generate.


I'm not sure what you're saying here. We use stored procedures that generate dynamic queries to retrieve datasets similar to this all the time. Our datalayer takes a datarow containing the values for whatever parameters are necessary for the procedures and iterates through that to pass the necessary parameters into our stored proc, then the stored proc makes sure any sql injection chars etc are stripped, generates the dynamic select query with paging etc. based on whatever values have been passed into it and returns a dataset with the requested page of data and a full count of the records. Why would procs be out in what you're doing?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)

Group: General Forum Members
Points: 816763 Visits: 46281
First of all... very well written article. Nice to see examples like that with some performance stats. Unfortunately, the most important performance for the user (duration) wasn't measured and the test data was kinda shy on row count.

It turns out that the "2 Bite" method is very fast provided that, as someone else already pointed out, that you use the system tables to give you a leg up. And, if you have a clustered index on the table to keep it from being just a heap, the first "Bite" requires no joins... that means you can have the best of both worlds... performance and fewer reads.

Just to give everyone some common data to test on, here's a million rows of data to play with... only takes about 41 seconds including the index builds...

--===== Create and populate a 1,000,000 row test table.
-- Column "RowNum" has a range of 1 to 1,000,000 unique numbers
-- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers
-- Column "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings
-- Column "SomeMoney has a range of 0.0000 to 99.9999 non-unique numbers
-- Column "SomeCSV" contains 'Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10'
-- for all rows.
-- Column "SomeHex12" contains 12 random hex characters (ie, 0-9,A-F)
-- Jeff Moden
SELECT TOP 1000000
RowNum = IDENTITY(INT,1,1),
SomeInt = ABS(CHECKSUM(NEWID()))%50000+1,
SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65),
SomeCSV = CAST('Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' AS VARCHAR(80)),
SomeMoney = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),
SomeHex12 = RIGHT(NEWID(),12)
INTO dbo.JBMTest
FROM Master.dbo.SysColumns t1
CROSS JOIN Master.dbo.SysColumns t2

--===== A table is not properly formed unless a Primary Key has been assigned
ALTER TABLE dbo.JBMTest
ADD PRIMARY KEY CLUSTERED (RowNum)

--===== Create and index for the lookups we expect
CREATE INDEX IX_JBMTest_SomeInt_SomeLetters2
ON dbo.JBMTest (SomeInt,SomeLetters2)



... and here's some code that test two newer methods, the original "Holy Grail", and Peso's method. If someone else wants to test another method, please add it to this code and run the whole thing because machine speeds vary and it would be nice to have it all together to compare to...

--===== Define the starting row and page size
DECLARE @StartRow INT ; SET @StartRow = 900000
DECLARE @PageSize INT ; SET @PageSize = 50

PRINT '--============================================================================='
PRINT '-- The "Holy Grail" method'
PRINT '--============================================================================='

--===== Turn on the timers
SET STATISTICS IO ON
SET STATISTICS TIME ON

--===== The "Holy Grail" method of getting a page of info
;WITH
cteCols AS
(
SELECT SomeInt, SomeLetters2,
ROW_NUMBER() OVER(ORDER BY SomeInt, SomeLetters2) AS Seq,
ROW_NUMBER() OVER(ORDER BY SomeInt DESC, SomeLetters2 DESC) AS TotRows
FROM dbo.JBMTest
)
SELECT Seq, SomeInt, SomeLetters2, TotRows + Seq - 1 AS TotRows
FROM cteCols
WHERE Seq BETWEEN @StartRow AND @StartRow + @PageSize - 1
ORDER BY Seq

--===== Turn off the timers
SET STATISTICS TIME OFF
SET STATISTICS IO OFF

PRINT '--============================================================================='
PRINT '-- The "No RBAR/No Join" method'
PRINT '--============================================================================='

--===== Turn on the timers
SET STATISTICS IO ON
SET STATISTICS TIME ON

--===== The "No RBAR/No Join" method
;WITH
cteCols AS
(
SELECT NULL AS SomeInt, NULL AS SomeLetters2, 0 AS Seq, Rows AS TotRows
FROM sys.Partitions
WHERE Object_ID = OBJECT_ID('dbo.JBMTest')
AND Index_ID = 1
UNION ALL --------------------------------------------------------------------
SELECT SomeInt, SomeLetters2,
ROW_NUMBER() OVER(ORDER BY SomeInt, SomeLetters2) AS Seq,
NULL AS TotRows
FROM dbo.JBMTest
)
SELECT Seq, SomeInt, SomeLetters2, TotRows
FROM cteCols
WHERE Seq BETWEEN @StartRow AND @StartRow + @PageSize - 1
OR Seq = 0
ORDER BY Seq

--===== Turn off the timers
SET STATISTICS TIME OFF
SET STATISTICS IO OFF

PRINT '--============================================================================='
PRINT '-- A different No Join method'
PRINT '--============================================================================='

--===== Turn on the timers
SET STATISTICS IO ON
SET STATISTICS TIME ON

--===== A different No Join method
;WITH
cteCols AS
(
SELECT SomeInt, SomeLetters2,
ROW_NUMBER() OVER(ORDER BY SomeInt, SomeLetters2) AS Seq,
NULL AS TotRows
FROM dbo.JBMTest
)
SELECT Seq, SomeInt, SomeLetters2, (SELECT Rows
FROM sys.Partitions
WHERE Object_ID = OBJECT_ID('dbo.JBMTest')
AND Index_ID = 1) AS TotRows
FROM cteCols
WHERE Seq BETWEEN @StartRow AND @StartRow + @PageSize - 1
OR Seq = 0
ORDER BY Seq

--===== Turn off the timers
SET STATISTICS TIME OFF
SET STATISTICS IO OFF

PRINT '--============================================================================='
PRINT '-- Peso''s Embedded "2 Bite" method'
PRINT '--============================================================================='
--===== Turn on the timers
SET STATISTICS IO ON
SET STATISTICS TIME ON

--===== Embedded "2 Bite" method
;WITH
cteCols AS
(
SELECT SomeInt, SomeLetters2,
ROW_NUMBER() OVER(ORDER BY SomeInt, SomeLetters2) AS Seq,
NULL AS TotRows
FROM dbo.JBMTest
)
SELECT Seq, SomeInt, SomeLetters2, (SELECT COUNT(*) FROM dbo.JBMTest) AS TotRows
FROM cteCols
WHERE Seq BETWEEN @StartRow AND @StartRow + @PageSize - 1
OR Seq = 0
ORDER BY Seq

--===== Turn off the timers
SET STATISTICS TIME OFF
SET STATISTICS IO OFF



Here's how that played out on my humble single 1.8Ghz/1G Ram desktop running 2k5 sp2....

--=============================================================================
-- The "Holy Grail" method
--=============================================================================

(50 row(s) affected)
Table 'JBMTest'. Scan count 1, logical reads 1985, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 7594 ms, elapsed time = 9261 ms.
--=============================================================================
-- The "No RBAR/No Join" method
--=============================================================================

(51 row(s) affected)
Table 'JBMTest'. Scan count 1, logical reads 1985, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysrowsets'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 1265 ms, elapsed time = 1314 ms.
--=============================================================================
-- A different No Join method
--=============================================================================

(50 row(s) affected)
Table 'sysrowsets'. Scan count 50, logical reads 100, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'JBMTest'. Scan count 1, logical reads 1985, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 1157 ms, elapsed time = 1383 ms.
--=============================================================================
-- Peso's Embedded "2 Bite" method
--=============================================================================

(50 row(s) affected)
Table 'JBMTest'. Scan count 2, logical reads 3970, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 1406 ms, elapsed time = 1466 ms.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Thomas Bühler
Thomas Bühler
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 17
Nice article. But I miss a solution for handling a custom sorting when using a paging functionality.
In practice (lets say a ASP.NET application with a GridView component) you need to sort dynamically by another column than the ID. So you have to take care of this before you do the paging.
Look here: http://aspnet.4guysfromrolla.com/articles/032206-1.aspx#
If you combine the custom sort capability together with the discussed Total Row retrieval you can get most out of this...

Cheers
C. Westra
C. Westra
SSC-Enthusiastic
SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)

Group: General Forum Members
Points: 133 Visits: 59
I'm afraid differences in resources are partly induced by Sql Server caching (intermediate) results between the first and latter queries.
If you use DBCC DROPCLEANBUFFERS between the queries, caches are emptied and differences in time and physical reads may better reflect actual use of resources.
jcraddock
jcraddock
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1232 Visits: 99
jackie.williams (3/11/2009)
jcraddock (3/11/2009)
Um, how are you going to declare the variables and such inside one statement from an ASP.Net page/class/etc? This looks like you have to use a proc, and procs are out with the queries I generate.


I'm not sure what you're saying here. We use stored procedures that generate dynamic queries to retrieve datasets similar to this all the time. Our datalayer takes a datarow containing the values for whatever parameters are necessary for the procedures and iterates through that to pass the necessary parameters into our stored proc, then the stored proc makes sure any sql injection chars etc are stripped, generates the dynamic select query with paging etc. based on whatever values have been passed into it and returns a dataset with the requested page of data and a full count of the records. Why would procs be out in what you're doing?


Well, all my applications are actually one application. All table information, validation rules, permissions, roles, etc, are stored in metadata. I generate the queries for list windows dynamically from the ASP.NET page with full filtering and sorting - one list page works for every single table in all systems, similarly one editpage works for every table in every system...to do what you propose, I would have to move my OBJECT class into Sql, as it contains all the metadata. I have no interest in running .NET from my SQL Server for several reasons - number one being total rewrite of the architecture. I was just hoping there was a way to do it directly from the ASP code instead of relying on a procedure. At the top of this window you can see the record count and then the page is displayed. I was hoping for a way (without rearchitecting my class onto SQL Server and taking the performance hit for turning that on) to get both numbers at once.

Here is a Screenshot:




R Michael
R Michael
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3523 Visits: 275
Thomas Bühler (3/12/2009)
Nice article. But I miss a solution for handling a custom sorting when using a paging functionality.


Yeah, I deliberately left that bit out because it had been covered by so many other articles. The 'real world' problem I chose this approach for does implement custom sorting by using conditional logic in the OVER(ORDER BY).

SQL guy and Houston Magician
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search