Hi,
I'm having a CURSOR running through ~45000 entries in a table. If I, for
example, use PRINT inside the cursor, can that be a performance hit? Are
there other performance things to think about when using cursors?
Thanks,
Mats-LennartCursors in general are not recommended. If you could discuss more about why
you are using cursors there could be alternate solutions that this newsgroup
can provide you with.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Mats-Lennart Hansson" <ap_skallen@.hotmail.com> wrote in message
news:ePBVg2ASGHA.5736@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm having a CURSOR running through ~45000 entries in a table. If I, for
> example, use PRINT inside the cursor, can that be a performance hit? Are
> there other performance things to think about when using cursors?
> Thanks,
> Mats-Lennart
>|||Thanks for your answer.
I know that it's not recommended, but in this case there is no other
(managable) solution. There is also no time for a redesign, so this is the
solution that will be used. However, I still wonder:
Can a lot of PRINTs be a performance problem? Are there other things that
can decrease performance, like user defined functions?
Thanks,
Mats-Lennart
"SriSamp" <ssampath@.sct.co.in> wrote in message
news:epxWI9ASGHA.5900@.tk2msftngp13.phx.gbl...
> Cursors in general are not recommended. If you could discuss more about
> why you are using cursors there could be alternate solutions that this
> newsgroup can provide you with.
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Mats-Lennart Hansson" <ap_skallen@.hotmail.com> wrote in message
> news:ePBVg2ASGHA.5736@.TK2MSFTNGP10.phx.gbl...
>|||Mats-Lennart Hansson wrote:
> Thanks for your answer.
> I know that it's not recommended, but in this case there is no other
> (managable) solution. There is also no time for a redesign, so this is the
> solution that will be used. However, I still wonder:
> Can a lot of PRINTs be a performance problem?
Of course. I don't know why you would use PRINT in a production system
however. PRINT is typically just debug code or for ad hoc stuff. Does
it matter if it's too late to change anyway? If performance is your
concern then PRINT may be insignificant next to the overhead of using a
cursor.
> Are there other things that
> can decrease performance, like user defined functions?
Generally speaking the more you do in a cursor loop the more processing
is required. One reason to prefer set-based solutions rather than
cursors is that the same isn't always true in declarative code -
performance doesn't necessarily degrade in line with complexity.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi,
I have no intentions in having a lot of PRINTs in our production system, why
I'm wondering is simply because of curiosity. Can I expect a performance
boost when going into production without PRINT's compared to now, when I'm
in development? Are there other things to do to improve efficiency? These
are questions I'm interested in. I want to learn from my mistakes to improve
my future projects :)
Why I'm using CURSORs in this project is because we are converting data from
one database into another. Unfortunately, the original data is not
consistent and needs to be checked before being converted. Of course there
are other solutions than using CURSORs, but this seemed to be the most
straightforward way.
Instead of looping through 50000 entries, can it be more efficient to run
through 5000 at a time, opening and closening the cursor in between? Could
this reduce some "overhead" costs?
Thanks for replying,
Mats-Lennart
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1142425512.174670.273740@.i39g2000cwa.googlegroups.com...
> Mats-Lennart Hansson wrote:
> Of course. I don't know why you would use PRINT in a production system
> however. PRINT is typically just debug code or for ad hoc stuff. Does
> it matter if it's too late to change anyway? If performance is your
> concern then PRINT may be insignificant next to the overhead of using a
> cursor.
>
> Generally speaking the more you do in a cursor loop the more processing
> is required. One reason to prefer set-based solutions rather than
> cursors is that the same isn't always true in declarative code -
> performance doesn't necessarily degrade in line with complexity.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment