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:Meeting

[mitchy]

Project Manager: "So we need to start this project off as soon as possible, as this will become the foundation of several different applications."

Programmer: "Wow, kewl, we should do it all in Perl."

DBA: "We will definitely need to have a robust database if it is to power all these different apps."

Programmer: "MySQL rules man! Besides, it is free."

Division Manager: "Hmm, free is good. Can you manage the data in MySQL?"

DBA: "Well, actually MySQL doesn't do..."

Programmer: (interrupts DBA) "We don't need any of that relational integrity crap, that's just marketroid speak for Oracle, dude! All we need are a bunch of tables. I can wrap all of the SQL in a Perl package."

DBA: "Then you will have dirty data, and we can only write apps in Perl if we have to use that library for access. This is really putting all of our eggs in one..."

Project Manager: (interrupts DBA) "So this is free, right? I like free. My stepson talks about Perl, so it must be a totally hip language." (snorts)

Division Manager: "So all of the database logic is now going to be written in Perl, right?"

Programmer: "Absolutely! The database is only there to store data. We can check everything in the application. It will all work perfectly, because I am 3l337."

DBA: "But that's like just using flatfiles, how are you going to..."

Division Manager: (interrupts DBA) "Hey, if this is free, and you can do all the code, then we can cut half of our database specialists to save costs. That is a great idea."

DBA: "You can do that, it is your company and all, but without triggers and some sort of stored procedures..."

Programmer: "What's a stored procedure? Trigger? Isn't that a horse from the 60s?"

Project Manager: "Heh, that's funny."

DBA: "And if the next application needs to be done in Visual Basic, or some other language? Then you will have to write the whole thing over again because..."

Programmer: "Look, this is easy, me and my buddies from the high school can pound this out over the weekend."

Division Manager: "Now THAT's team spirit!"

DBA: "But how can you ensure consistency of data? Your database cannot even enforce minimal compliance of..."

Programmer: "Look, grumpy dude, I don't make mistakes. My code would never create dirty data, and besides, I'm 3l337."

Project Manager: "Wow, my budget is going to look great after this."

DBA: (leaves room, goes home and beats dog)