| Author |
 Topic  |
|
|
JBelthoff
Posting Yak Master
USA
154 Posts |
 Posted - 06/09/2005 : 13:52:31
|
For the fastest paging solution, one must use a cursor! I know what your thinking...
Hogwash! Right?
Well, I would not have believed it myself but I tried it and it indeed works.
First I would like to give credit where it is due by saying that this solutions comes from NOT me but rather from Justin Lovell. His fast paging solution is nothing short of brilliant -- IMHO of course.
Justin's solution can be found on his blog located here: Justin Lovell Enterprise Paging
Here is the data I used for my experiment so you may try it for yourself. Kudos Justin!
--## FIRST Create the Very Large Table
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[VeryLargeTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[VeryLargeTable]
GO
CREATE TABLE [dbo].[VeryLargeTable] (
[VLTID] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LastName] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OtherData] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
--## SECOND Load some data
--## As we are adding 1 million records to the database..
--## please be patient as it could take some time.
--## If you already have a very large table to work from
--## you can use use that instead.
DECLARE @x int
SET @x = 1
WHILE @x < 1000000
BEGIN
INSERT VeryLargeTable ( FirstName, LastName, OtherData )
VALUES ( 'John' + ' ' + CAST ( @x AS varchar(100) ), 'Doe' + ' ' + CAST ( @x AS varchar(100) ), 'YAK Stuff' + ' ' + CAST ( @x AS varchar(100) ) )
SET @x = @x + 1
END
GO
--## THRIRD Check our data
SELECT COUNT(*) FROM VeryLargeTable
SELECT TOP 20 * FROM VeryLargeTable
WHERE VLTID > 375482
GO
--## FOURTH Create our old Slow procedure using a temp var table
CREATE PROCEDURE dbo.TestVeryLargePaging_Slow
@Page int,
@RecsPerPage int,
@TotalPages int OUTPUT
AS
SELECT @TotalPages = ( SELECT COUNT(*)/@RecsPerPage + 1 FROM VeryLargeTable )
DECLARE @TableVar table (
TID int identity(1,1) PRIMARY KEY,
VLTID int NOT NULL,
FirstName varchar(25) NOT NULL,
LastName varchar(25) NOT NULL,
OtherData varchar(25) NOT NULL
)
INSERT INTO @TableVar (VLTID, FirstName, LastName, OtherData)
SELECT VLTID, FirstName, LastName, OtherData
FROM VeryLargeTable
ORDER BY VLTID
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)
SELECT TID, VLTID, FirstName, LastName, OtherData
FROM @TableVar
WHERE TID > @FirstRec AND TID < @LastRec
GO
--## FIFTH Create our Fast Cursor Procedure
CREATE PROCEDURE dbo.TestVeryLargePaging_Fast
@Page int,
@RecsPerPage int,
@TotalPages int OUTPUT
AS
DECLARE @FirstRec int
SELECT @FirstRec = (@Page -1) * @RecsPerPage + 1
DECLARE @RowCount int
SELECT @RowCount = @RecsPerPage
SELECT @TotalPages = ( SELECT COUNT(*)/@RecsPerPage + 1 FROM VeryLargeTable )
DECLARE @PK int
DECLARE @tmpTable TABLE (
PK int NOT NULL PRIMARY KEY
)
DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
SELECT VLTID FROM VeryLargeTable
ORDER BY VLTID
OPEN PagingCursor
FETCH RELATIVE @FirstRec FROM PagingCursor INTO @PK
WHILE (@RowCount <> 0) And (@@Fetch_Status = 0)
BEGIN
INSERT INTO @tmpTable (PK)
VALUES (@PK)
FETCH NEXT FROM PagingCursor INTO @PK
SET @RowCount = @RowCount - 1
END
CLOSE PagingCursor
DEALLOCATE PagingCursor
SELECT VLTID, FirstName, LastName, OtherData
FROM VeryLargeTable lt
JOIN @tmpTable temp ON lt.VLTID = temp.PK
GO
--## LAST Execute the two queries!
--## Open up two tiled windows and run these side by side!
--## 1
DECLARE @Total int
EXEC dbo.TestVeryLargePaging_Slow 6920, 50, @Total OUTPUT
PRINT @Total
--## 2
DECLARE @Total int
EXEC dbo.TestVeryLargePaging_Fast 6920, 50, @Total OUTPUT
PRINT @Total
GO
--## To Clean up the mess
DROP TABLE dbo.VeryLargeTable
DROP PROCEDURE dbo.TestVeryLargePaging_Fast
DROP PROCEDURE dbo.TestVeryLargePaging_Slow
GO
Enjoy everyone and Thank you Justin Lovell!
JB 
|
Edited by - JBelthoff on 06/09/2005 14:18:05
|
|
|
Seventhnight
Flowing Fount of Yak Knowledge
USA
2419 Posts |
Posted - 06/09/2005 : 14:36:12
|
while this may be true... i don't think this qualifies this as the fastest method..
for example, you are testing against a crappy non-cursor paging method. In order to compare, you would need to work out the most efficient non-cursor paging method to compare against.
I believe it is something along the lines of:
Declare @Page int,
@RecsPerPage int,
@execStr varchar(2000)
Set @Page = 6920
Set @RecsPerPage = 50
Declare @tempTable table (pk int)
Set @execStr = 'Select top ' + convert(varchar,@RecsPerPage) +
' pk from (Select top ' + convert(varchar,@RecsPerPage*(@page-1)+1) +
' pk from veryLargeTable Order By pk) A order by pk desc'
Insert into @tempTable
exec(@execStr)
Select *
From veryLargeTable A
Inner Join @execStr B
On A.pk = B.pk
Corey
Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative. |
Edited by - Seventhnight on 06/09/2005 14:36:57 |
 |
