optimizing paging routine - 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)

optimizing paging routine

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-01-29 : 23:18:52

Hi,

I have a query that runs, with built in paging via temp tables as shown in the query below. I'm not sure if I'm correct in thinking this, but I'm thinking that for the queries in which "@page=1" is passed, we could have a different query that is basically just a SELECT TOP statement, so we don't go thru the temp table process etc. I've never seen this written into a temp table paging solution, I'm wondering if theres a reason for this ?

I guess testing is the only real way to determine. I'm workin a lil rusty and trying to figure out the best way to do it here still. Any help and input much appreciated !!

Thanks very much!!
mike123



CREATE PROCEDURE [dbo].[select_mailbox_paging] 
(
	@userID int,
	@page int,
	@recsPerPage tinyint
)
AS SET NOCOUNT ON


-- 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 #TempMail
(
	ID int IDENTITY,
	messageid int,
	messagefrom int,
	subject varchar(100),
	date datetime,
	nameOnline varchar(50),
	checked tinyint,
)
-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempMail (messageid, messagefrom, subject, date, nameOnline, checked)


SELECT  messageid, messagefrom, subject, M.date, nameonline, checked FROM tblMessage M
JOIN tbluserdetails UD ON M.messagefrom = UD.userid 
WHERE messageTo=@userID AND deletedByRecipient = 0 ORDER BY M.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(messageid) 
	 FROM #TempMail TM 
	 WHERE TM.ID >= @LastRec
	) 
FROM #TempMail
WHERE ID > @FirstRec AND ID < @LastRec
-- Turn NOCOUNT back OFF
SET NOCOUNT OFF

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-06 : 07:07:57

[code]CREATE PROCEDURE dbo.select_mailbox_paging
(
@userID int,
@page int,
@recsPerPage tinyint
)
AS

-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON

SELECT messageid,
messagefrom,
subject,
date,
nameonline,
checked,
MoreRecords = case
when t > (@Page * @RecsPerPage + 1) then t - (@Page * @RecsPerPage + 1)
else 0
end
FROM (
SELECT messageid,
messagefrom,
subject,
M.date,
nameonline,
checked,
row_number() over (order by m.date desc) AS recid,
count(*) over () as t
FROM tblMessage as M
JOIN tbluserdetails as UD ON M.messagefrom = UD.userid
WHERE messageTo = @userID
AND deletedByRecipient = 0
) AS x
where recid > (@Page - 1) * @RecsPerPage
recid <= (@Page * @RecsPerPage + 1)
ORDER BY date DESC[/code]

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-25 : 09:10:43

Did it work out for you?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources