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.