|
|
robvolk
SQLTeam MVY/MIA
USA
12242 Posts |
Posted - 06/09/2005 : 14:43:20
|
What exactly is "Enterprise" paging anyway? How does it differ from non-enterprise paging? Do I need the Enterprise Edition of SQL Server to use this cursor? (man, THAT would be a great feature, cursors are only available in Enterprise Edition) 
I guess they don't use indexes in "enterprise" databases either... |
|
|
|
JBelthoff
Posting Yak Master
USA
154 Posts |
Posted - 06/09/2005 : 15:36:02
|
Hi Seventhnight,
I see what you are doing. You replace the cursor with your query.
However, when I run your code I get the following errors.
Server: Msg 197, Level 15, State 1, Line 15
EXECUTE cannot be used as a source when inserting into a table variable.
Server: Msg 137, Level 15, State 1, Line 19
Must declare the variable '@execStr'.
So I changed it to this an ran some more tests.
CREATE PROCEDURE dbo.TestVeryLargePaging_Fast_New
@Page int,
@RecsPerPage int
as
create table #tempTable(pk int)
Declare @execStr varchar(2000)
Set @execStr = 'Select top ' + convert(varchar,@RecsPerPage) +
' VLTID from (Select top ' + convert(varchar,@RecsPerPage*(@page-1)+1) +
' VLTID from veryLargeTable Order By VLTID) A order by VLTID desc'
Insert into #tempTable
exec(@execStr)
Select *
From veryLargeTable A
Join #tempTable B
On A.VLTID = B.pk
drop table #tempTable
go
The results show that on lower pages, 1-5000 your solution is quite a bit faster, but when the page gets higher than about 5000 yours is slightly slower than what I popsted. At least that is what I got.
Thanks for the help, I think I'm going for your solution.
Do you know of a way to use the Table var instead of the TempTable in this instance?
Thanks,
JB
|
|
|
|
Seventhnight
Flowing Fount of Yak Knowledge
USA
2419 Posts |
Posted - 06/09/2005 : 16:10:59
|
Now that I thnik about it... I don't think you can use the table variable. However, you probably could cut out the last step by changing the execstr slightly...
CREATE PROCEDURE dbo.TestVeryLargePaging_Fast_New
@Page int,
@RecsPerPage int
as
Declare @execStr varchar(2000)
Set @execStr = 'Select Z.* From veryLargeTable Z Inner Join (Select top ' + convert(varchar,@RecsPerPage) +
' VLTID from (Select top ' + convert(varchar,@RecsPerPage*(@page-1)+1) +
' VLTID from veryLargeTable Order By VLTID) A order by VLTID desc) Y On Z.pk = Y.pk order By Z.VLTID'
exec(@execStr)
go
Corey
Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative. |
Edited by - Seventhnight on 06/09/2005 16:13:14 |
|
|
|
JBelthoff
Posting Yak Master
USA
154 Posts |
Posted - 06/09/2005 : 16:36:42
|
Hi Corey,
I minor change and now this is screamin'...!
You had + convert(varchar,@RecsPerPage*(@page-1)+1) +
I changed to + convert(varchar,@RecsPerPage*(@page)) +
Like this
ALTER PROCEDURE dbo.TestVeryLargePaging_Fast_New
@Page int,
@RecsPerPage int,
@TotalPages int OUTPUT
as
SELECT @TotalPages = ( SELECT COUNT(*)/@RecsPerPage + 1 FROM VeryLargeTable )
Declare @execStr varchar(2000)
Set @execStr = 'Select Z.* From veryLargeTable Z Inner Join (Select top ' + convert(varchar,@RecsPerPage) +
' VLTID from (Select top ' + convert(varchar,@RecsPerPage*(@page)) +
' VLTID from veryLargeTable Order By VLTID) A order by VLTID desc) Y On Z.VLTID = Y.VLTID order By Z.VLTID'
exec(@execStr)
go
Thank you so much. I have been looking for a way to make large table paging do-able without dumping everything over to a temp.
Justin was brilliant, but you are a genious!
It must be hot down there in SC today huh? It's hot here in CT! Whew!
JB
|
|
|
|
Seventhnight
Flowing Fount of Yak Knowledge
USA
2419 Posts |
Posted - 06/09/2005 : 19:50:07
|
its muggy down here in SC... a little bit of rain to cool us off though
... too bad I can't take all the credit for the solution... I'm sure I've seen it here a few times 
Corey
Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative. |
|
|
|
raclede
Posting Yak Master
Philippines
180 Posts |
Posted - 06/09/2005 : 20:50:39
|
well... paging comparison you can take a look at this one
http://www.aspfaq.com/show.asp?id=2120
"If the automobile had followed the same development cycle as the computer, a Rolls-Royce would today cost $100, get a million miles per gallon, and explode once a year, killing everyone inside. "
 raclede |
