Recordset Paging Performance - 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)

Recordset Paging Performance

Author

Topic

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-09-20 : 13:08:12

Ok so I'm just wondering if I could get some opinions on this method of paging search results ... ignore the actual search part I just used it for testing to see if it works ... how would you guys rank this on performance wise? I'm looking to use a non-dynamic sql version of paging ... i've heard people say you can do this with temp tables ... well anything you guys might change to boost performance or anything you might want to note about possible down falls of this method?


DECLARE @RowsPerPage INT, @Pages INT, @Page INT
DECLARE @StartRow INT, @EndRow INT
DECLARE @Rows TABLE (RowID INT NOT NULL IDENTITY(1,1), PKID INT)

SET @RowsPerPage = 20
SET @Pages = NULL
SET @Page = 5

SET @StartRow = @RowsPerPage * (@Page - 1) + 1
SET @EndRow = @StartRow + @RowsPerPage - 1

INSERT INTO @Rows (PKID)
 SELECT ProjectID FROM Projects 
  WHERE Title LIKE 'WWW:%' ORDER BY Title

SELECT @Pages = (COUNT(*) / @RowsPerPage) + 1 FROM @Rows

SELECT ProjectID, Title 
  FROM Projects 
       INNER JOIN @Rows ON ProjectID = PKID 
                  AND RowID BETWEEN @StartRow AND @EndRow 
 ORDER BY Title

SELECT @Pages AS Pages, @StartRow AS StartRow, @EndRow AS EndRow

Here's the execution plan I'm getting for the Page count and the actual displaying of the rows...

quote:

SELECT @Pages = (COUNT(*) / @RowsPerPage) + 1 FROM @Rows
|--Compute Scalar(DEFINE:([Expr1003]=[Expr1002]/[@RowsPerPage]+1))
|--Compute Scalar(DEFINE:([Expr1002]=Convert([Expr1004])))
|--Stream Aggregate(DEFINE:([Expr1004]=Count(*)))
|--Table Scan(OBJECT:(@Rows))

SELECT ProjectID, Title FROM Projects INNER JOIN @Rows ON ProjectID = PKID AND RowID BETWEEN @StartRow AND @EndRow ORDER BY Title
|--Sort(ORDER BY:([Projects].[Title] ASC))
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([stars].[dbo].[Projects]))
|--Nested Loops(Inner Join, OUTER REFERENCES:(@Rows.[PKID]))
|--Table Scan(OBJECT:(@Rows), WHERE:(@Rows.[RowID]>=[@StartRow] AND @Rows.[RowID]<=[@EndRow]))
|--Index Seek(OBJECT:([stars].[dbo].[Projects].[PK__Projects__6FE99F9F]), SEEK:([Projects].[ProjectID]=@Rows.[PKID]) ORDERED FORWARD)

Could I get better performance if I used a temporary table instead of a table variable ... I'm just wondering because of the table scan on the RowID...

I'm just bored and looking to make searching for my application faster and reduce the server side code and offload it to the sql server...

Edited by - onamuji on 09/20/2002 13:25:20

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources