Re: pros and cons of using stored procedures

Rusty Wright <rusty_at_socrates.Berkeley.EDU> wrote in news:uie66aupqhv.fsf_at_socrates.Berkeley.EDU:

> Can anyone recommend a book, web page, etc. that explains the pros and
> cons of using stored procedures, as opposed to putting the code in
> your front-end code? The background info is that a group of us are
> designing a database and we'll be using Oracle, and for the front-end
> we'll be using a web interface probably with the PHP scripting
> language as the glue.
>
> One of the people on the team is a proponent of using stored
> procedures and another is against it. Obviously, none of us are
> database experts and I'd like to be able to read up on stored
> procedures so that we can argue the issue intelligently.
>
> Thanks
>

There is no single only one right way to implement a solution. It all depends and the trade-offs that are acceptable to you.

I'm basing my response on the following understanding of what I think you described. At least 4 "systems" are involved.

1) Thin client (a.k.a. web browser) 
2) Web server (WS)
3) Application server (AS)
4) Database server (DBS)

2,3,4 could be all on the same box but don't have to be. To design in scalability I'd assume 4 separate boxes at a minimum. In layers 2,3,& 4 could actually be multiple systems.

When you refer to "frontend code" I'm not clear on what you mean, but assume that it runs in layer 3.

If you have "processing" that involves little or no DB interaction then layer 3 is a good place for it. However if the "frontend code" requires frequent DB accessing, then I see little to be gained shuffling packets between the DBS and the AS.

Another possibility involves using Java Server Pages (JSP) or creating Javascript that actually runs on the thin client. I caution that the size of this code and/or bandwidth limitation could results in SLOW response times for the human behind the browser.

Any solution can be made to "work".
But is the solution scalable & easy to maintain.

HTH & YMMV! Received on Fri Sep 07 2001 - 20:29:31 CDT