Re: stored procs? why?

At 08:54 AM 3/1/2002, brian moseley wrote:
>On Fri, 1 Mar 2002, Matt Sergeant wrote:
>
> > Given that though, it becomes very easy to see that if
> > you do have a large transaction (including multiple
> > table updates for example), it's often safer to
> > encapsulate that into a single stored procedure than it
> > is to write some large chunk of Perl that turns off
> > AutoCommit, does the work, and then commits. Plus it
> > ensures people can use other languages to execute the
> > same transaction. If you want to use SOAP/HTTP to
> > emulate a stored procedure to achieve the same effect
> > you're welcome to, but beware of the performance.
>
>beware the tremendously inflated development schedules that
>can result from this approach. at critical path, we used a
>lot of pl/sql stored procs for validation, integrity checks
>and transactions. almost every development schedule for
>adding or modifying features to the service was blown out by
>the stored proc development effort.

As usual, I suppose it "depends". We use stored procs quite rarely these 
days with web apps. But I recall in my client/server programmer days that 
if I had to make a change to business logic in a PowerBuilder app, I'd have 
to deploy the powerbuilder app to many users.

Whereas, if I had a lot of logic in the stored procs, it was quite easy to 
just update the stored proc.

Of course, middleware "could've helped", but back then client/server wasn't 
called client/middleware/server. :) And back then, middleware servers were 
even worse than they are today.  Actually overall coding in stored procs 
was fairly easy (on Sybase).

However, on a web based application, your code is centralized and not 
distributed to different workstations, so the value of using stored procs 
goes down quite a bit.

However, if you have many programs accessing a database (Web front-end, VB 
front end etc) then using stored procs as a poorman's middleware can be a 
valid architectural decision in some circumstances. And is probably faster 
than writing SOAP or other middleware (which will also add man-days to your 
projects).

Later,
     Gunther


__________________________________________________
Gunther Birznieks ([email protected])
eXtropia - The Open Web Technology Company
http://www.eXtropia.com/