"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

"cursor" what is that

Can anyone tell me where i could learn about "cursors", what they're for and how to code it ?
Thanks a lot !Cursor is basically a loop through the data. Where you can stop and process each row in this loop separately. They are too slow and not recommended to use often.

Read more on declaration and use here:

http://msdn2.microsoft.com/en-us/library/ms190028.aspx
http://www.databasejournal.com/features/mssql/article.php/1439731

Good Luck.|||Thanks a lot iburyak !

"Colescing" values in a field?

Hello all, i am wondering if i can do the following with simple TSQL
statements without needing to resort to using cursors... i am having a hard
time trying to think of a way, but TSQL isnt my 'native' sql programming
language (i 'grew up' on PL/SQL), so im hoping someone else can think of
something of how to do this.
I have a field which has essentially sequential values, say,
1,2,3,4,5,6..... but for certain reasons they are not sequence assigned
(they are actually alphanumeric, 1A, 1B, 1C, 1D, etc...). After time,
records get added and deleted, and the sequnce winds up with holes in it...
1A, 1B, 1D, 1G, ...
Is there a slick or elegent T/SQL statement i can run to coalesce these
values back down to fill in the gaps?
So the above holed sequence of 1A, 1B, 1D, 1G would wind up being 1A, 1B,
1C, 1D.
Thanks in advance.
- Arthur Dent.one obvious way, but perhaps not very elegant would be to insert the
values into a temporary table with an extra identity field, then use
that to remap your values with an update statement|||Arthur Dent wrote:
> Hello all, i am wondering if i can do the following with simple TSQL
> statements without needing to resort to using cursors... i am having a ha
rd
> time trying to think of a way, but TSQL isnt my 'native' sql programming
> language (i 'grew up' on PL/SQL), so im hoping someone else can think of
> something of how to do this.
> I have a field which has essentially sequential values, say,
> 1,2,3,4,5,6..... but for certain reasons they are not sequence assigned
> (they are actually alphanumeric, 1A, 1B, 1C, 1D, etc...). After time,
> records get added and deleted, and the sequnce winds up with holes in it..
.
> 1A, 1B, 1D, 1G, ...
> Is there a slick or elegent T/SQL statement i can run to coalesce these
> values back down to fill in the gaps?
> So the above holed sequence of 1A, 1B, 1D, 1G would wind up being 1A, 1B,
> 1C, 1D.
> Thanks in advance.
> - Arthur Dent.
Is SQL Server a word processor or a DBMS? How about putting the values
in a table rather than delimiting them in a column?
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
--|||One question comes to mind... WHY?
Another one would be... is this column being referenced by another table?
I would lean towards doing this in a client application.
"Arthur Dent" <hitchhikersguideto-news@.yahoo.com> wrote in message
news:eJvDfhYWGHA.1200@.TK2MSFTNGP03.phx.gbl...
> Hello all, i am wondering if i can do the following with simple TSQL
> statements without needing to resort to using cursors... i am having a
> hard time trying to think of a way, but TSQL isnt my 'native' sql
> programming language (i 'grew up' on PL/SQL), so im hoping someone else
> can think of something of how to do this.
> I have a field which has essentially sequential values, say,
> 1,2,3,4,5,6..... but for certain reasons they are not sequence assigned
> (they are actually alphanumeric, 1A, 1B, 1C, 1D, etc...). After time,
> records get added and deleted, and the sequnce winds up with holes in
> it...
> 1A, 1B, 1D, 1G, ...
> Is there a slick or elegent T/SQL statement i can run to coalesce these
> values back down to fill in the gaps?
> So the above holed sequence of 1A, 1B, 1D, 1G would wind up being 1A, 1B,
> 1C, 1D.
> Thanks in advance.
> - Arthur Dent.
>|||I think you guys misread it, I interpreted 1,2,3,4,5,6..... as being
1
2
3
4
5
6|||Will wrote:
> I think you guys misread it, I interpreted 1,2,3,4,5,6..... as being
> 1
> 2
> 3
> 4
> 5
> 6
Perhaps you are right. If Arthur had included DDL and some proper
sample data then we wouldn't have to resort to guessing.
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
--|||I understood it the same way you did Will.
So you should change your post to:
>I think you guy misread it, ... :-)
"Will" <william_pegg@.yahoo.co.uk> wrote in message
news:1144336766.712050.287240@.j33g2000cwa.googlegroups.com...
>I think you guys misread it, I interpreted 1,2,3,4,5,6..... as being
> 1
> 2
> 3
> 4
> 5
> 6
>|||> One question comes to mind... WHY?
Obviously to make the result set pretty as must have been the intention of
whoever invented this lovely key generator. :)
ML
http://milambda.blogspot.com/