Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

SQL Server 2005 Paging – The Holy Grail Expand / Collapse
Author
Message
Posted Wednesday, March 11, 2009 6:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 18, 2014 7:51 AM
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.
Post #673174
Posted Wednesday, March 11, 2009 6:59 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 7:14 AM
Points: 1,344, Visits: 1,983
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
Post #673204
Posted Wednesday, March 11, 2009 7:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 01, 2009 5:04 AM
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.

Post #673210
Posted Wednesday, March 11, 2009 7:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 05, 2012 3:10 AM
Points: 101, Visits: 166
Nice method, but works only for unique fields!
Post #673221
Posted Wednesday, March 11, 2009 8:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 13, 2013 7:49 AM
Points: 1, Visits: 12
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

Post #673310
Posted Wednesday, March 11, 2009 8:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 12, 2009 9:01 AM
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
Post #673337
Posted Wednesday, March 11, 2009 8:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 05, 2012 10:20 AM
Points: 29, Visits: 65
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

Post #673343
Posted Wednesday, March 11, 2009 11:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 13, 2012 10:25 AM
Points: 1, 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!
Post #673506
Posted Wednesday, March 11, 2009 11:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 03, 2012 9:35 AM
Points: 46, 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
Post #673578
Posted Wednesday, March 11, 2009 12:45 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:21 AM
Points: 1,298, Visits: 241
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) :)

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)



Post #673622
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse