"If you want a lot of information, one has to use cursors."

A developer I'm working with just gave me a stored procedure that uses three
cursors, two of which are nested into the first. These cursors retrieve data
from tables one row at a time to build temporary tables, which are used to
build a result set.
I told him not to use cursors because they're incredibly slow compared to
joins, and make the query much harder to read and maintain. He replied,
"If you want a lot of information, one has to use cursors."
I didn't know that. To think, I'd been using just plain old joins to get
data all these years. Guess I've been just doing it wrong.
PaulTell him "Cursors are useful if you don't know SQL"
(Acknowledgments to Nigel Rivett)
David Portas
SQL Server MVP
--|||The correct quote is if one has a lot of information and one chooses to use
cursors, one will see a significant decline in performance.
"PJ6" <nobody@.nowhere.net> wrote in message
news:%23nsiUcWuFHA.664@.tk2msftngp13.phx.gbl...
>A developer I'm working with just gave me a stored procedure that uses
>three cursors, two of which are nested into the first. These cursors
>retrieve data from tables one row at a time to build temporary tables,
>which are used to build a result set.
> I told him not to use cursors because they're incredibly slow compared to
> joins, and make the query much harder to read and maintain. He replied,
> "If you want a lot of information, one has to use cursors."
> I didn't know that. To think, I'd been using just plain old joins to get
> data all these years. Guess I've been just doing it wrong.
> Paul
>|||I didn't see a smiley face and if there's sarcasm there, it's not obvious
enough for me.
If this guy really ticks you off, you're going to have to prove him wrong
but this involves a bit of work for you re-coding the SP and the client
application.
Good luck.
"PJ6" <nobody@.nowhere.net> wrote in message
news:%23nsiUcWuFHA.664@.tk2msftngp13.phx.gbl...
>A developer I'm working with just gave me a stored procedure that uses
>three cursors, two of which are nested into the first. These cursors
>retrieve data from tables one row at a time to build temporary tables,
>which are used to build a result set.
> I told him not to use cursors because they're incredibly slow compared to
> joins, and make the query much harder to read and maintain. He replied,
> "If you want a lot of information, one has to use cursors."
> I didn't know that. To think, I'd been using just plain old joins to get
> data all these years. Guess I've been just doing it wrong.
> Paul
>|||My rule of thumb is that a cursor runs 10 times slower than a query.
The nested cursors are usually a sign that he is mimicking a tape file
system instead of writing SQL.|||> "If you want a lot of information, one has to use cursors."
You could swap out "if you want a lot of information" and replace it with
"if one doesn't understand set theory"...|||Hi
It is a good idea to make your developers test on databases that have a
reasonable amount of realistic data.
John
"PJ6" <nobody@.nowhere.net> wrote in message
news:%23nsiUcWuFHA.664@.tk2msftngp13.phx.gbl...
>A developer I'm working with just gave me a stored procedure that uses
>three cursors, two of which are nested into the first. These cursors
>retrieve data from tables one row at a time to build temporary tables,
>which are used to build a result set.
> I told him not to use cursors because they're incredibly slow compared to
> joins, and make the query much harder to read and maintain. He replied,
> "If you want a lot of information, one has to use cursors."
> I didn't know that. To think, I'd been using just plain old joins to get
> data all these years. Guess I've been just doing it wrong.
> Paul
>|||You must not know how to write a cursor. If it's done right, a cursor will
only take twice as long as a set-based query, but in some cases--usually
those involving a self-join--a cursor will actually perform better.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1126729006.844457.69490@.g14g2000cwa.googlegroups.com...
> My rule of thumb is that a cursor runs 10 times slower than a query.
> The nested cursors are usually a sign that he is mimicking a tape file
> system instead of writing SQL.
>|||He wrote the book on self joins.
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:Or3yv1WuFHA.3932@.TK2MSFTNGP15.phx.gbl...
> You must not know how to write a cursor. If it's done right, a cursor
> will
> only take twice as long as a set-based query, but in some cases--usually
> those involving a self-join--a cursor will actually perform better.
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1126729006.844457.69490@.g14g2000cwa.googlegroups.com...
>|||I just used a cursor a few hours ago:
open dishwasher for all the dirty dishes
while (@.@.fetch_status = 0)
begin
fetch next dish
insert into dishwasher
dish
end
And it worked. Yes, pseudo-code is good enough for most of my kitchen
appliances.
ML