Stored Procedures - Good or Bad? 629
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."
Stored Procedures are a must (Score:5, Interesting)
Also, when it comes to long-term database maintainability, putting your database logic in stored procedures allows the db admins to get an accurate overview of what objects/tables are in use and which are no longer needed. At my company, where we have over 20 databases, this is an absolute must.
Generally speaking, I use dynamic SQL during initial development and move to stored procs for QA and production.
Life without them... (Score:5, Interesting)
Most of the development I do at my job is Coldfusion+Fusebox with SQL Server on the backend (I don't care if you hate MS, don't bother knocking SQL Server) and stored procedures just make life easier. They're also handy in the instance that you may have multiple front-ends written in multiple languages accessing the same database in many cases. Making a change to the way data is returned is far easier to do in one stored procedure than in X number of front-ends. One of the main reasons we don't use mysql is because the stable versions don't have them.
Good and bad (Score:1, Interesting)
This means several teams can develop apps against the same database (often using completely different languages, and with completely different goals). The business logic isn't hidden in some obscure class that might be in a language you aren't fluent in.
The bad is that they tend to make the individual apps difficult to debug. sp's often throw errors that are non-obvious, and if they are buggy, will do very strange things to your data. Often, hours of debugging latter, you realize it had nothing to do with your code, but was a bug or bizarre error condition in the sp or trigger.
Lately, the databases are allowing sps triggers to be created in more modern languages. Postgres IMHO kicks normalizes everyone elses ass with it's ability to use python, perl, php and ruby (among others) to create your sp's. Then at least, it is code that is a big more understandable.
Good (Score:1, Interesting)
They provide a great deal of security if used properly. Not only do I not have to allow my users to have ANY direct access to tables, but I ahve the ability to change underlying structure more easily.
It is wonderful to make the boss' suggested change to his favorite application without having him even close out of it.
The downside is that you have an additional layer of code to manage. However, I have done a lot of code automation, and 90% of my sp's can be written with a few clicks of the mouse...
Re:Stored Procedures are a must (Score:5, Interesting)
Re:Two answers. (Score:3, Interesting)
No offense, but doesn't this mean you put together web sites in PHP, as the grandparent suggested?
Depends on your design methodology... (Score:3, Interesting)
If you are designing a web application, then I find it much more maintainable to utilize DAO interfaces & impls [sun.com] since this allows you to make changes that might be necessary should you experience an unexpected change in your environment.
Need to move from MySQL to Oracle? Simply override any db-specific code from your ANSI Impl, and go.
Although if there is no chance of an environment change, stored procedures become much more attractive.
Re:Good or bad? (Score:3, Interesting)
If the code is about rules of your application, stick it in your application code. i.e: "A User can only have 3 Widgets checked out at a time unless they're a Super-Widget member"
Alternatively if it's about data, it should be in the DB. i.e 'Users have a Type which can't be null unless the user is Inactive'.
I've seen both sides abusing this, one company I worked for refused to use stored procedures or Referential integrity and built all their persistence logic in code. The app sucked.
Another said 'all business rules must be in SQL stored procedures'. They never delivered in time.
It's all about balance, and identifying what's business logic and what's persistence logic.
Re:I don't use em unless I have to (Score:2, Interesting)
Re:I don't use em unless I have to (Score:3, Interesting)
Wow, that almost sounds like a bug. I hope you have a good process around that.
That's where I've always had a problem. Rolling out a new version of my code requires some DB support. The DB support has to be synchronzied and sometimes reversable. When dealing with stored procedures, there are two simultaneous code pushes that must occur during the same schedule. We have the code that updates our servers, and the code that code relies on in the database.
Nowadays, we have a package of code that goes out which may require some schema changes or a bit of migration, but that's about it.
Personally, I rarely put SQL commands in my code. I leave it all in the procedures on the server.
Instantiating the class in code looks like dealing with any other class. You get an instance, call a few setBlah methods with the appropriate types, and tell it to go. It validates you provided enough parameters, and submits the query for you.
It acts a lot like stored procedures, except they act more like application code, *and* you can chain them together transactionally.
I built this abstraction layer with the intention of using it for accessing stored procedures originally...however, I found that the DB I was using wouldn't allow me to call more than one SP in a transaction. That was an immediate show-stopper.
SQL 2005 will allow the use of C# in stored procedures which should increase their usefulness.
Wow, not to me. All that does is make it so you can never ever use a better database. Our current application runs on SQL Server, but we have customer requirements to run on Oracle. Good luck with that when your DB has to run C# application code.
In a team environment it is easy to have someone work on the procedures while you work on the code. (I know this can be done with interfaces and classes, but this is also very convenient)
Sure, and on my team, the DB guy can write and maintain the classes in this language I made without knowing the language the rest of the application is written in (another original design decision). We end up doing most of them ourselves, though.
Stored Procedures Dont Help (Score:2, Interesting)
1) The DBAs
2) Junior Microsoft developers (typically VB)
3) J2EE developers (N-tier developers)
My experience has been that DBAs are usually proponents of stored procedures. There are two simple reasons for this.. familiarity of database engine and job security.
The Junior developers who typically develop two tier apps prefer stored procedures for performance reasons, because there is usually a big-a*s backend database server which can lift a lot of the data processing load.
Then there are the N-tier developers who are seasoned enough to pick the right architecture. These people prefer non-stored procedural approach. They understand that the database is a persistent store and business logic should be in the middle tier. It gives them flexibility to swap database engines and business logic modifications are localized to the code modules they are working with.
Good, like most things if they are used properly. (Score:3, Interesting)
I used to work for a large web hosting company that wrote there billing software frontend in Delphi, and kept all the business logic in stored procedures on MS-SQL. It got so bad, they even ended up having a stored procedure that generated HTML/Text invoices for customers! Ever tried doing text layout in a stored procedure? It was absolutely nuts, but once they had started putting all the business logic (and much more) in stored procedures, its hard to stop without "splitting the code-base".
They were also scared to upgrade from MS-SQL 6.0 to anything newer for fear of it breaking their stored procedures. They tried at least once and failed miserably. As far as I know, to this day, they are still running MS-SQL 6.0.
This whole issue basically put a strangle hold on the company, it took forever to "innovate" and they eventually got bought out. The new parent company has spent over a year trying to migrate away from this "stored procedure" mess.
I think stored procedures are good, but only in very specific circumstances. If you design and code your application properly, there is usually very little need to start down the slippery slope of stored procedures.
keeping the logic in the code vs. performance (Score:3, Interesting)
i'm quite unsure of myself about this, though. at the moment i'm working on a budgeting application, and both performance and productivity are becoming an issue.
example: to aggregate budgets over a time period, i retrieve the budget objects for each budget period individually, and accumulate the aggregate data in code. this takes quite a bit of extra coding, and execution time is quite slow, however; doing aggregation queries in the db would certainly give better performance, and it would be a lot easier to slap together some queries instead of writing all that code.
so, the way i look at it, it comes down to a question of science vs. engineering. the scientific impulse is to adhere to the theories of keeping logic in one place, and respecting the objects' ownership of their data. the engineering impulse is to use the technique which is faster and easier to implement.
i guess at heart i'm more of a scientist than an engineer.
i'd love to hear others' takes on this question, btw.
Re:I don't use em unless I have to (Score:3, Interesting)
Re:I don't use em unless I have to (Score:4, Interesting)
That depends on your fundamental architecture. For smaller applications environments your app servers and RDBMS are often on the same box. Not great for security, but it often brings the price down to a point where the project can go ahead.
With all of your "traffic" in-server many of the vendors stacks will let you use IPC messaging instead of IP messaging, which can boost performance rather significantly.
The other thing to consider is that for environments like the full IBM stack, you're expected to have a cluster of DB/2 UDB servers with a hefty, hefty backbone in the data center.
Like I said, the vendors have different approaches. Oracles is monolithic -- get it all into one big server. DB/2 is pure database, and expects to have other architectural components in use when building an application. Sybase tries to walk a line that can live in either variant, with interesting ideas of it's own. Postgres has some really slick custom data type support.
The point is that within the overall application cluster, you can get the same kind of performance out of any vendors RDBMS is you're using the appropriate application stack in a properly configured cluster. You just don't architect the solutions for the different products in quite the same way is all.
Re:I don't use em unless I have to (Score:3, Interesting)
Stored procedures aren't good or bad...they just are. Passing a value judgment on whether they're good or bad is completely dependent on the situation.
And in theory, there's no difference between theory and practice. :-) It's true that every situation is different but from my perspective, sprocs (stored procedures) should be the only way that application programmers can alter a database. I'm an SQL programmer. I carry a coffee mug.
In my opinion, applications shouldn't have any more detailed knowledge of a data base structure than any other OOP language's object.
Back to the original poster:
Is it really important to shave compilation time when connection and execution times dominate?
An easily overlooked advantage of sprocs (and views) is that it allows the data base structure to be changed without changing a single line of application code. I have routinely made changes to a live database with little fuss whereas my front end associates sometimes need to do messier stuff such as (essentially, if not literally) restarting the web server. That is, front end changes are much more likely to interfere with operations than back end changes.
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, ...
Weak link? After seeing some SQL code of front end people, I totally resent being referred to as a "weak link". :-) I was a dedicated T-SQL programmer and I've been out of work for nearly a year. :-( Most job requirements I come across list SQL almost as an afterthought -- as if good SQL code isn't important.
Simple. Stick with SQL as the sproc language. Well ... it would be simple if all SQL sproc implementations were alike. I started working with PostgreSQL after working many years with T-SQL and I was totally amazed at PostgreSQL's shortcomings (sproc parameter definitions are downright brain damaged). After further research the vague nature of the SQL "standard" totally staggered me. Which lead to ...
I have ... found [using a dedicated SQL programmer] to cause bottlenecks when 'the database guy' is unavailable and learning curve problems (bugs) with new coders getting familiar with the db language.
But aren't bottlenecks unavoidable when any absent person's code is causing problems? Conversely, there are some things that can't be done without well written SQL so the problem remains that somebody on the team should be proficient in SQL.
Utlimately there is no clear-cut answer to the question since, as I alluded to, SQL implementations vary wildly. So you either make the final decision based on which SQL server you use OR choose your SQL server based on which approach you decide to use. If my inability to find a job is any indication, you (the original poster) will choose to avoid using sprocs. :-/
Re:I don't use em unless I have to (Score:3, Interesting)
1) General security - yup, SPs win hands down here.
2) SQL injection attack - using prepared statements and parameters, this *shouldn't* be a problem with a Java or
Given an app which treats all user input as parameters rather than straight SQL, how is the application not secure from injection?
Re:I don't use em unless I have to (Score:3, Interesting)
Yes, if the only thing that has access to the database is the application logic.
I feel that you're better off putting all of your business logic in one place. That can be the database, of course. I know a very clever DBA/developer who built a financial exchange system entirely in the database, accessing everything via stored procedures. It was very cool, as the database took care of many of the hard parts of building a system like that.
But personally, I think putting all your business logic in a database's proprietary language is a pain. The development tools aren't as good, you have a big vendor dependency, and it's hard to find developers who can work in that style.
Instead, I think of a database as the place where the application puts objects when it doesn't need them. In that case, there's no reason to give anybody but the app access to the database. Sure, people may need to run Crystal Reports, but you shouldn't let them do that against the production database servers anyhow.
So these days when I build things, I put no code in the database. And once I made that choice, it freed me up to not even have a database. That makes refactoring much easier, speeding development. Sometimes I still end up using a traditional SQL database, but that's now a choice for me based on performance characteristics, rather than a default.
Makes maintenance simple (Score:1, Interesting)
I suppose this doesn't apply to thin client applications, but I work on a thick client application and at the minute, most of the business logic is in the client. Whenever we ship a bugfix or new version, the client has to go round and update dozens of machines with the new EXE. We made this a bit simpler by writing a program that sucks new EXEs out of the database and replaces the client app, but it's an optional extra and people don't like paying for stuff.
I recently did a major enhancement and put all the business logic in the database via triggers and stored procedures. The only code in the client app is used for viewing the data the database business logic produces. If there's a bug, we ship a SQL patch to update the database with the new trigger/stored procedure. No wandering round updating EXEs. Clients love it.
Word of warning: If you're putting business logic in an Oracle database using triggers and stored procedures, keep the triggers as small as possible by putting common code in stored procedures. When the database boots, it compiles stored procedures and functions, but not triggers, which are compiled when the trigger is fired. If you've got a large trigger getting fired frequently, your app is going to grind to a halt pretty damn quickly. This might have been changed in Oracle 9i/10g (I'm using 8i).
DB Coder (Score:1, Interesting)
"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."
I've seen the SQL our Java Developers write *shudder*. Seriously, sounds like resource (i.e., people) allocation problems. I write all the sps for our web apps (Oracle's PL/SQL), and would argue that putting it in the db gives you:
1. Better speed because the DB is made for munging the data, and it's one trip to the db server. The DB Engine is processing all the SQL anyway. In the days of CGI and Cold Fusion the db was the fastest place to put data manipulating code (including selects) and it still is.
2. More security through secured passwords and roles (password protected). Commercial RDBMS support things like login-triggers and 'Virtual' Databases within the db to limit data access.
3. Arguably simpler maintenance/enhancements. If the logic is on the db side, and you need to make changes, you have one point to run your changes - depending on the scope, you may not even need to re-compile/re-deploy code on multiple app servers. Using Java to store business logic guarantees that I have to a) 'nicely' drain all servers of active sessions b) take each server offline, redeploy the java c) bring each one back online. A process that will take hours. My maintenance window for db changes typically takes 15 minutes (often less).
4. Freed of SQL tasks developers can focus more on application design and GUI performance.
Re:non-proprietary stored procedure languages (Score:3, Interesting)
I agree. However it should be noted that just changing the prodcedure language doesn't avoid proprietary lock-in. Writing a stored procedure in any language requires writing to a specific API with specific structures in a specific context for accessing, manipulating, and returning data. The fact it's written in C or Python or PHP or PL/SQL may make the stored procedure easier for someone to understand (if they're more familiar with those languages-- and I agree with a previous poster that PL/SQL beyond the most basic is difficult to understand, and don't get me started on T-SQL in MSSQL!), but most definitely doesn't make it portable. You can't really pull the procedures out and use them elsewhere--another DB or in some middle layer of software. Creating any significant DB functionality with stored procedures will result in a reduction in the portability of the application. You're locked in, even if it's a lock in to PostgreSQL.
Now don't get me wrong, I don't think this is a bad thing, and I'm in favor of using stored procedures, and have written quite a few of them, for Oracle and for PostgreSQL and MSSQL. They keep key functionality centralized, and allow less experienced coders to do useful work without having to understand all the complex data relationships, integrity rules, security policy, and such that are a part of any non-trivial DB application. I've also noticed that PostgreSQL's "native" procedure language is getting more and more compatible with Oracle's PL/SQL, making Oracle ironically perhaps the least subject to proprietary lock-in.