best way to do heavy paging (50k records per page)

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

SQL Server 2000 Forums

SQL Server Development (2000)

best way to do heavy paging (50k records per page)

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-03-22 : 05:09:30

I admit I haven't had to do much paging before and I have been working off one great routine I found for all of my paging and its worked great to date. This SPROC Im working on now I need to make sure its tuned as best as possible as its going to be a pretty intense query.

I'm working on creating a list for google sitemaps. Whether or not your familiar with that or not I need to basically query my database to show all the pages my site displays 50,000 at a time.

Any thoughts on the SPROC I have below? Any better ways I could do this? I currently don't like the fact its bringing the "moreRecords" value as an extra column as there are 50,000 rows of it. I guess I could move it to an output value but I would like to get some feedback on using temp tables method before I start tuning it.

Thanks once again for any input!
mike123

CREATE PROCEDURE dbo.select_sitemap_profiles
(
@Page int,
@recsPerPage int
)
AS
-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON
--Create a temporary table
CREATE TABLE #TempProfiles
(
ID int IDENTITY,
userID int,
NameOnline varchar(15)
)
-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempProfiles (userID, NameOnline)

SELECT userID, nameOnline FROM UserDetails WHERE active = 1 ORDER BY date DESC
-- Find out the first and last record we want

DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)
-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT *,
MoreRecords =
(
SELECT COUNT(*)
FROM #TempProfiles TP
WHERE TP.ID >= @LastRec
)
FROM #TempProfiles
WHERE ID > @FirstRec AND ID < @LastRec
-- Turn NOCOUNT back OFF
SET NOCOUNT OFF

GO

Kristen
Test

22859 Posts

Posted - 2006-03-22 : 05:29:01

I reckon you want to skip the #TempTable and use SET ROWCOUNT to do the paging so that you never have a sub-query.

There's some code liking around on SQLTeam which I'll try to find for you.

(I too have to do a job for Google Sitemaps soon, so I'll be very interested in hearing what works for you in the end)

Kristen