help 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 2005 Forums

Transact-SQL (2005)

help optimizing paging query

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-11-07 : 03:11:49

Hi,

I have the following query below that has been running fine,
but looks like its starting to slow down because of more rows in the table.

Just by looking at this query I'm wondering if anyone can tell me some tips on optimizing it.

I have about 10k rows in "tblMedia" and about 50k in "tblUserVotes" (both tables are growing..)

My concern is that

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

Is joining the whole table onto the other whole table, rather than just whats needed. I am now running SQL2005 and I have heard there are some better paging strategies built in? Is this true?

Any recommendations on the best way to approach this?

Much appreciated!!

Thanks,
mike123



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
 
WHERE m.activeStatus = 1 

GROUP BY 
 	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




CREATE TABLE [dbo].[tblUserVote](
	[voteID] [int] IDENTITY(1,1) NOT NULL,
	[voterID] [int] NOT NULL,
	[mediaID] [int] NOT NULL,
	[voteDate] [smalldatetime] NOT NULL,
	[points] [tinyint] NOT NULL
) ON [PRIMARY]



CREATE TABLE [dbo].[tblMedia](
	[mediaID] [int] IDENTITY(1,1) NOT NULL,
	[categoryID] [tinyint] NOT NULL,
	[mediaTypeID] [tinyint] NOT NULL,
	[serverID] [int] NOT NULL,
	[mediaHeight] [int] NOT NULL,
	[mediaWidth] [int] NOT NULL,
	[mediaFileName] [varchar](100) NOT NULL,
	[physicalSizeKB] [int] NOT NULL,
	[pageName] [varchar](100) NOT NULL,
	[mediaTitle] [varchar](200) NOT NULL,
	[mediaDesc] [varchar](500) NOT NULL,
	[metaTitle] [varchar](200) NOT NULL,
	[metaDesc] [varchar](200) NOT NULL,
	[metaKeywords] [varchar](200) NOT NULL,
	[datePosted] [datetime] NOT NULL,
	[dateRelease] [datetime] NOT NULL,
	[activeStatus] [tinyint] NOT NULL
) ON [PRIMARY]

GO

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-07 : 03:17:48

http://www.sqlteam.com/article/server-side-paging-using-sql-server-2005

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-07 : 03:28:33

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

E 12°55'05.25"
N 56°04'39.16"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-11-07 : 05:16:01

thanks guys, so it definately looks like there is room to improve..
I just read over these 2 links and it looks like these are 2 slightly different strategies.. for my situation does anyone have any input on the better route to go ? also, performance wise, is this strategy alot better? should I be looking at upgrading all my sql2000 queries to this style?

much appreciated once again,

mike123

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-11-08 : 03:45:06

I came up with this query, based off the first link suggested. It's working but I am not sure how to add one small piece of functionality. In my previous SPROC I had a a column brought back called "moreRecords". What wouuld be the best way to add this?

Also, I'm not even sure that this is the right approach because of the "COUNT(UV.mediaID) AS votes " and "LEFT OUTER JOIN tblUserVote UV ON UV.mediaID = M.mediaID"

I'm trying to determine if I just converted this same problem to sql2005? I'm looking at the execution plans in query analyzer, and the new query does seem much simpler. I'd like to be able to see some cold hard read/write/execution time numbers tho. Would this best be done by running another trace when I get this one running live instead of old one? Is there a better way?

Peso's link seems more suited to my specific problem, altho I have still found this first article very helpful. I plan to convert all my regular temp table paging SPROCS to this style (the ones without the JOINS and counts) Do you think I will see a performance improvement?

Any suggestions very much appreciated

Thanks once again!
mike123


CREATE PROCEDURE [dbo].[select_MediaList_Page_Newest_2005] 
	(
	 @Page smallint,
	 @RecsPerPage tinyint
	 )

AS SET NOCOUNT ON

DECLARE
--	@PageSize INT,
--	@PageNumber INT,
	@FirstRow INT,
	@LastRow INT

--SELECT	@PageSize = 6,
--	@PageNumber = 1

SELECT	@FirstRow = (@Page - 1) * @RecsPerPage + 1,
	@LastRow = (@Page - 1) * @RecsPerPage + @RecsPerPage ;

WITH Media  AS
(
	SELECT	M.mediaID,  
	pageName, 
	mediaTitle,
	mediaDesc, 
	datePosted,
	COUNT(UV.mediaID) AS votes, 
	SUM ( 
		CASE 
			WHEN points IS NULL 
			THEN 0 
			ELSE points 
		END ) as points,

			ROW_NUMBER() OVER (ORDER BY datePosted DESC) AS RowNumber
	FROM	

	tblMedia M 	LEFT OUTER JOIN tblUserVote UV ON UV.mediaID = M.mediaID
  	WHERE m.activeStatus = 1
 
GROUP BY 
 	M.mediaID, 
	pageName, 
	mediaTitle, 
	mediaDesc, 
	datePosted
)
SELECT	RowNumber, 
	mediaID,  
	pageName, 
	mediaTitle,
	mediaDesc, 
	datePosted,
	votes,
	points

FROM	Media
WHERE	RowNumber BETWEEN @FirstRow AND @LastRow

ORDER BY RowNumber ASC

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-11-10 : 04:07:26

bump for the weekend, anybody able to help out very much appreciated.. still a lil stuck on this one ..
thanks again!
mike123

Kristen
Test

22859 Posts

Posted - 2007-11-10 : 05:38:08

I ahven't read the thread carefully, so just guessing ...

Can you add to the "ROW_NUMBER() OVER (ORDER BY datePosted DESC) AS RowNumber" bit a column for

COUNT(*) OVER (PARTITION BY NULL)

tog et the total number of rows.

Do you need a ";" in front of the "with" ??

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-10 : 15:27:51

It seems with SP2 you do not need (PARTITION BY NULL)!

Just write, COUNT(*) OVER ()

E 12°55'05.25"
N 56°04'39.16"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-11-30 : 16:28:33

thanks peso, was able to get it going like this..

I looked at the execution plans for this new one vs the old one, and they are quite different.

Whats the best way to determine which one is faster? I'm just testing in QA and they are both done in a second, which I'm sure is not ideal.

Also, I have dozens of these SPROCs that are done with the original method posted, is it advisable to update them all to this newer method?

Thanks very much!

mike123



CREATE PROCEDURE [dbo].[select_MediaList_Page_Newest_2005] 
	(
	 @Page smallint,
	 @RecsPerPage tinyint
	 )

AS SET NOCOUNT ON

DECLARE
--	@PageSize INT,
--	@PageNumber INT,
	@FirstRow INT,
	@LastRow INT

--SELECT	@PageSize = 6,
--	@PageNumber = 1

SELECT	@FirstRow = (@Page - 1) * @RecsPerPage + 1,
	@LastRow = (@Page - 1) * @RecsPerPage + @RecsPerPage ;

WITH Media  AS
(
	SELECT	M.mediaID,  
	pageName, 
	mediaTitle,
	mediaDesc, 
	datePosted,
	COUNT(UV.mediaID) AS votes, 
	SUM ( 
		CASE 
			WHEN points IS NULL 
			THEN 0 
			ELSE points 
		END ) as points,

			ROW_NUMBER() OVER (ORDER BY datePosted DESC) AS RowNumber,
			COUNT(*) OVER () - (@page * @RecsPerPage) as MoreRecords
	FROM	

	tblMedia M 	LEFT OUTER JOIN tblUserVote UV ON UV.mediaID = M.mediaID
  	WHERE m.activeStatus = 1
 
GROUP BY 
 	M.mediaID, 
	pageName, 
	mediaTitle, 
	mediaDesc, 
	datePosted
)
SELECT	RowNumber, 
	mediaID,  
	pageName, 
	mediaTitle,
	mediaDesc, 
	datePosted,
	votes,
	points,
	MoreRecords

FROM	Media
WHERE	RowNumber BETWEEN @FirstRow AND @LastRow

ORDER BY RowNumber ASC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources