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."
Bad, of course.
I only put triggers or constraints or whatever in the database for one reason: to make sure only valid data enters the database.
For instance if ColumnA must be between 5 and ColumnB+34, that should go in the database. The database itself should guarantee the data is clean. "Data logic" you could call it.
Business logic and everything else should go in the higher layers. There is some ambiguity about what is "data logic" and what is "Business logic" but it's usually pretty clear.
Why? Maintanence. The stored procedures tend to rust in place over time. If you're an "agile" developer you'll go nuts not being able to refactor business logic or have unit tests check your database procedures. If you're a "BDUF" (big design up front) shop, you might like it, but thankfully many are moving away from that.
They let you abstract data access procedures. It's kind of like asking "why are functions a good thing?"
Because a person who knows what the hell they are doing can write a good function (or stored procedure) containing what the hell they know so that a person who doesn't know a hell of a lot about what the first person knows but a lot about something else on a higher level can interface with that logic and get a result without having to be a guru.
Or something like that...
Dissolve... Resolve... Evolve...
In addition, depending on your situation, it shouldn't be too hard for the developers to learn how to write stored procedures for the database. Once you know one language, learning another isn't that hard. The developers might write some inefficient code at first, but they'll get better very quickly. Plus, it will give them a better idea of how the database really works and performs, improving their overall designs.
Of course, IANADBA (I am not a DBA), so take it with a grain of salt.
Besides the optimization the DB might do on SP's as opposed to dynamically created SQL statements, SP's are nice from a security point of view.
:).
You have to be extra careful with dynamic SQL due to SQL injection bugs that we all know about. This isn't really an issue when you're dealing with stored procedures that take defined data types as opposed to creating SQL on the fly based upon your data (which could have injected SQL).
Controlling which DB accounts can use what stored procedures is also handy mechanism for determining permissions. Stored procedures represent what all your application might do, so picking which DB connections (which have credentials) can access these is a nice place to control those permissions.
Granted, you can still do lots of stupid things to mess up security
Also, there are places where SP's are not really possible. Severely dynamic reports are a good example (assuming you allow that functionality in your application). There's definitely times when you HAVE to generate SQL on the fly. In any event, try to create a "data access layer" in your code and if you have to dynamically generate SQL, run all sorts of checks on it with regexp's etc to check for injection.
Views don't let you change the format of the data, all they allow you to do is to present data from multiple tables as if they were in one table.
triggers that keep history tables
triggers that keep referencial integrity that a foreign key simply can't do
stored procedures that loop through large amounts of data that only return a small amount back
A stored procedure can be an evil, evil thing:
implementing complicated algorithms that can't be debugged
creating HTML
I've seen both sides. It's like asking if javascript is a good thing for web browsers.
John, who gets a 503 when trying to log on
The other major issue is security. If your app has the ability to view tables in the database, then breaking your app, or finding the login and password your app uses compromises the entire system. It is much better to implement security policies in the database/stored procedures, and only allow users/apps to call stored procedures, possibly even using the procedures to validate login information on each procedure call. If someone managed to get the (non administrative) password to your database, would you rather have them be able to do anything they want (or even just view anything they want, such as credit card or health information), or have them only be able to do exactly what they were able to do without the password?
Especially if login information is used as a parameter (I always use username and password as parameters to look up access levels in all user-executable scripts for this reason), if the user cannot access a single table in your database, then they can only do what the stored procedures allow them to do. This allows you to have a central place where security logic is executed, and business logic may either be in the procedures (ideal), or in the app. Just remember, leaving business logic up to the app may pose a risk if the app has a flaw, if the procedures allow the user more access than the app does. If your database security model has more flaws than your app could, then maybe you should consider switching databases.
--That's the point of being root, you can do anything you want, even if it's stupid.
Thank you, just to add a bit:
Would you want to send 5 million rows to the application just to check a few fields in each of them, and how they relate to records before and after them? Hell no, sending 5 million rows uses a ton of bandwidth, even for small row sizes. Also, SQL is a language for set operations, while most (99%, Lisp/etc are other 1%) application languages are designed around a single value, not a set of values. For example:
To check if array a[50000] is a subset of array b[2000000], in most languages, you must somehow iterate through both arrays (sorting would help, if possible) and see if each value in a (all 50000 of them) also exists somewhere in the 2 million values for b.
In SQL, something like SELECT COUNT(*) FROM a WHERE a.key NOT IN (SELECT key FROM b) would do the same thing, only much faster than downloading 2,050,000 rows and comparing them. Each vendor handles internal set operations differently, but all of them optimize their internal data structures and abilities to do exactly these types of operations.
--That's the point of being root, you can do anything you want, even if it's stupid.
Don't mix up data integrity with business logic. You don't need a stored procedure to do all that; stored procedures make sure the billing address makes sense (like ensuring the zip code and city match).
Stored procedures are great when you have complex relationships between data than can't be defined by simple constraints. Any time you need to apply procedural logic to ensure data integrity, you need a stored procedure.
Taking the aggregate information and dealing with it is the job of the business logic, which is the middle tier. But the middle tier should be unable to insert data that is incorrect, no matter how poorly written the middle tier is.
Microsoft is to software what Budweiser is to beer.
Most of the time the DB isn't even accessible from the outside. Why worry about being rooted then ?
There's your answer... Most != All. The DB is connected to something, which is connected to something that is accessible from the outside. But attacks don't just happen from the outside, either. Probably 90% of the attacks you really need to be concerned about with most DB applications are an inside job. Can your company trust every user that has some access to the database?
--That's the point of being root, you can do anything you want, even if it's stupid.
First, I think that stored procs are not only often good but lead to better, more powerful, secure, and flexible applications that would be feasible without them. But on the other hand, they lead to hard to maintain applications which are explicitly tied to one database. So they are necessary but often misused.
Triggers are more important and usually use stored proceedures to ensure that the information in the database is always meaningful or that some other automated activity happens on an insert or update.
Unless I absolutely have to, I try to avoid having my application call stored proceedures directly. A relational database manager *should* be able to hide the stored procedures behind a view allowing a nice standard interface for your data. This means that if you have to move to another RDBMS later, porting is much more simple and mostly confined to the backend.
BTW, I agree with the points about having your business logic in one place. Stored procedures allow you to move business logic which is inherent to the database into the database, thus making it available from all clients regardless of the language they are written in. For a single app/db pair this is not an issue but if you have a large DB with many different clients, it is a larger issue. Maintaining your application in one location is a LOT less work than reimplimenting it all in every one of your apps.
Triggers, BTW, as I mentioned before are very powerful mechanisms. They are not called by the app directly but are run every time a row is inserted, updated, or deleted (some RDBMS's also allow select triggers, though some have alternate ways of implimenting this). They can be used to impliment additional security restrictions, enforce referential integrity, or more advanced stuff such as send email when a new order is added to the database. Again, this is done regardless of whether the order is filed using a web app or a Windows app for the rep in the call center. Since the logic is unknown to the app, it isn't even aware of what happens after the order is placed. Talk about clean interfaces..... This requires stored procedures.
So, these are the great things about stored procedures. But when they are used badly, you end up with the stored procedures reducing the agility of the application because they tie it in too closely to the database. What do you do when your app is tied to Sybase and your customers want MS SQL Server? What if *all* your logic is in that database? Do you rewrite *all* of it for MS SQL Server? Probably Not. You are stuck and out of your investment.
In my opinion, it is important to maintain a clear line between what is the database's job and what is the application's job. If this line is blurred, bad things can result. Stored procedures are a very easy way to blur this line.
I design my apps with the following layers to them:
UI
Application-specific logic
Database Access Layer
Information Presentation (SQL API using views)
Information Management and Data Logic (usually stored procedures)
Information Storage (highly normalized, usually).
This allows the database to be a server app in its own right, and the client logic to run in the apps themselves. HERMES (see my sig) is mostly built this way, but there are a few things I need to change before I am happy with the interfaces. This is one reason it no longer supports MySQL.
LedgerSMB: Open source Accounting/ERP