optimizing paging query - SQL Server Forums

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

Transact-SQL (2000)

optimizing paging query

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2005-05-31 : 20:37:55

Hi,

I have just created the following query below. Its a paging query but I am questioning its efficiency for when the first page is requested.

Let's say I pass the parameters '1','20'

Wouldn't it be better if the SPROC determined that since its the first page, theres no need to do the temp table etc. Rather it would just select the top @recsperpage if @page=1

What do you think? The reason Im concerned is because I believe probably 90% the time this query is run will be just the first page.

Any thoughts? I'm used to VB so I get really bad at control structures in t-sql, any help on how to do this is also greatly appreciated.

Thanks alot once again!
mike123

CREATE PROCEDURE dbo.select_MediaList_Page
(
@Page smallint,
@RecsPerPage tinyint
)
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 #TempMedia
(
ID int IDENTITY,
mediaID int,
pageName varchar(100),
mediaTitle varchar(200),
mediaDesc varchar(500),
datePosted datetime,
votes int,
points int
)
-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempMedia (mediaID, pageName, mediaTitle, mediaDesc, datePosted, votes, points)

SELECT
M.mediaID,
-- M.categoryID,
-- Cat.categoryDesc,
pageName,
mediaTitle,
mediaDesc,
datePosted,
COUNT(UV.mediaID) AS votes,
SUM (
CASE
WHEN points IS NULL
THEN 0
ELSE points
END ) as points
FROM
tblMedia M
LEFT OUTER JOIN tblUserVote UV ON UV.mediaID = M.mediaID
--LEFT OUTER JOIN tblCategories Cat ON M.categoryID = cat.categoryID

WHERE m.activeStatus ='1'

GROUP BY
-- M.categoryID,
--Cat.categoryDesc,
M.mediaID,
pageName,
mediaTitle,
mediaDesc,
datePosted
ORDER BY
datePosted 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(mediaID)
FROM #TempMedia TM
WHERE TM.ID >= @LastRec
)
FROM #TempMedia
WHERE ID > @FirstRec AND ID < @LastRec
-- Turn NOCOUNT back OFF
SET NOCOUNT OFF

GO

raclede
Posting Yak Master

180 Posts

Posted - 2005-05-31 : 20:56:57

since you are creating a pagination... please read the article below, it shows different kind of pagination and determining the fastest approach..

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

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-05-31 : 21:09:53

I'd leave it as it is Mike. But to optimise the site, I'd cache the output of a "default" request (i.e. page 1, 20 recs per page).

Damian
Ita erat quando hic adveni.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2005-05-31 : 21:30:33

thanks damian ! I will once again take your advice ! ;)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources