SQL Server 2005 and Cursors

[mrcraze]

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.

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

[Erland Sommarskog]

mrcraze (st*********@hotmail.com) writes:

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.
...
DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
DYNAMIC? Is there any particular reason you are using a dynamic cursor?
Dynamic cursors makes me very nervous. I always run my cursors
INSENSITIVE. Dynamic is not what you use for good performance.

It also seems like a bad idea for paging. When paging, you want a consistent
result. That is, if I perform a search, and my search matches say 59
objects. What I really want is to view all 59 objects at once. However,
there are many web authors out there, who think that I cannot digest more
than 10 at a time. Anyway, I first get to see hits 1-10. Then I expect
to see hits 11-20 next time. But if you rerun the query each time,
I might get to see hits 12-21, because a new item is now #5. If you use a
dynamic cursor, things gets even more complicated.
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.

As I said, dynamic cursors always make me nervous, and I have more
than once run into issues where dynamic cursors have lead to query
plans from hell.

I tried your example in a translated version on in an exteneded version
of the Northwind database, but I did notice any difference between
SQL 2000 and SQL 20005. But without knowing your table or what
"criteria = TRUE" stands for it's difficult to try to recreate the
problem without further information.

A good source on different ways to do paging is
http://www.aspfaq.com/show.asp?id=2120.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx