Stored Procedures - Good or Bad?

superid asks: "I'd like to get opinions and real world experiences that people have had with database centric applications that rely extensively on stored procedures. I believe that most enterprise class databases such as Oracle, MS-SQL, PostgreSQL, DB2 and others implement stored procedures. MySQL has been criticized for not supporting stored procedures and will be adding them in MySQL 5. The ANSI-92 SQL Standard also requires implementing some form of stored procedure (section 4.17). So, I'm asking Slashdot readers: if you were architecting a highly data-centric web based application today from a clean slate, how much (if at all) would/should stored procedures factor into your design? Where are they indispensable and where do they get in the way?" "The arguments for stored procedures are pretty straightforward: 1) Centralized code; 2) Compiled SQL is faster; 3) Enhanced security (as our application is over 15 years old, and consists of much legacy code, reimplementation and feature creep that now includes over 3000 stored procedures). At one time we had a client/server architecture so those three advantages were relevant. However, in the past 4 years we have moved everything to web front ends and I have argued that this is no longer true. Does it really matter if my business rules are centralized in stored procedures or in a set of php/asp scripts (ie, in the web tier)? Is it really important to shave compilation time when connection and execution times dominate? (and overall response is ok anyway?) Since the focal point is the webserver, shouldn't security be done there, rather than the DB?

In addition, you either have to have a dedicated T-SQL or PL/SQL coder who then is the weak link in your coding chain, or your pool of developers must become fluent in both your scripting language of choice as well as the SP language. I have experienced both of these approaches and found this to cause bottlenecks when 'the database guy' is unavailable and learning curve problems (bugs) with new coders getting familiar with the db language.

Finally, after staying with our DB engine choice for all these years we are acknowledging that they may not be around forever. Management has asked us to look into migrating our data and business logic to another DB choice. We'd sure love to just be able to point the web tier at a new data source but that is unattainable due to a convoluted tangle of db specific code."

Comments

Re:Two answers.

[cas2000]

> Specifically, MySQL-driven Web sites with a
> great deal of "real database work" going on
> in the background.

you're not doing "real database work" for the simple reason that you're not using a real database.

you're using a toy - and no matter how good you are at using a toy, it's still a toy.

> PHP/MySQL programming and real DB development --
> are mutually exclusive.

i think the original poster was commenting on mysql, not on php.

you *can* do real programming in php, but i can't see why anyone would want to. it's basically a stripped down weird version of perl for people who are too scared of perl to bother finding out that if they can program php they know about 95% of what they need to program perl.

Re:Why stored procedures are bad. (10)

[jkarlin]

wow, maybe you should take 5 minutes before you start hacking your code together to talk about your database and the things you need it to do. I know developers like you that hear half the problem and race to their computer to start coding. I'd rather find a whiteboard, draw some pretty pictures, and figure out what the hell we're trying to do first. And you know what, except for the simplist projects (which wouldn't have 2 people anyway), I'd finish before you and work a hell of a lot less.