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.
>
>

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...[vbcol=seagreen]
> What kind of cursor did you declare?
> Linchi
> "ErikE" wrote:
|||I tried the different types according to the transact-sql extended syntax,
all with the same result.
"Linchi Shea" wrote:
[vbcol=seagreen]
> What kind of cursor did you declare?
> Linchi
> "ErikE" wrote:
|||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.
>
>

PLS HELP:Merge cells - use cursor?

Hi,
I just wonder if someone could help me here:
Say, i have a table with one column: 'Name'
Name1
Name2
Name3
...
I need to get the string of "Name1, Name2, Name3, ..."
The only way i see is to use cursor - but i know cursors aren't the best way
if an alternative exists...
So is there a way to just use a select to perform that?
Thank you,
AndreyYes, I think you'll need to visit each row.
However instead of using a cursor, try using a table variable (excuse any
typos)
declare @.OutputList varchar(8000)
set @.OutputList=''
declare @.Rows integer
declare @.Val varchar(255)
declare @.I integer
declare @.T1 table (RowNum integer identity(1,1) primary key, RowData
varchar(255))
insert @.T1 select name from YourTable
set @.Rows = @.@.ROWCOUNT
if @.Rows<>0
begin
set @.I=0
while @.I<@.Rows
begin
set @.I=@.I+1
select @.Val=@.T1.RowData where @.T1.RowNum = @.I
set @.OutputList = @.OutputList + @.Val + ','
end
set @.OutputList = left(@.OutputList,Len(@.OutputList)-1) -- get rid of final
comma
end
....gtr
"MuZZy" wrote:

> Hi,
> I just wonder if someone could help me here:
> Say, i have a table with one column: 'Name'
> Name1
> Name2
> Name3
> ...
> I need to get the string of "Name1, Name2, Name3, ..."
> The only way i see is to use cursor - but i know cursors aren't the best w
ay if an alternative exists...
> So is there a way to just use a select to perform that?
> Thank you,
> Andrey
>|||Isn't that just a matter of formatting the output for display? So do it
in the client app, which is where presentation belongs and where it's
generally easier to do this kind of thing.
Otherwise, maybe something like this will help:
CREATE TABLE Foo (name VARCHAR(10) PRIMARY KEY)
SELECT
MAX(CASE ord WHEN 1 THEN name END)+
MAX(CASE ord WHEN 2 THEN ','+name ELSE '' END)+
MAX(CASE ord WHEN 3 THEN ','+name ELSE '' END)+
MAX(CASE ord WHEN 4 THEN ','+name ELSE '' END)+
MAX(CASE ord WHEN 5 THEN ','+name ELSE '' END)
/* repeat as required... */
FROM
(SELECT P.name, COUNT(*)
FROM Foo AS P,Foo AS Q
WHERE P.name >= Q.name
GROUP BY P.name) AS T(name,ord)
David Portas
SQL Server MVP
--