On Friday 01 March 2002 07:37 pm, Terrence Brannon wrote: > On Friday, March 1, 2002, at 11:38 AM, Dave Rolsky wrote: > > On Fri, 1 Mar 2002, Perrin Harkins wrote:
> 1 - This is just how the group I worked with at Oracle had things > set up. And they too had memory problems. > > 2 - Why can't you just get several database servers and load the > stored procedures into all of them? > > 3 - I created a CPAN module, SQL::Catalog, which provides some of > the benefits of stored procedures (separation of SQL and Perl, > centralization of all SQL, access to SQL via file or memory cache > or straight from DB) and actually provides more than stored procs > in some ways because you can have each query parsed and the > statistics on the query stored so you can generate reports on the > types of queries you have in the system. Perhaps I'm missing something and if so, I apologize for being dense, but this discussion of stored procedures is puzzling me. At my current job, we use stored procedures AND application servers with very little problem. You guys seem to be forgetting that a major reason people use stored procedures is to provide a performance boost for complex queries because SPs get compiled and cached on the database server. SPs can really help you out if used judiciously. If you are using straight SQL queries only, then you are missing out on that. 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 can see where overuse of this might create memory problems, but as I said, judicious use can provide a great performance gain. Furthermore, if you've properly abstracted your database access from your program logic, you should be able to easily switch to platforms that don't support SPs just by changing the SQL calls. 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. One other question: Why is this discussion happening on this particular list? I guess I missed the opening message that would explain the tie-in to P5EE, but I can't see where P5EE could possibly be dictating or proscribing the use of SPs in its design. jpt