|
|
|
jsmith8858
Dr. Cross Join
USA
7402 Posts |
|
|
DonAtWork
Flowing Fount of Yak Knowledge
1442 Posts |
Posted - 06/10/2005 : 10:03:08
|
SO to sum up, Cursor = poo 
*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected) |
|
|
|
hypa
Starting Member
1 Posts |
Posted - 07/01/2005 : 14:34:19
|
quote:
Originally posted by JBelthoff
Hi Corey,
I minor change and now this is screamin'...!
You had + convert(varchar,@RecsPerPage*(@page-1)+1) +
I changed to + convert(varchar,@RecsPerPage*(@page)) +
Like this
ALTER PROCEDURE dbo.TestVeryLargePaging_Fast_New
@Page int,
@RecsPerPage int,
@TotalPages int OUTPUT
as
SELECT @TotalPages = ( SELECT COUNT(*)/@RecsPerPage + 1 FROM VeryLargeTable )
Declare @execStr varchar(2000)
Set @execStr = 'Select Z.* From veryLargeTable Z Inner Join (Select top ' + convert(varchar,@RecsPerPage) +
' VLTID from (Select top ' + convert(varchar,@RecsPerPage*(@page)) +
' VLTID from veryLargeTable Order By VLTID) A order by VLTID desc) Y On Z.VLTID = Y.VLTID order By Z.VLTID'
exec(@execStr)
go
Thank you so much. I have been looking for a way to make large table paging do-able without dumping everything over to a temp.
Justin was brilliant, but you are a genious!
It must be hot down there in SC today huh? It's hot here in CT! Whew!
JB
This method does not appear to retrieve accurate results after page 2, have you tested this? |
|
|
| |
Topic |
|
|
|
For Fast Enterprise paging, Use a Cursor
Forum Locked
