|
|
Posted by Erland Sommarskog on 10/01/94 11:43
(JayCallas@hotmail.com) writes:
> * I created several stored procedures that take parameters and inserts
> the data into the appropriate tables. This was done for easy access/use
> from client side apps (i.e. web-based).
>
> Proper development tactics says to try and do "code reuse". So, if I
> already have stored procs that do my logic, should I be writing a
> second way of handling the data? If I ever need to change the way the
> data is handled, I now have to make the same change in two (or more)
> places.
Undeniably, this is surely a common reason why people end up with
cursors. They already have a procedure that performs an operation on
a scalar set of data, and now they need to do it multiple times. We
have plenty of such examples in our database.
When you face this situation you have a tradeoff: speed up development
and run a cursor and take the performance cost. Or rewrite that
stored procedure so that it deals with data in a table. Typically
when we do this, we use a temp table for input or more often a process-
keyed table. (See http://www.sommarskog.se/share_data.html#usingtable for
a discussion on this.) You keep the old scalar procedure, but to not
duplicate logic, you make it a wrapper on the set-based procedure.
Rewriting a scalar procedure into set-based is not a trivial task,
not the least when you code with performance in mind. And not the least
that at least during a transitional period there will be loops that
call the set-based procedure for one row at a time. There is certainly
an overhead of getting data from a table rather than from parameters.
So there is definitely a trade-off here. But if a loop today is the
best bet, it may not be tomorrow, because as the data grows in size,
performance becomes an issue.
I should add that I'm here talking of procedure where logic is really
complex. As the others have said, code reuse is not equally much a
virtue in SQL as it is application code.
> * Different data from the same row needs to be inserted into multiple
> tables. "Common sense" (maybe "gut instinct" is better) says to handle
> each row as a "unit". Seems weird to process the entire set for one
> table, then to process the entire set AGAIN for another table, and then
> YET AGAIN for a third table, and so on.
I'm not sure that I understand this point. As I mentioned, I usually
have my input to my set-based procedures in a table, so I don't really
see the problem.
> * Exception handling. Set based processing means that if one row fails
> the entire set fails. Looping through allows you to fail a row but
> allow everything else to be processed properly. It also allows you to
> gather statistics. (How many failed, how many worked, how many were
> skipped, etc.)
Yes, this is a point that is often over-looked. Judging from some of
the other replies, some people appears to prefer ignore this issue,
but depending on your business requirement and the likelyhood for
errors, this may be a decisive reason to run a cursor.
For instance, I recently rewrote a stored procedure in our system
which makes a contract note definitive. A stock broker have thousands
of contract notes every day. Typically customer notes stay open all
day, but at the end of the day they should all be made definitive. In
conjunction with this there are a number of updates to perform. With
the old procedure, the notes were handled one by one, and if an occasional
note failed, that was no disaster. It could be sorted out the next day.
For the new procedure, I do perform some initial validations, and notes
that fails these validations will not be updated whereas the rest
will be. But from the point that I've started to perform updates, there
is not really an easy way out if there is some problem with a single note.
That means that the entire operation will fail. In our case, this should
only happen exceptionally, but if it happens the customer be in dire
straits, not the least that this will typically happen after hours.
(Please don't suggest that I should validate everything in advance.
We're not only talking constraints, but assertions in sub-procedures,
whereof some are very complex.)
A possible fall back in this case, that I have implemented, but maybe
I should would be that if there is an error, I would then start to take
the notes one-by-one.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|