Hi All,
I want to avoid using cursors and loops in stored procedures.
Please suggest alternate solutions with example (if possible).
Any suggestion in these regards will be appreciated.
Thanks in advance,
T.S.NegiHere is a sample:
http://www.extremeexperts.com/SQL/A...TSQLResult.aspx
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"T.S.Negi" <tilak.negi@.mind-infotech.com> wrote in message
news:a1930058.0502132109.268ae0dd@.posting.google.c om...
> Hi All,
> I want to avoid using cursors and loops in stored procedures.
> Please suggest alternate solutions with example (if possible).
> Any suggestion in these regards will be appreciated.
>
> Thanks in advance,
> T.S.Negi|||Loops and cursors are just programming constructs, not actual problems
to be solved, so there is no generic solution or example to show how to
avoid them. You should try to avoid or minimize the use of loops and
cursors in SQL and this is done by writing standard set-based code:
SELECT, UPDATE, DELETE and INSERT statements that operate on sets of
rows rather than one row at a time. The actual details will depend on
exactly what you want to achieve.
If you require hlep with a specific problem then please post more
details as described in:
http://www.aspfaq.com/etiquette.asp?id=5006
--
David Portas
SQL Server MVP
--|||Unfortunately, all the examples in that article are loops!
--
David Portas
SQL Server MVP
--|||tilak.negi@.mind-infotech.com (T.S.Negi) wrote:
>Hi All,
>I want to avoid using cursors and loops in stored procedures.
>Please suggest alternate solutions with example (if possible).
Off at a tangent:
I see many times in these groups (and elsewhere) that cursors should
be avoided. As a comparative newbie can someone explain to me the
reasons for this.
--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/|||There are plenty of good reasons to use declarative, set-based SQL code
instead of cursors. The reason most usually given is performance. SQL
Server, like other SQL databases, is designed primarily for
set-at-a-time rather than row-at-a-time operations. Cursors are
typically very slow, although performance obviously varies considerably
depending on what you are doing. Paradoxically, it is also true to say
that there is a small class of problems for which cursors are faster
than any set-based solution. In a well-designed database those
situations are uncommon in my experience and you would be well-advised
to get a second opinion if you think you have come across such a case.
Performance and locking issues with cursors tend to mean they are far
less scalable than the set-based alternatives so even if they work for
you today they may not be a viable solution in future.
Besides performance there are other good reasons to use set-based code:
The declarative code is usually much more concise and therefore easier
to develop, inspect, test and maintain; It's more likely to be
portable to other database platforms; SQL professionals (good ones
using TSQL anyway) tend to write cursors seldom and so are likely to be
more comfortable and more productive writing set-based code; Set-based
code avoids or tends to show-up some of the logical anomalies and
design problems that can lie hidden and unnoticed in procedural cursor
code.
A legitimate place to use a cursor is for something inherently
procedural (typically admin tasks such as managing backups, sending
emails or importing/exporting files) but in general in an RDBMS you
should assume the solution to any data-manipulation problem will be
set-based unless expert analysis proves otherwise. That's why I would
class cursors as a feature for advanced users only. If you find
yourself writing cursors regularly then it's time to rethink what you
are doing or maybe go on a course to learn grown-up SQL (too many
cursors are written by programmers who don't know better techniques)
:-)
--
David Portas
SQL Server MVP
--|||On Tue, 15 Feb 2005 14:21:03 +0000, John A Fotheringham wrote:
>I see many times in these groups (and elsewhere) that cursors should
>be avoided. As a comparative newbie can someone explain to me the
>reasons for this.
Hi John,
SQL Server is heavily optimized towards set-based operations, where you
use one single query to specify what you want and let SQL Server work out
the best strategy to satisfy your request. That's why SQL is called a
declarative language (you declare the intended results, not the way to get
there, as opposed to procedural languages (where you specify the procedure
to get the intended results).
Using cursors is forcing a procedural approach on SQL. You still use a
query to specify the rows you want to fetch (the declarative part), but
then you fetch one, do something with it and fetch the next - that is
purely procedural.
It is my experience that at least 99% of all existing cursor-based code
can be replaced by set-based code. In most cases, the replacing set-based
code is shorter (less lines), easier to read, understand and maintain (at
least after you've mastered the learning curve to switch from procedural
thinking to declarative thinking) and -most important- performs much
faster.
For the remaining less than one percent, cursors are indeed the best
solution. I won't say that cursors should _always_ be avoided. But I will
say that they are to be used as a final resort only - and it's always wise
to get a second opinion first. Newsgroups are a great place to explain
your problem and ask if others agree that this particular problem can't be
solved with declarative code. In most cases, you'll get a surprising
answer - and if you take the trouble to not only copy and adapt the code
posted, but also to try to understand it, you'll get a great learning
experience thrown in for free!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||One trick I have started to use:
Whenever I need to iterate in a procedure I have a table that I have
created with only one column like so:
CREATE TABLE [Numbers] (
[PkNumber] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [Pk_Number] PRIMARY KEY CLUSTERED
(
[PkNumber]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Declare @.i as int
set @.i = 0
while @.i <= 10000
begin
Insert into dbo.Numbers Default values
set @.i = @.i + 1
end
GO
Now with this table I can query against it like it were a loop but
still remaining set based.
So for instance If I were trying to Schedule a date every 7 days for
the next year I could do something like
insert into dates(dates)
Select DateAdd(Day,pk_Number * 7,getdate())
from Numbers
where pk_number < 366
Just a little trick
Tal McMahon|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> It is my experience that at least 99% of all existing cursor-based code
> can be replaced by set-based code. In most cases, the replacing set-based
> code is shorter (less lines), easier to read, understand and maintain (at
> least after you've mastered the learning curve to switch from procedural
> thinking to declarative thinking) and -most important- performs much
> faster.
Our system has its fair share ot iterative processing, and maybe the
most common good reason to use a cursor is that you have a stored
procedure that performs an operation on a single set of values and
you want to reuse that logic.
If all the procedure performs is a simple update, or a plain insert,
there's little reason to keep the procedure.
But we have core procedures that performs a lot of updates and inserts
(including validations) for a bunch of in-parameters. Rewriting such
a procedure to operate set-based from an input table is a major task.
We've done it in one case. I seem to recall that the time estimate was
200 h, and I think we exceeded that. The result is a monster procedure
on 3000 lines that uses 43 table variables.
This particular rewrite was necessary given some of the volumes that can
occur in some of the iterations with its predecessor. But I can tell
you that I am not going to initiate more rewrites, just for the sake
of it.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 15 Feb 2005 22:57:01 +0000 (UTC), Erland Sommarskog wrote:
(snip)
>This particular rewrite was necessary given some of the volumes that can
>occur in some of the iterations with its predecessor. But I can tell
>you that I am not going to initiate more rewrites, just for the sake
>of it.
Hi Erland,
I see what you mean and I totally agree: if it works and it is not "too"
slow, then there is abolutely no reason to change it.
The often prohibitive cost of rewrites is just another reason for me to
continue trying to convince everybody to write set-based code right from
the off.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I have a book called SQL PROGRAMMING STYLE that should be published by
2005 April that has some chapters on how to think in Sets instead of
procedures. There is no single, magic answer.|||>> I see many times in these groups (and elsewhere) that cursors should
be avoided. As a comparative newbie can someone explain to me the
reasons for this. <<
All of the performance reasons that portas, Kornelis and Sommarskog
gave are the usual reasons. But don't for get the lack of portability!
In spite of the SQL standards, cursors are still VERY proprietary. But
even if they were all perfectly aligned, there are enough
"implementation dependent" things to screw you over. For example,
there is a warning that is raised when a GROUP BY has a NULL removed
from one of the groups. Sounds good, since I might not want to run a
report if I have missing data in one or more groups ("And where the
hell is Smith's sales figures??!")
But this warning can be raised at DECLARE CURSOR, OPEN cursor and/or
FETCH cursor. This is going to change application program logic quite
a bit.|||Except, of course, that this schedules the event every 7 days for the
next 7 years...
Your where clause should be:
where (pk_number * 7) < 366|||Erland Sommarskog <esquel@.sommarskog.se> wrote:
>Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
>> It is my experience that at least 99% of all existing cursor-based code
>> can be replaced by set-based code. In most cases, the replacing set-based
>> code is shorter (less lines), easier to read, understand and maintain (at
>> least after you've mastered the learning curve to switch from procedural
>> thinking to declarative thinking) and -most important- performs much
>> faster.
>Our system has its fair share ot iterative processing, and maybe the
>most common good reason to use a cursor is that you have a stored
>procedure that performs an operation on a single set of values and
>you want to reuse that logic.
This is the only situation in which I've used a cursor so far.
I have a trigger set on insert into one table, and for each inserted
record I want to use it's contents to create and/or update the
contents of a record in a second table.
I've written a procedure to do the fairly complex update from one
record to another, and I call that procedure from inside a fairly
simple cursor loop that forms the main body of the trigger procedure.
I'm not too worried about cursor overheads here, because in general
only one record at a time is being inserted into the first table.
At the time I wrote this cursors seemed the only (and natural) way to
iterate through the records in the "inserted" table, which in this
instance is what I need to do as each record has to be processed
separately.
If there's a better/more appropriate way of doing this I'd be
interested to hear it. A lot of the "set-based" solutions I see here
just seem to be implementing loops using a table and from a purely
programming point of view (my background) seem a counter-intuitive way
of doing things.
--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/|||I would recommend that you don't use cursors in triggers. As already
discussed, if you have a legacy of procedural code that works on one
row at a time then yes, you may be forced to call that code in a loop
just because of the cost of rewriting your procedure in set-based form.
That's a pity because some day you may want to update more than one row
at a time and anyway you really don't need the overhead of a cursor
declaration in a trigger, even for a single row.
> A lot of the "set-based" solutions I see here
> just seem to be implementing loops using a table and from a purely
> programming point of view (my background) seem a counter-intuitive
way
> of doing things.
I don't know what you are referring to. Maybe you have an example? If
you mean using WHILE loops and SELECT statements in place of cursors
then yes, that is just cursor in disguise and all my comments about
cursors apply equally to those other row-by-row constructs.
It is true that declarative SQL requires a slightly different mindset
and it is often noted that procedural programmers find these methods
counter-intuitive. However, the relational model and SQL are the
dominant industry database standards with good reason and that is
hopefully a good enough incentive for the programmer to learn the
standard techniques and best-practices, aside from the very practical
considerations already explained.
--
David Portas
SQL Server MVP
--|||> If there's a better/more appropriate way of doing this I'd be
> interested to hear it.
Very likely there is a better way but we'll need more details first:
http://www.aspfaq.com/etiquette.asp?id=5006
--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote:
>I would recommend that you don't use cursors in triggers. As already
>discussed, if you have a legacy of procedural code that works on one
>row at a time then yes, you may be forced to call that code in a loop
>just because of the cost of rewriting your procedure in set-based form.
>That's a pity because some day you may want to update more than one row
>at a time and anyway you really don't need the overhead of a cursor
>declaration in a trigger, even for a single row.
So how would you advise to select a single row from the inserted table
without using a cursor?
>> A lot of the "set-based" solutions I see here
>> just seem to be implementing loops using a table and from a purely
>> programming point of view (my background) seem a counter-intuitive
>way
>> of doing things.
>I don't know what you are referring to. Maybe you have an example? If
>you mean using WHILE loops and SELECT statements in place of cursors
>then yes, that is just cursor in disguise and all my comments about
>cursors apply equally to those other row-by-row constructs.
Well that's what I thought. Most of these approaches seem to be a
loop through table, and it's not obvious (to the newbies) that this
would be more efficient than a cursor.
>It is true that declarative SQL requires a slightly different mindset
>and it is often noted that procedural programmers find these methods
>counter-intuitive. However, the relational model and SQL are the
>dominant industry database standards with good reason and that is
>hopefully a good enough incentive for the programmer to learn the
>standard techniques and best-practices, aside from the very practical
>considerations already explained.
I'm not disputing this, just asking questions so I can better get into
the correct mindset for SQL.
--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote:
>> If there's a better/more appropriate way of doing this I'd be
>> interested to hear it.
>Very likely there is a better way but we'll need more details first:
>http://www.aspfaq.com/etiquette.asp?id=5006
Well I was really just asking a generic question. The tables involved
are fairly lengthy (although as far as the logic goes only a few
fields apply) and I didn't think to fill my post with all the details.
I wasn't seeking a particular solution, just asking the general
question.
In essence I have
CREATE TABLE transaction
(
IDint IDENTITY(1,1),
tsdatetime
jobvarchar(20)
statuschar(1)
...
other transaction fields
...
)
CREATE TABLE jobs
(
jobvarchar(20)
statuschar(1)
last_updatedatetime
...
other job fields
...
)
Each time a transaction comes in I use the details in the transaction
to update the jobs table. If it's a new job I create a new record,
otherwise I perform an update. The nature of the update can depend
on the value of the new status, so that depending on the status
different values amongst the "other transaction fields" will cause
different updated for the "other job fields". Further processing may
occur for some status codes.
To achieve this I wrote a "processTrn" procedure which takes a single
transaction and executes all the (largely procedural) updates.
To call this procedure I created a trigger on the transactions table,
and it's there that I use a cursor to go through the "inserted" table
to extract each new record in turn and call the procedure on it.
To my mind this is a naturally loop+procedural process.
Note, because the transactions table is added to 1 record at a time by
an external process, the actual cursor loops in this case are usually
for a single record.
--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/|||> So how would you advise to select a single row from the inserted
table
> without using a cursor?
I *wouldn't* select a single row. The problem is precisely to AVOID
processing single rows of data and process the whole set of data at
once. This is what we mean by "set-based" code. By putting business
logic in a stored proc that opeartes only on one row at a time you have
forced yourself to call that proc once for each row. It likely doesn't
have to be that way but since you haven't explained what the proc does
I can't really advise on the alternatives.
--
David Portas
SQL Server MVP
--|||> If it's a new job I create a new record
INSERT INTO Jobs (job, ...)
SELECT job, ...
FROM Inserted
WHERE NOT EXISTS
(SELECT *
FROM Jobs
WHERE job = Inserted.job)
Note however that you should generally avoid duplicating data between
tables (except for key columns). Duplicated data is a problem in a
relational database and the goal of Normalization in db design is to
eliminate it. You should also aim to eliminate transitive dependencies
- i.e. columns that can always be derived from data in other (non-key
columns) - doing so reduces the need for triggers.
> otherwise I perform an update
UPDATE Jobs
SET ... ?
WHERE EXISTS
(SELECT *
FROM Inserted
WHERE job = Jobs.job AND ... ?)
> The nature of the update can depend
> on the value of the new status, so that depending on the status
> different values amongst the "other transaction fields" will cause
> different updated for the "other job fields".
That's not much information to go on but you could probably use CASE
expressions for this.
--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote:
>> If it's a new job I create a new record
>INSERT INTO Jobs (job, ...)
> SELECT job, ...
> FROM Inserted
> WHERE NOT EXISTS
> (SELECT *
> FROM Jobs
> WHERE job = Inserted.job)
Thanks. I begin to see how the procedural approach can be avoided.
--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/|||LOL,
yeah I guess you are right must have been late.|||John A Fotheringham (jafsoft@.gmail.com) writes:
> This is the only situation in which I've used a cursor so far.
> I have a trigger set on insert into one table, and for each inserted
> record I want to use it's contents to create and/or update the
> contents of a record in a second table.
> I've written a procedure to do the fairly complex update from one
> record to another, and I call that procedure from inside a fairly
> simple cursor loop that forms the main body of the trigger procedure.
> I'm not too worried about cursor overheads here, because in general
> only one record at a time is being inserted into the first table.
Normally, it is not a good idea ot have a cursor in a trigger, but if
you know your business well enough to be confident that one row-at-a-time
is the normal case, this sounds like a sound approach to me. From a
theoretical point of view, the trigger certainly could be improved. But as
long as the penalty for the cursor is low or non-existent, it seems very
difficult to justify spending time on a more complex solution.
That cannot be denied, if you want to encapsulate logic by putting
it in stored procedures, this is easier for scalar values than for
sets of values, since procedure parameters are scalar. It is possible
to work around this by sharing temp tables or similar, but only does
this increase complexity. You can also get recompilation issues that
are bad for performance.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
