For Fast Enterprise paging, Use a Cursor

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

For Fast Enterprise paging, Use a Cursor

Author

Topic

JBelthoff
Posting Yak Master

173 Posts

Posted - 2005-06-09 : 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

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-09 : 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.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-06-09 : 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

173 Posts

Posted - 2005-06-09 : 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
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-09 : 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.

JBelthoff
Posting Yak Master

173 Posts

Posted - 2005-06-09 : 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
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-09 : 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

180 Posts

Posted - 2005-06-09 : 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

7423 Posts

Posted - 2005-06-09 : 21:33:47

quote:
Originally posted by raclede

well... paging comparison you can take a look at this one

http://www.aspfaq.com/show.asp?id=2120

hmmm .. the "Chris Hohmann" method sure looks familiar ....

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25496

- Jeff

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-06-10 : 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 Post

Posted - 2005-07-01 : 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?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources