I'm also sure that my preferences are shaped by an experience with some stored procedures which were very poorly written, which contrasted nicely with cases where the same logic had been moved into an application layer and then was organized there fairly cleanly.

And I would like to point out that when I said, "I wouldn't lean that way..." I meant that to be truly understood as a personal preference which others disagree with, and which I might choose either way on in practice. You demonstrated nicely that others do disagree, and confirmed at least one of the grounds on which they disagree. (The performance benefit from being able to rethink your database design.)

Plus finally I would like to note that the choice of which way to make this decision is not as important as who you have doing the work. Competent people can make either decision work, incompetent people (or even competent people with the wrong background or pressures) can make either fail.

-derby

I'd have to agree with Abigail-II on this one. Provided that you are unlikely to move to a DMBS that doesn't support stored procedures any time soon, SPs are as fundemental to decoupling your applications from the database schema, as assessor/mutator methods are to decoupling your objects from their underlying implementation.

They are also the DBMS equivalent of using perls builtin functions in terms of performance. Just as the most efficient way of processing data in perl is to, as far as possible, minimise crossing the boundaries between the interpreted byte-code of your program and the compiled C of the built in functions, so minimising the frequency (and volumes of data, but that isn't always possible) crossing the boundary between your application and the DB is the most effective way of using the DB.

Applications, especially business applications, shouldn't be concerned with how to get the information they need, ie. which tables, how to build the SQL queries etc. They should only need to ask for 'the lastest orders to be processed', or the 'Delivery details for order number nnnnnnn'. Using stored procedures is the best way of isolating the application from these details, especially if there are multiple applications that use each type of business object. If the schema has to change, all the changes are isolated with the DBMS, provided that the SP name and the data required by the application stays the same. I've also used SPs to allow the same application to talk to several DBMSs.

Sure, SPs can be badly written but so can Perl and SQL. Especially SQL! The vast majority of people using SQL have no idea of the costs of using JOINS, referential integrity, foriegn keys, badly chosen primary and secondary indexes. And even if you become expert enough in any given DB to understand these costs. Move to another DB and your knowledge may well be useless.

That's why really good DBA's are way more than just people that ensure the backups are done. Also why a good DBA becomes a specialist in the DMBS that he deals with. Unfortunately the ideal of SQL as a write once, run anywhere database selection and administration language is forlorn.

---

Examine what is said, not who speaks.
"Efficiency is intelligent laziness." -David Dunham
"When I'm working on a problem, I never think about beauty. I think only how to solve the problem. But when I have finished, if the solution is not beautiful, I know it is wrong." -Richard Buckminster Fuller

Stored procedures, if used sensible, are the layer between your program code, and the physical data layout. You could implement business rules in your stored procedures, but you don't have to.

As for stored procedures being the "backend", that's just a matter of perspective. Properly designed systems have many layers, and stored procedures are just one layer. If you put that layer in the "backend", it's in the backend. If you consider that layer part of the middle-tier, it's part of the middle-tier.

You should put your business logic where it makes the most sense for your solution. Don't worry too much about what lable is put on it. Don't chase buzzwords.

Abigail

That's all fine what C. J. Date says, and I'm not disagreeing with that. Stored procedures are good to separate the logical view of the data from the physical layout. Which is a totally different issue then where you have your business rules.

Stored procedures are good because of their encapsulation. It's the same reason you use accessors to access the state of your objects, instead of exposing the innards.

Abigail

First off, I agree. Having a decent set of stored procs can really simplify migration and changing situations, a stored proc might even return results from a totally different DB than the one the user is connected to. But I was wondering about one thing, I am a Sybase user, iirc you are as well. Ive noticed that a heavy use of stored procs is favoured by Sybase users. Since I've not had much exposure to other DB's I cant really say, but I have noticed that it seems users of other DB's tend not to go that way. Any thoughts from your point of view?

I've used Oracle, PostgreSQL and SQL Server. I push as much as possible into stored procedures on all of these platforms. It saves a lot of data moving back and forth between the program and the database. Oracle and PostgreSQL have very powerful procedural languages that can be used to write stored procedures (PostgreSQL stored procedures can even be written in perl). SQL Server seems a little weaker in this regard but seems to be getting better in newer versions.

90% of every Perl application is already written. ⇒

dragonchild

My main database experience is with Sybase and MySQL. Sybase has stored procedures, but MySQL doesn't have them. Which is one of the many reasons MySQL can't be taken seriously.

AFAIK, Oracle has stored procedures as well. I've done some db2 work, but that was mostly writing an qmail-db2 interface (in C); I don't know whether db2 has stored procedures.

Abigail

I don't know whether db2 has stored procedures.

It does, as do Informix and SQL Server.

I believe, but am not certain, that the concept of stored procedures (and triggers) began with DB2 in order to support CICS and IMS respectively.

I agree that MySQL has a long way to go before it can be consider a substitute for any one of the commercial RDMBSs. I've heard that PostGRE is moving up quite rapidly, but also still has a ways to go. That said, given the contrast in the funding for the commercial -v- "free" RDBMSs, they aren't doing at all badly given their relatively short histories.

---

Examine what is said, not who speaks.
"Efficiency is intelligent laziness." -David Dunham
"When I'm working on a problem, I never think about beauty. I think only how to solve the problem. But when I have finished, if the solution is not beautiful, I know it is wrong." -Richard Buckminster Fuller