Slashdot Mirror
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."
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."
I actually miss them in my current job with mySQL. I used to like the way you could run a stored proc every X period to copy "live" data to "public" areas. Or, archive "public" data after "x" period of time. But then again, I am a micro$oft developer at heart, and all this Perl, CGI, Java, (even COBOL) on old RS6000 systems gives me a headache sometimes.
why are you posting if you don't have an answer?
you are only giving me a headache.
Data controlled by the DBA good. Data controlled by developers or users... bad, very, very bad.
SELECT * FROM users WHERE CLUE = TRUE
0 Rows Returned
or if there are rows, fix it quick!
DELETE
FROM users
WHERE CLUE=TRUE
If it can magically make 503 errors go away - yes if it does anything else - no
If you have to ask, you'll never know.
Call me weird, but I like these types of flamewars. I've done a little PHP hacking with MySQL and I'm curious about the issue of stored procedures, since I've never had the opportunity to use them.
DBA: "I think we should use more stored procedures"
Project Manager: "That would be consistent with our n-tiered strategy"
Programmer: "But all of our business rules are in object libraries"
Division Manager: "Could I get the ranch dressing? Thanks."
DBA: "The right way is to put business logic in stored procedures. It's faster"
Project Manager: "We're standardizing on faster programs across the enterprise"
Programmer: "But we'll have to re-write the entire system!"
Division Manager: "Pass the pepper. Thanks."
DBA: "Oh, that's alright. We were going to upgrade the servers too. We can finish both jobs at the same time"
Programmer: "We're upgrading the hardware too?! So basically we're building the entire company from nothing again, right?"
Project Manager: "You're not being much of a team player"
DBA: "The new system will be more robust and have new features"
Division Manager: "Lets get the system built as soon as possible. We're laying off the entire division in a few weeks"
Programmer: "WHAT?! I just signed a new car lease!!"
Division Manager: "Pass the croutons please"
Business isn't willing to pay for products, innovation and careers, so we get brands, mortgage commercials and layoffs.
Pearl being my coding sister :)
(Of course, I meant Perl)
As a DBA it should not be your job to write or maintain the code. There should be a separation of DBA and developer just like church and state should be separate.
What offended me was his strong implication that the two -- PHP/MySQL programming and real DB development -- are mutually exclusive. It's an attitude that I see a lot on Slashdot, and it pisses me off.
:-)
Just:
*) Ensure that your company blows inordinate amounts of money on software with expensive support to cover your ass. It doesn't matter whether it works better, but you should be able to point at someone else to blame if something goes wrong. This also means that you don't actually have to understand the product, and can trust your DBMS vendor's salesmen for all your information, which will chew it up into bite-size pieces and spoon-feed it to you.
*) Act very self-important, talk down your nose to anyone that *doesn't* waste as much money as you, and treat your money-wasting as a mark of pride ("I have a $N million dollar budget this year!")
*) Place "real" before every class of software you work with, and vaguely insinuate that there are technical (especially reliability) failings in any cheaper product than the one you use. It worked marvelously for "real UNIX admins" for years.
*) Mention "Fortune X" frequently. For example "Fortune 500 DBAs with $5M budgets like me use *real* database software -- *we* can't afford downtime".
*) Avoid, if at all possible, knowing anything about software or computers other than database interfaces and possibly a scripting language or two. It's also good to choose a single OS, learn only that, and then slip occasional comments about how other OSes are unreliable.
*) Slowly begin slipping corporate acronyms into your speech. Use "ROI" whenever it's appropriate, and even when it isn't, to make it sound like other people are just slipshod and guessing numbers. "Enterprise class" is also good, since it implies, with only very vague factual requirements, that you work at a larger and more stodgy company that the person that you're trying to put down.
You'll be well on your way to doing "real" database work in no time.
May we never see th