Optimizing cursor performance?

Hi there!
I have an application that uses cursors en mass in SQL. Actually, it does not
speak one word relational SQL with MSSQL, only cursors. I think it has a fair
bet in becoming World Champion in cursor abuse.
Anyway, the application is almost pure sequential/flat file based, and thus I
wondered, is there anything I can do to MSSQL that pulls out every bit of cursor
performance available, as it is very much needed?
Microsoft knows the problems, as they themself bought this very application..
I doubt, therefore I might be.
Kim
Cursors are never (mostly) good choice. Without seeing your data ,it's hard
to suggest something.
"Kim Noer" <kn@.nospam.dk> wrote in message
news:%23NlLrRMvFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Hi there!
> I have an application that uses cursors en mass in SQL. Actually, it does
> not speak one word relational SQL with MSSQL, only cursors. I think it has
> a fair bet in becoming World Champion in cursor abuse.
> Anyway, the application is almost pure sequential/flat file based, and
> thus I wondered, is there anything I can do to MSSQL that pulls out every
> bit of cursor performance available, as it is very much needed?
> Microsoft knows the problems, as they themself bought this very
> application..
> --
> I doubt, therefore I might be.
>
|||I have found in many cases that declaring the cursor as Static can often
lead to increased performance but as always it depends<g>.
Andrew J. Kelly SQL MVP
"Kim Noer" <kn@.nospam.dk> wrote in message
news:%23NlLrRMvFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Hi there!
> I have an application that uses cursors en mass in SQL. Actually, it does
> not speak one word relational SQL with MSSQL, only cursors. I think it has
> a fair bet in becoming World Champion in cursor abuse.
> Anyway, the application is almost pure sequential/flat file based, and
> thus I wondered, is there anything I can do to MSSQL that pulls out every
> bit of cursor performance available, as it is very much needed?
> Microsoft knows the problems, as they themself bought this very
> application..
> --
> I doubt, therefore I might be.
>
|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Ov$xEZNvFHA.3864@.TK2MSFTNGP12.phx.gbl

> Cursors are never (mostly) good choice. Without seeing your data
> ,it's hard to suggest something.
I know, that's why I'm trying to be slightly sarcastic in my message .
What do you need of information? Unfortunately I can only pull out limited
information on this, as the application is closed source.
I doubt, therefore I might be.
|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23$zCDeRvFHA.3588@.tk2msftngp13.phx.gbl
> I have found in many cases that declaring the cursor as Static can
> often lead to increased performance but as always it depends<g>.
But is there anything more 'high level' I can do? What does cursors appreciate
of hardware; same as with relational SQL?
The fun thing is that the application doesn't seem to care much about extra RAM.
Even when the SQL server sucks up 1.5GB versus 500MB, I still don't see any
performance improvements noteworthy.
Almost seems like there's some hardwired cap on max cursor performance .
I doubt, therefore I might be.
|||Depending on how they are configured and such they may use a lot of Tempdb.
So making sure tempdb is on a fast drive array might help. They tend to use
a fair amount of cpu as well. But there is no magic switch to make cursors
go faster. The real answer is sadly to rewrite them to not use cursors.
Andrew J. Kelly SQL MVP
"Kim Noer" <kn@.nospam.dk> wrote in message
news:udfAjDSvFHA.2076@.TK2MSFTNGP14.phx.gbl...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23$zCDeRvFHA.3588@.tk2msftngp13.phx.gbl
> But is there anything more 'high level' I can do? What does cursors
> appreciate of hardware; same as with relational SQL?
> The fun thing is that the application doesn't seem to care much about
> extra RAM. Even when the SQL server sucks up 1.5GB versus 500MB, I still
> don't see any performance improvements noteworthy.
> Almost seems like there's some hardwired cap on max cursor performance .
> --
> I doubt, therefore I might be.
>
|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e50F7YSvFHA.3580@.TK2MSFTNGP10.phx.gbl

> Depending on how they are configured and such they may use a lot of
> Tempdb. So making sure tempdb is on a fast drive array might help.
> They tend to use a fair amount of cpu as well. But there is no magic
> switch to make cursors go faster. The real answer is sadly to
> rewrite them to not use cursors.
I tried "performance monitor" the tempdb while pounding it with nasty cursors
through the application. However I don't seen any kind of activity in the
tempdb. Looking at the database the application uses, I get a "log byte
flushed/sec" activity (about 61kb) every 4-5 second or so.
Monitoring CPU usage I get around ~60% on a hyperthread Intel P4. "Monitoring
Avg. Disk Transfer" I get numbers far below the capacity of the disk (it's a WDC
Raptor 10k rpm).
So I'm not really any closer figuring out where the bottleneck is regarding
cursor abuse.
I doubt, therefore I might be.

Optimizing cursor performance?

Hi there!
I have an application that uses cursors en massé in SQL. Actually, it does not
speak one word relational SQL with MSSQL, only cursors. I think it has a fair
bet in becoming World Champion in cursor abuse.
Anyway, the application is almost pure sequential/flat file based, and thus I
wondered, is there anything I can do to MSSQL that pulls out every bit of cursor
performance available, as it is very much needed?
Microsoft knows the problems, as they themself bought this very application..
--
I doubt, therefore I might be.Kim
Cursors are never (mostly) good choice. Without seeing your data ,it's hard
to suggest something.
"Kim Noer" <kn@.nospam.dk> wrote in message
news:%23NlLrRMvFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Hi there!
> I have an application that uses cursors en massé in SQL. Actually, it does
> not speak one word relational SQL with MSSQL, only cursors. I think it has
> a fair bet in becoming World Champion in cursor abuse.
> Anyway, the application is almost pure sequential/flat file based, and
> thus I wondered, is there anything I can do to MSSQL that pulls out every
> bit of cursor performance available, as it is very much needed?
> Microsoft knows the problems, as they themself bought this very
> application..
> --
> I doubt, therefore I might be.
>|||I have found in many cases that declaring the cursor as Static can often
lead to increased performance but as always it depends<g>.
--
Andrew J. Kelly SQL MVP
"Kim Noer" <kn@.nospam.dk> wrote in message
news:%23NlLrRMvFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Hi there!
> I have an application that uses cursors en massé in SQL. Actually, it does
> not speak one word relational SQL with MSSQL, only cursors. I think it has
> a fair bet in becoming World Champion in cursor abuse.
> Anyway, the application is almost pure sequential/flat file based, and
> thus I wondered, is there anything I can do to MSSQL that pulls out every
> bit of cursor performance available, as it is very much needed?
> Microsoft knows the problems, as they themself bought this very
> application..
> --
> I doubt, therefore I might be.
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Ov$xEZNvFHA.3864@.TK2MSFTNGP12.phx.gbl
> Cursors are never (mostly) good choice. Without seeing your data
> ,it's hard to suggest something.
I know, that's why I'm trying to be slightly sarcastic in my message :).
What do you need of information? Unfortunately I can only pull out limited
information on this, as the application is closed source.
--
I doubt, therefore I might be.|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23$zCDeRvFHA.3588@.tk2msftngp13.phx.gbl
> I have found in many cases that declaring the cursor as Static can
> often lead to increased performance but as always it depends<g>.
But is there anything more 'high level' I can do? What does cursors appreciate
of hardware; same as with relational SQL?
The fun thing is that the application doesn't seem to care much about extra RAM.
Even when the SQL server sucks up 1.5GB versus 500MB, I still don't see any
performance improvements noteworthy.
Almost seems like there's some hardwired cap on max cursor performance :).
--
I doubt, therefore I might be.|||Depending on how they are configured and such they may use a lot of Tempdb.
So making sure tempdb is on a fast drive array might help. They tend to use
a fair amount of cpu as well. But there is no magic switch to make cursors
go faster. The real answer is sadly to rewrite them to not use cursors.
--
Andrew J. Kelly SQL MVP
"Kim Noer" <kn@.nospam.dk> wrote in message
news:udfAjDSvFHA.2076@.TK2MSFTNGP14.phx.gbl...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23$zCDeRvFHA.3588@.tk2msftngp13.phx.gbl
>> I have found in many cases that declaring the cursor as Static can
>> often lead to increased performance but as always it depends<g>.
> But is there anything more 'high level' I can do? What does cursors
> appreciate of hardware; same as with relational SQL?
> The fun thing is that the application doesn't seem to care much about
> extra RAM. Even when the SQL server sucks up 1.5GB versus 500MB, I still
> don't see any performance improvements noteworthy.
> Almost seems like there's some hardwired cap on max cursor performance :).
> --
> I doubt, therefore I might be.
>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e50F7YSvFHA.3580@.TK2MSFTNGP10.phx.gbl
> Depending on how they are configured and such they may use a lot of
> Tempdb. So making sure tempdb is on a fast drive array might help.
> They tend to use a fair amount of cpu as well. But there is no magic
> switch to make cursors go faster. The real answer is sadly to
> rewrite them to not use cursors.
I tried "performance monitor" the tempdb while pounding it with nasty cursors
through the application. However I don't seen any kind of activity in the
tempdb. Looking at the database the application uses, I get a "log byte
flushed/sec" activity (about 61kb) every 4-5 second or so.
Monitoring CPU usage I get around ~60% on a hyperthread Intel P4. "Monitoring
Avg. Disk Transfer" I get numbers far below the capacity of the disk (it's a WDC
Raptor 10k rpm).
So I'm not really any closer figuring out where the bottleneck is regarding
cursor abuse.
--
I doubt, therefore I might be.

Optimizing cursor performance?

Hi there!
I have an application that uses cursors en mass in SQL. Actually, it does n
ot
speak one word relational SQL with MSSQL, only cursors. I think it has a fai
r
bet in becoming World Champion in cursor abuse.
Anyway, the application is almost pure sequential/flat file based, and thus
I
wondered, is there anything I can do to MSSQL that pulls out every bit of cu
rsor
performance available, as it is very much needed?
Microsoft knows the problems, as they themself bought this very application.
.
I doubt, therefore I might be.Kim
Cursors are never (mostly) good choice. Without seeing your data ,it's hard
to suggest something.
"Kim Noer" <kn@.nospam.dk> wrote in message
news:%23NlLrRMvFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Hi there!
> I have an application that uses cursors en mass in SQL. Actually, it does
> not speak one word relational SQL with MSSQL, only cursors. I think it has
> a fair bet in becoming World Champion in cursor abuse.
> Anyway, the application is almost pure sequential/flat file based, and
> thus I wondered, is there anything I can do to MSSQL that pulls out every
> bit of cursor performance available, as it is very much needed?
> Microsoft knows the problems, as they themself bought this very
> application..
> --
> I doubt, therefore I might be.
>|||I have found in many cases that declaring the cursor as Static can often
lead to increased performance but as always it depends<g>.
Andrew J. Kelly SQL MVP
"Kim Noer" <kn@.nospam.dk> wrote in message
news:%23NlLrRMvFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Hi there!
> I have an application that uses cursors en mass in SQL. Actually, it does
> not speak one word relational SQL with MSSQL, only cursors. I think it has
> a fair bet in becoming World Champion in cursor abuse.
> Anyway, the application is almost pure sequential/flat file based, and
> thus I wondered, is there anything I can do to MSSQL that pulls out every
> bit of cursor performance available, as it is very much needed?
> Microsoft knows the problems, as they themself bought this very
> application..
> --
> I doubt, therefore I might be.
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Ov$xEZNvFHA.3864@.TK2MSFTNGP12.phx.gbl

> Cursors are never (mostly) good choice. Without seeing your data
> ,it's hard to suggest something.
I know, that's why I'm trying to be slightly sarcastic in my message .
What do you need of information? Unfortunately I can only pull out limited
information on this, as the application is closed source.
I doubt, therefore I might be.|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23$zCDeRvFHA.3588@.tk2msftngp13.phx.gbl
> I have found in many cases that declaring the cursor as Static can
> often lead to increased performance but as always it depends<g>.
But is there anything more 'high level' I can do? What does cursors apprecia
te
of hardware; same as with relational SQL?
The fun thing is that the application doesn't seem to care much about extra
RAM.
Even when the SQL server sucks up 1.5GB versus 500MB, I still don't see any
performance improvements noteworthy.
Almost seems like there's some hardwired cap on max cursor performance .
I doubt, therefore I might be.|||Depending on how they are configured and such they may use a lot of Tempdb.
So making sure tempdb is on a fast drive array might help. They tend to use
a fair amount of cpu as well. But there is no magic switch to make cursors
go faster. The real answer is sadly to rewrite them to not use cursors.
Andrew J. Kelly SQL MVP
"Kim Noer" <kn@.nospam.dk> wrote in message
news:udfAjDSvFHA.2076@.TK2MSFTNGP14.phx.gbl...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23$zCDeRvFHA.3588@.tk2msftngp13.phx.gbl
> But is there anything more 'high level' I can do? What does cursors
> appreciate of hardware; same as with relational SQL?
> The fun thing is that the application doesn't seem to care much about
> extra RAM. Even when the SQL server sucks up 1.5GB versus 500MB, I still
> don't see any performance improvements noteworthy.
> Almost seems like there's some hardwired cap on max cursor performance .
> --
> I doubt, therefore I might be.
>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e50F7YSvFHA.3580@.TK2MSFTNGP10.phx.gbl

> Depending on how they are configured and such they may use a lot of
> Tempdb. So making sure tempdb is on a fast drive array might help.
> They tend to use a fair amount of cpu as well. But there is no magic
> switch to make cursors go faster. The real answer is sadly to
> rewrite them to not use cursors.
I tried "performance monitor" the tempdb while pounding it with nasty cursor
s
through the application. However I don't seen any kind of activity in the
tempdb. Looking at the database the application uses, I get a "log byte
flushed/sec" activity (about 61kb) every 4-5 second or so.
Monitoring CPU usage I get around ~60% on a hyperthread Intel P4. "Monitorin
g
Avg. Disk Transfer" I get numbers far below the capacity of the disk (it's a
WDC
Raptor 10k rpm).
So I'm not really any closer figuring out where the bottleneck is regarding
cursor abuse.
I doubt, therefore I might be.

Optimizing cursor

I've tried a bunch of different ways in an effort to stay away from using a cursor, but I haven't been able to accomplish what I need to do without one. So, I coded this process using cursors and performance (as expected) is pretty mediocre. I was wondering if someone could take a quick look and suggest a different approach or maybe suggest ways to optimize the current code.

*Attached* is my code.

TIAYou're already doing LEFT OUTER JOIN on CONTACTS table, so why not avoid additional updates by including dateLastContact and emailformat fields into your SELECT?

Other suggestions may come later, but you can definitely break this thing down by combining the operations and maybe even considering using BCP to generate the actual script that you can run as a scheduled task.|||--I don't know the definition of your group and collection cursors so that may change this process
--It looks like you should be able to do this with set based updates and temp tables. Take a look and
--see if you see any major flaws

---- Save New Contacts in a temp table

select * (enumerate columns) into #new_contacts
FROM ContactImport new_data LEFT OUTER JOIN contacts previous_data ON new_data.emailAddress = previous_data.emailAddress
WHERE contact_id is null

---- Save contacts updates in a temp table

select * into #update_contacts
FROM ContactImport new_data LEFT OUTER JOIN contacts previous_data ON new_data.emailAddress = previous_data.emailAddress
WHERE contact_id is not null

--Update contacts with new contact info if necessary
if @.dup_handling = 'o'

update contacts
set x=x1,y=y1,z=z1 (enumerate columns)
from #update_contacts
where contacts.contact_id = #update_contacts.contact_id

-- Update group contacts for all new contacts, existing contacts missing groupcontact
insert groupContacts (group_id, contact_id)
select group_id, contact_id
from #update_contacts left outer join groupContacts on #update_contacts.contact_id = groupContacts.contact_id
and #update_contacts.group_id = groupContacts.group_id
where groupContacts.contact_id is null
UNION ALL
select group_id, contact_id from #new_contacts

-- Update collections contacts for all new contacts, and existing contacts missing collection contact
insert colCollections (col_id, contact_id)
select coll_id, contact_id
from #update_contacts left outer join colContacts on #update_contacts.contact_id = colContacts.contact_id
and #update_contacts.col_id = colContacts.col_id
where colContacts.contact_id is null
UNION ALL
select col_contacts, contact_id from #new_contacts

insert contacts (x,y,z) (enumerate columns)
select x,y,z from #new_contacts|||vaxman,

Thanks for your ideas; I'll try them shortly. Although, shouldn't I stay away from #temp tables. Maybe do this using a real "temp" table??

To answer your questions:

1. The group cursor is there because a contact can be assigned to multiple groups. In the case of existing contacts I also don't want to add them to the group if they're already in it.

2. The collection cursor works just like the group cursor. Basically, if I'm updating a contact I need to make sure that the contact is not being added multiple times to the same collection (coll_id-contact_id is a unique constraint), and I also have to ensure contacts get added to collections that don't exist (for a non-matching coll_id-contact_id).

Thanks again|||Hi naceBal,

temp tables are not necessarily bad if they solve a greater problem. In this case, you update contacts, groupcontacts and colcontacts from the result set of a query. You can't update multiple tables in a singe statement, so the alternative would be to update the permanent tables by running the main query 3 seperate times. Presumably that will take three times as many resources as running it only once and re-using the results.

I understand WHY the group and col cursors exist, but their exact deffinition was not included so there might be more to them than I assumed.

The set based solution should perform much better. Also, when you declare cursors and specify an order by , group, or distinct, SQL server creates a temporary working table with the resutls anyway, so -- you had temp tables already ;)

Good luck!

Optimizer hints in PL/SQL cursor declaration

Hello,
Do optimizer hints work when you put them in cursor declarations in PL/SQL procedures?
Any help will be appreciated.Yes they do.|||Great, thanks.
I'll try some performance tuning then... I may be back with some new questions :-)

Optimization tips for Cursor and Scope_Identity

...using SQL 2K
...This is for a data migration project which involves the splitting of a
flat file into two or more tables with relationships. The migration will
happen over time and not at one time.
...My SQL Server/DB experience up to this point has been primarily focused
on client side request, but am pretty short on optimization... so I need som
e
direction here. I can research via Google, BOL and forums, but I don't even
know where to start.
I am currently using a cursor to walk through rows in tblMigration and build
an insert statement for tblParent. I am using the cursor becuase I need to
capture the new identity value (SCOPE_IDENTITY) after each insert into
tblParent and use it to migrate the rest of the records destined for the
various child tables in the new schema.
I am moving data from one server to another and have set up a linked server.
currently a migration of 53,000 records is taking 42 min (on my dev box:
WINXP, 1GB ram, 2 GHZ).
I originally wanted to do the following, but was told this would play havoc
with replication
1. ALTER tblParent ADD old_id BIGINT
2. INSERT tblParent (field1,field2,old_id) SELECT field1, field2, id FROM
tblMigration WHERE field1='parent'
3. do remaing migration relating the tblParent.old_id to tblMIgration.id fie
ld
4. and then drop the old_id collumn from tblParent
Once again, I have not posted actual TSQL because it would be very long and
I just need the pointer in the right direction... but if that would help, le
t
me know.
thanks
KevinHavoc with replication? How?

> I originally wanted to do the following, but was told this would play
> havoc
> with replication
> 1. ALTER tblParent ADD old_id BIGINT
> 2. INSERT tblParent (field1,field2,old_id) SELECT field1, field2, id FROM
> tblMigration WHERE field1='parent'
> 3. do remaing migration relating the tblParent.old_id to tblMIgration.id
> field
> 4. and then drop the old_id collumn from tblParent
Either way, if you have a natural key in the field1 or field2 combination
that you can use for getting the surrogate (identity) key that is even
better.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"kevin" <kevin@.discussions.microsoft.com> wrote in message
news:1A0B0471-24CF-414D-AE0B-5EF830993828@.microsoft.com...
> ...using SQL 2K
> ...This is for a data migration project which involves the splitting of a
> flat file into two or more tables with relationships. The migration will
> happen over time and not at one time.
> ...My SQL Server/DB experience up to this point has been primarily focused
> on client side request, but am pretty short on optimization... so I need
> some
> direction here. I can research via Google, BOL and forums, but I don't
> even
> know where to start.
> I am currently using a cursor to walk through rows in tblMigration and
> build
> an insert statement for tblParent. I am using the cursor becuase I need
> to
> capture the new identity value (SCOPE_IDENTITY) after each insert into
> tblParent and use it to migrate the rest of the records destined for the
> various child tables in the new schema.
> I am moving data from one server to another and have set up a linked
> server.
> currently a migration of 53,000 records is taking 42 min (on my dev box:
> WINXP, 1GB ram, 2 GHZ).
> I originally wanted to do the following, but was told this would play
> havoc
> with replication
> 1. ALTER tblParent ADD old_id BIGINT
> 2. INSERT tblParent (field1,field2,old_id) SELECT field1, field2, id FROM
> tblMigration WHERE field1='parent'
> 3. do remaing migration relating the tblParent.old_id to tblMIgration.id
> field
> 4. and then drop the old_id collumn from tblParent
> Once again, I have not posted actual TSQL because it would be very long
> and
> I just need the pointer in the right direction... but if that would help,
> let
> me know.
> thanks
> Kevin
>|||"Louis Davidson" wrote:

> Havoc with replication? How?
>
I was told, by our SQL DBA, that ad-hoc schema changes would not be allowed
with replication. Now I am not certain if he meant
1. that replication, by design, would cause any schema changes to fail
2. that schema changes would be in violation of business rules because they
would break replication and thus I was not to do this or
3. that replication would destroy any schema changes I made by overwritting
them
I am working on the understanding, a bit fuzzy as it is, that replication
essentially is a manage procedure for copying database schema and data
changes from one server to another as a form of backup and/or security.
Suffice it to say, I was explicity told "No schema changes". Personnaly I
don't see the big deal wth adding collumns as this will be invisible to any
client apps unless they are calculating the ordinal position of particular
collumns... which would be pretty insane.
In either case saying that a migration of a midsized client would take 40
minutes got a consession. I will throwing my old_id into an existing collum
n
that would never be populated by legacy records on the most critical part of
the migration.
I will still have to use cursors for some of the smaller tables.
If there is a way to optimize (hints etc), I would love the input.
thanks.
Kevin
"Louis Davidson" wrote:

> Havoc with replication? How?
>