Derived table stored procedure or ROWCOUNT

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)

Derived table stored procedure or ROWCOUNT

Author

Topic

monfu
Yak Posting Veteran

81 Posts

Posted - 2008-09-18 : 06:25:53

Dear All,

I have a dilemma. I wish that my stored procedure is as performant as possible.

So I created 2 stored procedures, which are retreiving the exact same data. One is using a derived table and one is using the ROWCOUNT.

Here are the 2 examples:-

Example 1

BEGIN
WITH Resources AS
(
SELECT DISTINCT resourceID, ROW_NUMBER() OVER (ORDER BY p.pageId) AS RowNum .........
FROM dbo.PageResources AS pr INNER JOIN dbo.Pages ....
WHERE .....
)
SELECT * FROM Resources
WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1
AND @PageNum * @PageSize
ORDER BY PageID ASC

Example 2

BEGIN
SET NOCOUNT ON
SET ROWCOUNT @PageSize
BEGIN
WITH Resources AS
(
SELECT DISTINCT resourceID, ROW_NUMBER() OVER (ORDER BY p.pageId) AS RowNum .........
FROM dbo.PageResources AS pr INNER JOIN dbo.Pages ....
WHERE .....
)
SELECT * FROM Resources
WHERE (@PageSize = 0)
OR
( RowNum BETWEEN (@PageNum - 1) * @PageSize + 1
AND @PageNum * @PageSize)
ORDER BY PageID ASC
END
END

The time to execute these stored procedures is almost the same

The derived table stored procedure Execution Time is 851ms while the ROWCOUNT stored procedure is 857ms.

I was expecting the derived table stored proc to take much longer, since as far as I know, its selecting all the data before returning the output recordset.

Or am I wrong?

Any help is very much appreciated since i am quite new to performance testing

Thanks a lot

Johann

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-18 : 06:38:44

Why do you want CTE at all? you can simply generate the rownumber inside a derived table itself.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources