Re: stored procs? why?

At 12:53 AM 3/3/2002, Perrin Harkins wrote:
> > One might think you'd gain similar advantages by doing a "prepare" on
>your
> > sql queries prior to running them, but preparing your SQL queries
>prior to
> > running them only really helps when you are going to run them more
>than once
> > during the same connection, and they provide no query optimization on
>the
> > database side, whereas stored procedures are compiled, optimized, and
>stored
> > in the db server's memory for later use by any process.
>
>I could be wrong, but I think that with Oracle the queries you send will
>be kept in the database's query cache, just as if they were saved as
>stored procedures.  Using bind variables helps limit the number of
>unique queries and keep things in the cache.  It doesn't really matter
>though.  This discussion is really about stored procedures that have
>application logic in them, not just saved SQL queries.

I don't know about how Sybase does it now, but it used to be that there was 
no such thing really as a prepared statement in Sybase with placeholders. 
You could make up such a construct via the API, but it didn't do anything 
special in Sybase.

Stored Procs was the preferred mechanism for performance. And even so, in 
Sybase once a stored proc is compiled (if you set it up to do it), it is 
stored in disk with a pre-constructed query plan (which also takes CPU 
cycles to generate). So even if you shut your database down and start it up 
again, you'd still have a performance improvement from having precompiled 
and pre-prepared query plan stored persistently in Sybase.

> > I'm just saying that I don't see why it has to be so black and white
>here.
> > SPs are good; n-tier is good; in fact, it's all good, unless you
>overindulge
> > in any of it.
>
>I don't think SPs are good.  That's why I raised this question: to hear
>why other people think they are.

I think they used to be much better than the are now. 5-10 years ago there 
were less middleware choices and you were talking about client-side 
programming in VB not Web.

In the Web I do believe the utility goes down quite a bit. However, even 
so, I find that stored procs are useful for some occasional tasks.

> > One other question:  Why is this discussion happening on this
>particular
> > list?
>
>There was a thread with several posts from people who seemed excited at
>the prospect of writing SPs in perl.  I thought that was a strange thing
>to want, so I asked why.

I think allowing SPs to be written in Perl is something to be a *little* 
excited about. One major disadvantage (IMHO) of SPs is the language is 
quite annoying to deal with. If Perl can run on your DB, why not? Maybe 
that's not so bad if you don't want to build a middleware server and may 
need to access the database from more than a web app.

Later,
     Gunther