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
pfwojnar
pfwojnar
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 35
I've been using one of these two methods. The Optimizer seems to be smart enough to know not to do the COUNT multiple times. However, I've never done an analysis of this method quite as deep as the one you've done here. Great job by the way, I may need to change what I'm doing.

DECLARE @startRow INT ; SET @startrow = 50

;WITH cols
AS
(
SELECT table_name, column_name,
ROW_NUMBER() OVER(ORDER BY table_name, column_name) AS seq
FROM [INFORMATION_SCHEMA].columns
)
SELECT table_name, column_name, (SELECT COUNT(*) FROM cols) AS TotRows
FROM cols
WHERE seq BETWEEN @startRow AND @startRow + 49
ORDER BY seq

For the other way, replace (SELECT COUNT(*) FROM cols) AS TotRows with (SELECT MAX(seq) FROM cols) AS TotRows.
StarNamer
StarNamer
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1962 Visits: 1992
I tried this and found that it depends on the indexes avaialble.

Using a 1.2 million record table as follows

CREATE TABLE [dbo].[tblInvoice](
[SpecialGLIndicator] [varchar](50) NOT NULL,
[DocumentNumber] [bigint] NOT NULL,
[PostingDate] [smalldatetime] NOT NULL,
[DocumentDate] [smalldatetime] NOT NULL,
[EnteredOn] [smalldatetime] NOT NULL,
[DocTypeID] [int] NOT NULL,
[Period] [varchar](4) NOT NULL,
[PKID] [int] NOT NULL,
[Amount] [money] NOT NULL,
[UserID] [int] NOT NULL,
[TranCodeID] [int] NOT NULL,
[CompanyID] [int] NOT NULL,
[Month] [smalldatetime] NOT NULL,
[WithPO] [tinyint] NOT NULL,
[Lines] [int] NOT NULL,
[docs] [int] NOT NULL,
[TransCode] [varchar](10) NOT NULL,
CONSTRAINT [PK_tblInvoice] PRIMARY KEY CLUSTERED
(
[DocumentNumber] ASC,
[CompanyID] ASC,
[Month] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_27754] ON [dbo].[tblInvoice]
(
[CompanyID] ASC,
[Month] ASC
)
INCLUDE ( [docs])
GO
CREATE NONCLUSTERED INDEX [IX_27777] ON [dbo].[tblInvoice]
(
[CompanyID] ASC,
[Month] ASC,
[WithPO] ASC
)
INCLUDE ( [UserID], [docs])
GO
CREATE NONCLUSTERED INDEX [IX_27779] ON [dbo].[tblInvoice]
(
[Month] ASC,
[WithPO] ASC
)
INCLUDE ( [UserID], [CompanyID], [docs])
GO
CREATE NONCLUSTERED INDEX [IX_31155] ON [dbo].[tblInvoice]
(
[UserID] ASC,
[Month] ASC
)
INCLUDE ( [CompanyID], [docs])
GO
CREATE NONCLUSTERED INDEX [IX_tblInvoice_PostingDate] ON [dbo].[tblInvoice]
(
[PostingDate] ASC
)
GO



I found that the 2-bite (select count(*) then select) method was most efficient.

-- select count
Table 'tblInvoice'. Scan count 1, logical reads 3855, physical reads 3, read-ahead reads 3851, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 156 ms, elapsed time = 810 ms.
-- select
(10 row(s) affected)
Table 'tblInvoice'. Scan count 1, logical reads 41, 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 = 1938 ms, elapsed time = 688 ms.

-- double row_number
(10 row(s) affected)
Table 'tblInvoice'. Scan count 4, logical reads 4251, 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 = 11406 ms, elapsed time = 6212 ms.


However, if I add an index as follows.

CREATE NONCLUSTERED INDEX [IX_test1] ON [dbo].[tblInvoice]
(
[CompanyID] ASC,
[Month] DESC,
[DocumentNumber] ASC
)



The IO improves.

Table 'tblInvoice'. Scan count 1, logical reads 2832, physical reads 0, read-ahead reads 2821, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 156 ms, elapsed time = 350 ms.

(10 row(s) affected)
Table 'tblInvoice'. Scan count 1, logical reads 239, 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 = 94 ms, elapsed time = 103 ms.

(10 row(s) affected)
Table 'tblInvoice'. Scan count 1, logical reads 2832, 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.

SQL Server Execution Times:
CPU time = 8345 ms, elapsed time = 4556 ms.


Unfortunately, the CPU and Elapsed times are still worse. So it seems it's not a panacea for all ills.

Still a very interesting technique which is worth trying. Thanks for the article.

Derek
Scott Monnig
Scott Monnig
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 12
Gang,

I suggest that Peso's first response at 9:21 AM where a simple count(*) of the cols table be seriously considered.

This approach, which we employ as well, serves the need well when you consider that you have already queried the table/tables in question in the "cols" portion. No need to go back again just for the count.

Consider that if you have developed a robust data layer in an application where ad-hoc querying is supported, the Row_Number() function may take multiple columns into consideration. The approach presented requires you to reverse the sort direction of each criteria. Peso's (and our employed approach) does not require you to do that. Simply query as you are required to in the "cols" or similar CTE expression and Count(*) that for your total rows which match your criteria.
werner.broser
werner.broser
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 Visits: 166
Nice method, but works only for unique fields!
johanp.za
johanp.za
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 16
Maybe not the best but I've been using this for years.
Keen to try the methods posted in the article.


USE [*********]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[ReturnRows]
(
@SQL nvarchar(4000),
@Page int,
@RecsPerPage int,
@ID varchar(255),
@Sort varchar(255)
)
AS
DECLARE @Str nvarchar(4000)
SET @Str='Select TOP '+CAST(@RecsPerPage AS varchar(20))+' * FROM ('+@SQL+') T WHERE T.'+@ID+' NOT IN
(select TOP '+CAST((@RecsPerPage*(@Page-1)) AS varchar(20))+' '+@ID+' FROM ('+@SQL+') T9 ORDER BY '+@Sort+') order by '+@Sort
PRINT @Str
EXEC sp_ExecuteSql @Str


ryan_ternier
ryan_ternier
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 13
Thanks for the article. I'm working on a Query that returns a list of users, but I've never been able to do it with the ROW_NUMBER() because of the orderby. Here's what i have that works - SQL 2000 way. Is there any better way of writing this?

ALTER PROCEDURE [dbo].[procSelectUsers] (
@p_vchLetterFilter VARCHAR(1),@p_vchOrderBy VARCHAR(20), @p_vchSortDirection VARCHAR(20), @p_vchQuickSearch VARCHAR(50), @p_intPage int, @p_intPageSize int
)

AS
BEGIN
DECLARE @TempTable TABLE(RowID INT IDENTITY, UserID int)

INSERT INTO @TempTable(UserID)
SELECT intUserID
FROM tblUsers U, tblSecurityRoles SR, tblWorkgroups W
WHERE U.sysDateDeleted = 0 AND SR.intSecurityRoleID = U.intSecurityRoleID
AND W.intWorkgroupID = U.intWorkgroupID
AND vchLastName LIKE @p_vchLetterFilter + '%'
AND vchLastName LIKE '%' + @p_vchQuickSearch + '%'
ORDER BY
CASE
WHEN @p_vchOrderBy = 'last' AND @p_vchSortDirection = 'ASC' THEN (RANK() OVER (ORDER BY vchLastName ASC, vchFirstName ASC))
WHEN @p_vchOrderBy = 'last' AND @p_vchSortDirection = 'DESC' THEN (RANK() OVER (ORDER BY vchLastName DESC, vchFirstName DESC))
WHEN @p_vchOrderBy = 'first' AND @p_vchSortDirection = 'ASC' THEN (RANK() OVER (ORDER BY vchFirstName ASC, vchLastName ASC))
WHEN @p_vchOrderBy = 'first' AND @p_vchSortDirection = 'DESC' THEN (RANK() OVER (ORDER BY vchFirstName DESC, vchLastName DESC))
WHEN @p_vchOrderBy = 'username' AND @p_vchSortDirection = 'ASC' THEN (RANK() OVER (ORDER BY vchUserName DESC))
WHEN @p_vchOrderBy = 'username' AND @p_vchSortDirection = 'DESC' THEN (RANK() OVER (ORDER BY vchUserName ASC))
WHEN @p_vchOrderBy = 'security' AND @p_vchSortDirection = 'ASC' THEN (RANK() OVER (ORDER BY vchRoleTitle DESC, vchLastName DESC, vchFirstName DESC))
WHEN @p_vchOrderBy = 'security' AND @p_vchSortDirection = 'DESC' THEN (RANK() OVER (ORDER BY vchRoleTitle ASC, vchLastName ASC, vchFirstName ASC))
WHEN @p_vchOrderBy = 'workgroup' AND @p_vchSortDirection = 'ASC' THEN (RANK() OVER (ORDER BY vchDescription DESC, vchLastName DESC, vchFirstName DESC))
WHEN @p_vchOrderBy = 'workgroup' AND @p_vchSortDirection = 'DESC' THEN (RANK() OVER (ORDER BY vchDescription ASC, vchLastName ASC, vchFirstName ASC))
END

SELECT UserID, vchFirstName, vchLastName,vchUserName, vchPassword,
vchEmail, IsNull(U.intSecurityRoleID,0) as intSecurityRoleID,
IsNull(U.intWorkgroupID,0) as intWorkgroupID, vchInitials, vchBadgeNumber,
SR.vchRoleTitle AS vchSecurityRole, W.vchDescription AS vchWorkgroupDesc
FROM @TempTable T, tblUsers U, tblSecurityRoles SR, tblWorkgroups W
WHERE U.sysDateDeleted = 0 AND SR.intSecurityRoleID = U.intSecurityRoleID
AND T.UserID = U.intUserID
AND W.intWorkgroupID = U.intWorkgroupID
AND vchLastName LIKE @p_vchLetterFilter + '%'
AND vchLastName LIKE '%' + @p_vchQuickSearch + '%'
AND t.RowID BETWEEN ((@p_intPage - 1) * @p_intPageSize + 1) AND (@p_intPage * @p_intPageSize)

END /* CREATE PROCEDURE procSelectAllUsers */





This is what I had before, but look at how it printed out the results - so it wouldn't work the way I needed:


ALTER PROCEDURE [dbo].[procSelectUsers] (
@p_vchLetterFilter VARCHAR(1),@p_vchOrderBy VARCHAR(20), @p_vchSortDirection VARCHAR(20), @p_vchQuickSearch VARCHAR(50), @p_intPage int, @p_intPageSize int
)

AS
BEGIN
DECLARE @startRowIndex INT
SET @startRowIndex = (@p_intPage * @p_intPageSize) + 1;


WITH Users as (
SELECT ROW_NUMBER() OVER (ORDER BY intUserID ASC) as Row,
intUserID, vchFirstName, vchLastName,vchUserName, vchPassword,
vchEmail, IsNull(U.intSecurityRoleID,0) as intSecurityRoleID,
IsNull(U.intWorkgroupID,0) as intWorkgroupID, vchInitials, vchBadgeNumber,
SR.vchRoleTitle AS vchSecurityRole, W.vchDescription AS vchWorkgroupDesc
FROM tblUsers U, tblSecurityRoles SR, tblWorkgroups W
WHERE U.sysDateDeleted = 0 AND SR.intSecurityRoleID = U.intSecurityRoleID
AND W.intWorkgroupID = U.intWorkgroupID
AND vchLastName LIKE @p_vchLetterFilter + '%'
AND vchLastName LIKE '%' + @p_vchQuickSearch + '%'
)



SELECT
intUserID, vchFirstName, vchLastName,vchUserName, vchPassword,
vchEmail, intSecurityRoleID, intWorkgroupID, vchInitials, vchBadgeNumber,
vchSecurityRole, vchWorkgroupDesc
FROM Users
WHERE Row BETWEEN @startRowIndex AND @startRowIndex + @p_intPageSize - 1
ORDER BY
CASE
WHEN @p_vchOrderBy = 'last' AND @p_vchSortDirection = 'ASC' THEN (RANK() OVER (ORDER BY vchLastName ASC, vchFirstName ASC))
WHEN @p_vchOrderBy = 'last' AND @p_vchSortDirection = 'DESC' THEN (RANK() OVER (ORDER BY vchLastName DESC, vchFirstName DESC))
WHEN @p_vchOrderBy = 'first' AND @p_vchSortDirection = 'ASC' THEN (RANK() OVER (ORDER BY vchFirstName ASC, vchLastName ASC))
WHEN @p_vchOrderBy = 'first' AND @p_vchSortDirection = 'DESC' THEN (RANK() OVER (ORDER BY vchFirstName DESC, vchLastName DESC))
WHEN @p_vchOrderBy = 'username' AND @p_vchSortDirection = 'ASC' THEN (RANK() OVER (ORDER BY vchUserName DESC))
WHEN @p_vchOrderBy = 'username' AND @p_vchSortDirection = 'DESC' THEN (RANK() OVER (ORDER BY vchUserName ASC))
WHEN @p_vchOrderBy = 'security' AND @p_vchSortDirection = 'ASC' THEN (RANK() OVER (ORDER BY vchSecurityRole DESC, vchLastName DESC, vchFirstName DESC))
WHEN @p_vchOrderBy = 'security' AND @p_vchSortDirection = 'DESC' THEN (RANK() OVER (ORDER BY vchSecurityRole ASC, vchLastName ASC, vchFirstName ASC))
WHEN @p_vchOrderBy = 'workgroup' AND @p_vchSortDirection = 'ASC' THEN (RANK() OVER (ORDER BY vchWorkgroupDesc DESC, vchLastName DESC, vchFirstName DESC))
WHEN @p_vchOrderBy = 'workgroup' AND @p_vchSortDirection = 'DESC' THEN (RANK() OVER (ORDER BY vchWorkgroupDesc ASC, vchLastName ASC, vchFirstName ASC))
END

END /* CREATE PROCEDURE procSelectAllUsers */



EXEC [procSelectUsers] '', '', 'ASC', '', 0, 6

A1
A2
B1
B2
C1
C2


EXEC [procSelectUsers] '', '', 'ASC', '', 1, 6

A3
A4
B3
B4
C3
C4


Results show an example of the output - not the actual output
Charles Cherry
Charles Cherry
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 68
I just read today that ADO.Net Data Services 1.5 CTP (coming out in a few months) will have built-in support for server-side data paging.

http://blogs.msdn.com/astoriateam/archive/2009/03/01/announcing-ado-net-data-services-v1-5-ctp1.aspx
Dale Allen
Dale Allen
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 13
I agree with Peso, tried the holy grail method and it took my procedure from 7 (using the count(*) method) to 12 seconds to execute. Those 5 extra seconds would unfortunately leave unhappy customer, nice idea though!
daninmanchester
daninmanchester
SSC-Enthusiastic
SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)

