tuning 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

SQL Server Development (2000)

tuning paging query

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-10-11 : 23:45:33

Hi there,

I'm Looking for some general feedback on this SPROC. Is it horribly inefficient? Should I be taking a different approach?

I'm trying to tune my database and this is coming up as one of the heavier READ queries so I'd like to do what I can to make it run better. I'm going to run Index Tuning wizard against the trace table in a few hours when I have some more data as well.

Any suggestions/thoughts much appreciated.

Thanks once again!!
mike123

I did a trace, its running on average 2200 reads, 22 writes, and 550 CPU.

CREATE PROCEDURE dbo.select_MediaList_Page_Newest
(
@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

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-10-12 : 06:44:48

I prefer to use a table variable instead of a temp-table because that doesn't require anything going to disk. You might see some performance gain but not too much compoared to the other stuff you might do here. Do you have a clustered index on UV.mediaID and M.mediaID? What does the query plan look like?

This is probably the part that's killing the procedure:

SELECT *,
MoreRecords =
(
SELECT COUNT(mediaID)
FROM #TempMedia TM
WHERE TM.ID >= @LastRec
)
FROM #TempMedia

Your sub-query will do a table scan for every single row in the temp-table and that's not very efficient. Add a COUNT(*) FROM #TempMedia to a variable after you fill it up and do a SELECT *, @Mycounter... instead. That way you'll know where you are in the paging process (the ID will tell you that) and how many total rows you have. And for even better performance you can pass the total record count back to the procedure when it's run the second time. The recordset count doesn't change between each page transition.

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-13 : 10:21:40

Also refer

http://weblogs.sqlteam.com/jeffs/archive/2003/12/22/672.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources