An interesting situation you may want to check:
The way cursors work (have always worked) is that you ask for a cursor of a
specific type. If the server can't give you that type of cursor, it gives
you a less functional cursor ("implicit conversion"). You can test which
kind of cursor you received after the fact, but I suspect most people don't
do so. Not only that, many people seem to ask for cursors with more
functionality then they really need. So lets say in 6.5 you asked for
(perhaps without realizing it) a dynamic cursor, couldn't get one for some
reason, and got a static cursor instead. Lets say that you never checked to
see what kind of cursor it was, but you also never used any functionality
that the static cursor couldn't provide. So your app worked correctly. Now
7.0 comes along and has removed the restriction that caused the implicit
conversion to occur in 6.5, so you are getting the dynamic cursor you
actually requested. Well, what if the cursor you asked for is actually
slower than the cursor you were unintentionally getting in 6.5?
This is not a far-fetched scenario. Here is a statement from the 7.0 Books
Online about a 6.5 limitation that is removed in 7.0:
"If a dynamic cursor is requested and the Transact-SQL statement contains an
ORDER BY that does not match an index or subquery, the cursor is converted
to a keyset-driven or static cursor. If all the tables have a unique index,
but no index that covers the ORDER BY, the cursor is converted to a
keyset-driven cursor. If at least one table has no index that covers the
ORDER BY and at least one has no unique index (not necessarily the same
table), the cursor is converted to static. "
So in 6.5 it could be that you were asking for a dynamic cursor and getting
a keyset or static. In 7.0 you get the dynamic cursor you asked for, but
walking whichever index is selected turns out to be slower than the keyset
or static cursor population. You could test this by modifying your code to
explicitly request keyset or static (and try both, since you don't know
which you were getting in 6.5).
All opinions expressed here are mine and mine alone
> We have an c/s app which uses sql7 as backend(upgraded from sql6.5). We
> use stored procs with cursors .. and 'order by' clause for results. The
> performance for these stored procs has been unbearably slow. What used
> to take miniutes with 6.5 now takes hours (when 200,000-row tables are
> involved). The performance is acceptable when 'order by' is not used.
> We have installed sp1 but there is no apprarent improvement.
> Has anyone else encountered the same problem?
> Are we talking about waiting for sp2 or sp3 to fix this?