SQL Server 2005 and Cursors -- sql server (microsoft)

steve_craze NO[at]SPAM hotmail.com
6/22/2006 1:01:13 AM

Hi Everyone!

We are using a cursor for paging results in SQL server, mainly due to
the performance gains achieved when working with large results sets.
We have found this to be of great benefit when working with SQL Server
2000, but have run into major problems when using it on SQL Server
2005. Basically, it runs a cursor and inserts primary keys into a
temporary table, then inner joins that table to the original table
selecting all the presentation data columns required.

The query goes like this:

DECLARE @PageSize int
SET @PageSize = 10

DECLARE @PK int
DECLARE @tblPK TABLE (
PK int NOT NULL PRIMARY KEY
)
DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
SELECT KeyID FROM JobTable WHERE KeyID IN(SELECT KeyID FROM JobTable
WHERE Criteria = TRUE) ORDER BY JobTable.KeyID
OPEN PagingCursor
FETCH RELATIVE 1 FROM PagingCursor INTO @PK
SET NOCOUNT ON
WHILE @PageSize > 0 AND @@FETCH_STATUS = 0
BEGIN
INSERT @tblPK (PK) VALUES (@PK)
FETCH NEXT FROM PagingCursor INTO @PK
SET @PageSize = @PageSize - 1
END
CLOSE PagingCursor
DEALLOCATE PagingCursor

SELECT ResultsFields FROM JobTable INNER JOIN @tblPK tblPK ON
JobTable.KeyID = tblPK.PK WHERE Criteria = TRUE ORDER BY
JobTable.KeyID

I know this doesn't look as optimised as it should but there is a lot
happening under the hood to get it to this point. This aside, there
must be a reason why performance suffers so much with SQL 2005?
Inserting a print statement into the cursor loop outputting the date
stamp showed that each iteration was taking approx 4.5 seconds. This
is a problem we never experienced in SQL Server 2000.

Any help would be greatly appreciated!

Thanks in advance,
Steve

Mike C#
6/22/2006 10:55:23 PM

Why not use the ROW_NUMBER() function instead of that cursor to page?
Something like this (untested):

WITH PagingQuery AS
(
SELECT ROW_NUMBER()
OVER (ORDER BY KeyId) AS Rank, ResultFields
FROM JobTable
)
SELECT Rank, ResultFields
FROM PagingQuery
WHERE Rank >= 10 AND Rank <= 19;

[quoted text, click to view]

sql server (microsoft) : SQL Server 2005 and Cursors