Poor performance when using Transact SQL cursor

After a recent upgrade from SQL Server 2000 to SQL Server 2005, we get
problems with certain queries using a Transact SQL cursor.
I have noticed the following:
Table with a smaller number of rows.
declare cursor completes normally.
open cursor completes normally
fetch cursor retrieves the first row from the table.
Table with a larger number of rows.
declare cursor completes normally.
open cursor builds a temporary table with information about all the rows
matching the seek conditions. (This can take some time, depending on the
number of rows)
fetch cursor retrives a row from the table, based on values from the first
row in the temporary table.
This behaviour is undesirable because the application may cancel the current
query, do something else and start a new query on the same table. This
creates a lot of overhead.
I have done several tests, and I am sure that the change in behaviour is not
governed by the number of rows returned, but solely on the number of rows in
the table. Setting the conditions such that now rows will meet the conditions
will still show execute as described above.
Can anyone tell me why it has changed, and how I can get the "old" behaviour
back, or just point me to a place where it is described.
Thanks in advance.Did you remember to update stats on all tables with FULLSCAN when you did
the migration? Also, do you really need a cursor to do what you need?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"ErikE" <ErikE@.discussions.microsoft.com> wrote in message
news:4CB99F77-9C10-4FC6-BDFF-98C1B8A022FB@.microsoft.com...
After a recent upgrade from SQL Server 2000 to SQL Server 2005, we get
problems with certain queries using a Transact SQL cursor.
I have noticed the following:
Table with a smaller number of rows.
declare cursor completes normally.
open cursor completes normally
fetch cursor retrieves the first row from the table.
Table with a larger number of rows.
declare cursor completes normally.
open cursor builds a temporary table with information about all the rows
matching the seek conditions. (This can take some time, depending on the
number of rows)
fetch cursor retrives a row from the table, based on values from the first
row in the temporary table.
This behaviour is undesirable because the application may cancel the current
query, do something else and start a new query on the same table. This
creates a lot of overhead.
I have done several tests, and I am sure that the change in behaviour is not
governed by the number of rows returned, but solely on the number of rows in
the table. Setting the conditions such that now rows will meet the
conditions
will still show execute as described above.
Can anyone tell me why it has changed, and how I can get the "old" behaviour
back, or just point me to a place where it is described.
Thanks in advance.|||What kind of cursor did you declare?
Linchi
"ErikE" wrote:
> After a recent upgrade from SQL Server 2000 to SQL Server 2005, we get
> problems with certain queries using a Transact SQL cursor.
> I have noticed the following:
> Table with a smaller number of rows.
> declare cursor completes normally.
> open cursor completes normally
> fetch cursor retrieves the first row from the table.
> Table with a larger number of rows.
> declare cursor completes normally.
> open cursor builds a temporary table with information about all the rows
> matching the seek conditions. (This can take some time, depending on the
> number of rows)
> fetch cursor retrives a row from the table, based on values from the first
> row in the temporary table.
> This behaviour is undesirable because the application may cancel the current
> query, do something else and start a new query on the same table. This
> creates a lot of overhead.
> I have done several tests, and I am sure that the change in behaviour is not
> governed by the number of rows returned, but solely on the number of rows in
> the table. Setting the conditions such that now rows will meet the conditions
> will still show execute as described above.
> Can anyone tell me why it has changed, and how I can get the "old" behaviour
> back, or just point me to a place where it is described.
> Thanks in advance.|||I have the same problem,
i updated the statistics and nothing. it gives "Transaction ended by
trigger"
it works fine on SQL 2000 with no problem.
I am using Forward Only and Read Only Cursor.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:E15A5055-5388-425A-9981-7BBCB2DF8E37@.microsoft.com...
> What kind of cursor did you declare?
> Linchi
> "ErikE" wrote:
>> After a recent upgrade from SQL Server 2000 to SQL Server 2005, we get
>> problems with certain queries using a Transact SQL cursor.
>> I have noticed the following:
>> Table with a smaller number of rows.
>> declare cursor completes normally.
>> open cursor completes normally
>> fetch cursor retrieves the first row from the table.
>> Table with a larger number of rows.
>> declare cursor completes normally.
>> open cursor builds a temporary table with information about all the rows
>> matching the seek conditions. (This can take some time, depending on the
>> number of rows)
>> fetch cursor retrives a row from the table, based on values from the
>> first
>> row in the temporary table.
>> This behaviour is undesirable because the application may cancel the
>> current
>> query, do something else and start a new query on the same table. This
>> creates a lot of overhead.
>> I have done several tests, and I am sure that the change in behaviour is
>> not
>> governed by the number of rows returned, but solely on the number of rows
>> in
>> the table. Setting the conditions such that now rows will meet the
>> conditions
>> will still show execute as described above.
>> Can anyone tell me why it has changed, and how I can get the "old"
>> behaviour
>> back, or just point me to a place where it is described.
>> Thanks in advance.|||I tried the different types according to the transact-sql extended syntax,
all with the same result.
"Linchi Shea" wrote:
> What kind of cursor did you declare?
> Linchi
> "ErikE" wrote:
> > After a recent upgrade from SQL Server 2000 to SQL Server 2005, we get
> > problems with certain queries using a Transact SQL cursor.
> > I have noticed the following:
> > Table with a smaller number of rows.
> > declare cursor completes normally.
> > open cursor completes normally
> > fetch cursor retrieves the first row from the table.
> >
> > Table with a larger number of rows.
> > declare cursor completes normally.
> > open cursor builds a temporary table with information about all the rows
> > matching the seek conditions. (This can take some time, depending on the
> > number of rows)
> > fetch cursor retrives a row from the table, based on values from the first
> > row in the temporary table.
> >
> > This behaviour is undesirable because the application may cancel the current
> > query, do something else and start a new query on the same table. This
> > creates a lot of overhead.
> >
> > I have done several tests, and I am sure that the change in behaviour is not
> > governed by the number of rows returned, but solely on the number of rows in
> > the table. Setting the conditions such that now rows will meet the conditions
> > will still show execute as described above.
> >
> > Can anyone tell me why it has changed, and how I can get the "old" behaviour
> > back, or just point me to a place where it is described.
> >
> > Thanks in advance.|||I have remembered to update stats. Cursors are only used in older
applications, so the problem is just to avoid spending time rewriting these
applications.
"Tom Moreau" wrote:
> Did you remember to update stats on all tables with FULLSCAN when you did
> the migration? Also, do you really need a cursor to do what you need?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "ErikE" <ErikE@.discussions.microsoft.com> wrote in message
> news:4CB99F77-9C10-4FC6-BDFF-98C1B8A022FB@.microsoft.com...
> After a recent upgrade from SQL Server 2000 to SQL Server 2005, we get
> problems with certain queries using a Transact SQL cursor.
> I have noticed the following:
> Table with a smaller number of rows.
> declare cursor completes normally.
> open cursor completes normally
> fetch cursor retrieves the first row from the table.
> Table with a larger number of rows.
> declare cursor completes normally.
> open cursor builds a temporary table with information about all the rows
> matching the seek conditions. (This can take some time, depending on the
> number of rows)
> fetch cursor retrives a row from the table, based on values from the first
> row in the temporary table.
> This behaviour is undesirable because the application may cancel the current
> query, do something else and start a new query on the same table. This
> creates a lot of overhead.
> I have done several tests, and I am sure that the change in behaviour is not
> governed by the number of rows returned, but solely on the number of rows in
> the table. Setting the conditions such that now rows will meet the
> conditions
> will still show execute as described above.
> Can anyone tell me why it has changed, and how I can get the "old" behaviour
> back, or just point me to a place where it is described.
> Thanks in advance.
>
>