Re: pros and cons of using stored procedures

I had a chat with one of the senior Oracle techies at Openworld about where the logic should go, and what language to put it in. Basically anything which involves table access should be in PL/SQL packages, not stand-alone functions or procedures. Computing, but not data, intensive logic can be either in the database as Java or in the application server layer, 3 or 4 below.

No surprises there, then.

"Ban Spam" <ban-spam_at_operamail.com> wrote in message news:Xns9115BC1C8D814SunnySD_at_24.0.3.73...
> 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 Mon Sep 10 2001 - 02:10:52 CDT