Re: stored procs? why?

On Thu, 2002-02-28 at 16:28, Perrin Harkins wrote:
> All this talk about stored procedures in Perl makes me wonder.  Aren't 
> stored procedures the kind of legacy architecture everyone is trying to 
> get away from?  Aren't clustered application servers (like mod_perl + 
> selected modules) a better approach in general?
> 
> I usually hear people support stored procs by saying that they are 
> cross-language, but so is SOAP and HTTP.  So why is everyone so hot to 
> code stored procedures?

I'm at the beginning of a large project to port an application where all
of the business logic and smarts are in MS SQL Server (T-SQL) stored
procedures. Hundreds of them, some of them quite long. The people who
created the app over the last few years know a *lot* about stored
procedures. Their surface impressions:

 - Coding stored procedures is painful because many constructs we take
for granted in Perl (or Java, or whatever) just aren't there. It's
difficult to create generic code except for very small tasks, so there
winds up being lots of repitition.

 - Poorly written and inefficient SPs bog down the server, not just a
single client 

 - Testing and debugging is difficult

 - But... when coded correctly, stored procedures run blindingly fast.

I have a suspicion that while most of the SPs in this project will be
eliminated, there will be a remaining few that do some heavy batch
processing and will need to be ported to various databases. We'll see.

IMO, the need for them really depends on what you're doing. If speed is
the main requirement, you know you're going to be using the same
database system for the next 10 years, and you have an experienced SQL
programming staff, it's not necessarily a bad decision.

I think programming SPs in Perl would almost certainly be a bad decision
for large systems. Finding solid SQL programmers is difficult enough;
I'd guess finding solid SQL and Perl programmers would have to be *much*
harder. It's a cool idea, though.

Chris

-- 
Chris Winters ([email protected])
Building enterprise-capable snack solutions since 1988.