Group: General Forum Members
Points: 164 Visits: 58
hhcosmin, I agree with what you say the caching policy needs to be defined and suitable. It can be dynamic these days and linked to the data but a lot of the time for what I do I can cache a set of ID’s for the duration so it becomes very cheap.

One thing that frustrates me about some sites is where the data does change and paging just becomes messy and isn’t always the best way to present it. For example page 1 becomes page 2 because the dataset has changed and so when I click next page I get half of what I was just looking at on page 1 and the first half of what was page 2 etc. Or you go back page and what was page 2 isn’t page 2 anymore.

I’m not quite sure I understand how the last page becomes very expensive using this approach as once the initial query has been performed anything else is PK based. Unless you are talking about having to read from the beginning of say 1000000 PK’s to the end as appose to the current page? Which is true, that would be a nightmare scenario!

So what you say about very large datasets is true and I would typically limit the results set to 1000 or at the extreme 5000 records using the SQL command. If the user wants to go beyond this it does make it complicated but I can’t remember the last time I went beyond page three of any search.

Typically in my experience if a user gets more than a handful of pages they are likely to want to refine their search or maybe sort it to get the records they want in the top pages. Enticing a user down this road is pretty easy….. “you got over 1000 records … try refining your search below!” then give them a bunch of useful refinements.

Obviously every solution has its pros and cons and I wouldn’t present any one as the holy grail as every application has its own requirements.

Dan Bayley
affordable website design UK
Free Google and Yahoo sitemap generator
André Cardoso
André Cardoso
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1375 Visits: 372
Another alternative that I currently have (I haven't measured/compared it) is to use a COMPUTE clause (if the client can cope with multiple result sets).
select a, b, c
from tableX
where a = 1
compute count(a)

The best part is that it works in .NET 1.1 and SQL 2000 (most of the systems I support) Smile

Re-checking: the compute that I used was for obtaining the total of the result set (that was paginated). For pagination it's not so interesting because the number of rows retrieved is controlled by the client (the count should be for the whole table/view).

Select top 200 a, b, c
from tableX
where a = 1
compute sum(c)



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