Posted by Erland Sommarskog on 06/23/06 12:43

mrcraze (steve_craze@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]