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

I don't use em unless I have to

[ph4rmb0y]

I like to keep business logic in one place as much as possible. You are almost assured to have some in your app, so I try to keep all logic there.

Stored Procs and triggers make can make the code simpler and more efficient, but spread out the workings of the application and unless properly documented, more difficult to understand.

Just my $0.02 CDN

Re:I don't use em unless I have to

[nz_mincemeat]

Stored Procs and triggers make can make the code simpler and more efficient, but spread out the workings of the application and unless properly documented, more difficult to understand.

As a developer I've found otherwise. The reason being that when you're examining a bit of code with embedded SQL you often lose context of what table structures it is trying to refer to.

Of course my DBA is very good in helping out and training the developers in SP usage, so YMMV.

Re:I don't use em unless I have to

[severoon]

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.

I would argue that the three main points you made in favor of stored procedures are not points that apply to every case (I don't think they were intended to, either, but hear me out):

1. Centralized code. There are lots of ways to divide code up. I would argue that good n-tiered designs for web apps already use methods of organizing code into deployable/organizational units (layers, design patterns, component object models, etc) that render irrelevant the contribution that stored procedures are able to make in this regard.
2. Compiled SQL is faster. This is purely a performance enhancement. Performance enhancements like this belong at the back of the development cycle once you can measure the performance of the app; maybe it's perfectly acceptable. If it's not, then you look for bottlenecks and focus the energy where you get the biggest bang for the buck. If the biggest bottleneck is compilation of SQL, then stored procedures is your answer. Until the performance analysis is in, though, I think implementing performance enhancements maximizes investment of time and resources and minimizes return. Besides, much of the time a good, scalable design makes performance considerations irrelevant...you can have the cleanest code and just scale it up over hardware (within known constraints, of course) until performance meets requirements.
3. Enhanced security. Depending on stored procedures as a key element of security is obviously not desirable. Having said that, in your particular case, though suboptimal, you have to look at the big picture to see if the business justification is there to leave it in for this reason. Having said that, I would try to design the app, security-wise, as though stored procedures don't exist to whatever extend possible. Just as with the business rules, you should be free to change DB vendors from a security standpoint as well.

I would argue that business rules and business logic should be implemented in a vendor-independent way. Also, I would implement the business functionality of the app so that it can support a web front end, but also someday a desktop UI, a programmatic web services front end, etc. That's the soul of n-tiered architectures, they're supposed to bring that kind of flexibility along with the use of tiers...this kind of flexibility is the point.

If an app is not flexible in this way but claims to be an "n-tiered architecture", I'd argue that it is only nominally so. Looking like an n-tiered app without providing any of the benefits is a Pyrrhic victory for the architects and designers. That would be inconsequential except for the (usually large) investment of company resources.

Re:I don't use em unless I have to

[Atrax]

> Compiled SQL is faster.

Actually, this depends on the database in question these days. SQL Server 2k does a pretty good job of keeping embedded queries hot, so the performance gain is waay less impressive than it was in, say, SQL 7.0

[cue MSSQL Bashing in 3...2....1.....]

Haven't really kept up with competing RDBMSes recently, but it wouldn't surprise me if competitors were also narrowing the gap

> Enhanced security

One incredibly common security hole being SQL Injection, I have to agree with this, but with the following caveat :

I've seen developers create stored procs which do a bunch of string concatenation within the SP, then EXEC the resulting string. This is just as injection prone as doing it in a script in the first place, but the developers in question often cite SQL injection as their one of their reasons for using SPs in the first place.

Again, back to the problem with lack of knowledge on the developer's part causing security holes, rather than the platform.

Re:I don't use em unless I have to

[1001011010110101]

I'm an Oracle DB guy (trying to dive into J2EE, better late than ever), and while I'd agree with your general point of view, I think you are missing something:

1. Centralized code. There are lots of ways to divide code up.

Sometimes is nice to assure that no matter what tools access your data model, only valid/complete information gets into it (usually this is the case with schemas that have existed in a company for some time). Sometimes there are different front ends to the same data, incoming interfaces, etc. They tend to grow on databases as time passes by :). It also helps you reusing business logic in different technologies/applications.

2. Compiled SQL is faster. This is purely a performance enhancement. Performance enhancements [...]

Usually coding thru the stored procedures/triggers is a good way to have access to all the features in the database and programming languages, performance and productivity wise...some things are not available thru a interface to external engines and you need to be pretty close to the engine to get them. These things can make a huge difference.

3. Enhanced security. Depending on stored procedures as a key element of security is obviously not desirable.

Stored procedures are a very good idea if they are the only way to access the related data schema. Just be assured to deny direct write and/or read access to the tables.

[...]Should be free to change DB vendors from a security standpoint as well.

One thing that I've seen around and found quite appropriate: When deciding whether to choose exploiting or not DB specific features, the DB cost is a factor. Why buy a brand name DB and not exploit the features it gives you? The cash you spend on it should be used for something, either performance/resource-wise, or easier development. Otherwise just use an el-cheapo engine like postgress or mysql.

Re:I don't use em unless I have to

[Hangtime]

Enhanced security. Depending on stored procedures as a key element of security is obviously not desirable. Having said that, in your particular case, though suboptimal, you have to look at the big picture to see if the business justification is there to leave it in for this reason. Having said that, I would try to design the app, security-wise, as though stored procedures don't exist to whatever extend possible. Just as with the business rules, you should be free to change DB vendors from a security standpoint as well.

SQL injection is a very large problem in the enterprise. Stored procedures facilitate better safety. Yes, you can test for all the characters in your code but as soon as your app and password are compromised their is open reign on your database if you gave it data reader, data writer, dbo or heaven forbid SA. All new applications are build on stored procedures not only for data modfication but also SELECT queries. The application itself has no rights to the underlying tables. This ensures if indeed the application was ever compromised the most anyone could do is what the application does today. Also, they would have to figure out the XML strings to manipulate the data with a stored procedure.

Two answers.

[MisterFancypants]

You're going to get two answers. You'll get the "always used stored procedures" answer from people who actually do real database work and the "never use stored procedures" from the people who hack small websites in PHP.

If I were a bit more of a tinfoil hat wearing man, I'd be Slashdot makes some of these "Ask Slashdot" topics up because the ensuing flamewar will cause more page hits than usual.

Re:Two answers.

[Anonymous Coward]

That's funny, I was going to make the comment but instead of "people who actually do real database work" I was going to say "people who think it's still 1980 and who think changing software should take an act of congress" and instead of "people who hack small websites in PHP" I was going to say "people who use dynamic languages to deliver high-volume applications in weeks instead of months".

I guess it's a matter of perspective.

Good.

[Pig Hogger]

They're double plus good, of course.

The idea of a database is to put the whole data-relation logic in the database, if only to insure atomicity of operations.

Because as soon as you rely on an external process to maintain data integrity, you're bound to fall prey to some sloppy programmer who does not understand the data relationships and will not properly maintain the data integrity.

At least, when you use stored procedures, you can concentrate the data integrity logic in only one place, which is easier to control and manage.

Stored Procedures vs adhoc queries in apps

[jfroebe]

If the applications are written in one of the various scripting languages, then this argument doesn't apply:

One major problem with enterprise applications is that when a problem is found in an adhoc query (poorly written, a bug with the DBMS, performance related, etc) then the application would normally have to be recompiled and pushed out to the entire enterprise (could be tens of thousands of computers to push to). This isn't desirable.

Moving the queries into stored procedures (where possible) allows you to correct the stored procedure at a central location and roll it back to the 'old' stored procedure if necessary with minimal effort.

A good rule of thumb: use stored procedures for compiled applications

Jason L. Froebe

Stored Procedures often more harmful than helpful

[1010011010]

Implementing your application logic as stored procedures has some detrimental side effects.

1. SPs turn your database into an application server, centralizing things that needn't be, and raising load on that central machine.
2. SPs invite use of vendor-specific features, and therefore lock-in and loss of portability.
3. SPs are not typically amenable version control and are maintained outside the rest of your code base.
4. SPs represent "premature optimization." There may be a time and a place for SPs, but they are used a lot more than needed in many applications. For example, one application at my company has over 1,000 SPs, and quite a number are just wrappers for simple select statements.

Prepared statements and vendor-neutral SQL are the way to go for portability and controllability of the development process. Use SPs judiciously, if at all, and only when there's a highly compelling need to do so(e.g., order of magnitude speedup, etc).

Re:What's the point of this question?

[arkanes]

An RDMBS (as opposed to just a database) is actually for manipulating data, not just storing it. Otherwise you'd just use flatfile for everything and implement all the relational logic in your app code. The database can execute stored procs far, far faster than your app code can perform the same functions. Using database side stored procs gives you the exact same advantages as a class library with additional performance and security options. There's no loss. Why not use them?

Stored procedures == Database API (a good thing)

[FlyerFanNC]

I'm an Oracle DBA, and I like creating packages of stored procedures and functions (and especially table functions) that represent an API of sorts to the database. This means that the application code doesn't care how the data is stored, and the DBA is free to rearrange the data for tuning purposes, without requiring any app changes (assuming the API remains constant).

In the past I've supported keeping more of the business logic in the database, but I no longer believe this is an optimal design. Now I keep business logic out of the database as much as possible and limit the stored code to enforcing data integrity and making the database look like a "black box" to the apps.

portability

[MORTAR_COMBAT!]

I've found that, say, writing an app with a lot of code in Oracle PL/SQL, using cursors, etc, means your app will only and forever support Oracle, without a whole lot of re-write and likely re-design.

So unless you like vendor tie-in... stay away from db-specific stored procedures.

My take

[ZeroConcept]

Stored procedures are a performance optimization, consider the following scenario:

Retrieve the 20th page using a page size of 50 records for all the SKUs under a catalog (potentially millions total) for a specific user which could or not have visibility permissions for each SKU. Assume the security provided by the database is too coarse to fulfill the business requirements, therefore some set of rules must be evaluated to determine SKU visibility for a particular user.

That query would normally be very fast if implemented as a stored procedure because:
1) Only one round-trip is needed.
2) You don't have to move all the data to a middle-tier and then filter out information.
3) RDBMSes are usually faster at filtering data out (by using indexes, denormalization, etc.) that what a developer could code in a middle-tier to filter out information.
4) Most RDBMSes are very good at scheduling tasks, caching, managing memory, etc. The more you move logic away from it, the more you would have to implement it yourself.

You could send all the SQL statements to the database and achieve the same effect, but it might make debugging harder and you still have all the SQL logic in some place, only a different one.

On the flip side:
1) It's harder to write stored procedures than it is to write code in a managed language like Java or C# (thirty-line SELECT statements are not very intuitive).
2) Generally speaking, the compiler of a managed language does a better job at catching errors than a compiler for stored procedures, where a lot of the errors will be caught at runtime.
3) Stored procedures are not portable.

My advice is, if you are only using the RDBMS as a persistence device and your data size is not huge, avoid stored procedures and create some sort of middle-tier object model. Only when performance is a impediment, use stored procedures. You might as well use a hybrid approach, try to model as much as you can in the middle-tier and implement stored procedures for those tasks which are performance intensive.

I work with people that worship UML and patterns as well with RDBMS Gods that can plow through pages and pages of stored procedures without blinking. As much as I love ULM and patterns there are some tasks that must be done in the RDBMS for performance reasons, and tasks that are simply more maintainable when done in the middle-tier. Both approaches have advantages and disadvantages, the trick is to use the best approach according to the situation.

Re:Good or bad?

[johnstoj]

Don't be assinine. A modern production environment is running at least 100MBit Ethernet, if not 1GBit. A HUGE query, say 2500 characters, is 2.5KBytes. Gee, thats pretty rough.

It's not the query he is talking about, it's the transference of the data set from the database to the application server. This is where the data will be processed if the logic is in the app server.

Good for what they're for; crap otherwise

[sparks]

Generally a database should be where the data is kept. Nothing else. If there is some functionality which is absolutely 100% to do with how the data is stored, then it *might* make sense to use a stored procedure for it. Better that than filling your actual business logic with the minutae of a particular DBM.

On the other hand, you should never, ever put actual application logic in a stored procedure. The reasons are several. The most important is that stored procedure languages are all, to a greater or lesser extent, crap. This comment will cause me to be flamed to death by those who only know PL/SQL etc, but the fact is it's true. They are not general-purpose programming languages.

Sure, you might not RIGHT NOW want to fork off sendmail from your application, but some day you might. Or, horror of horrors, maybe you'd like to write directly to a system file? Or use a neat SNMP library you found? Although there are twisted, hacker-like ways to do these things in most DBMs they are hardly the model of reliability or professionalism. [1]

Secondly, they tie you in at a fundamental level to a particular database vendor. Database software is generally neither Free nor free. They want you to put your business logic in their stored procedure language because it will only run in their database products. Lock in is bad. OK, you'll be locked in whatever you do, but I'd rather be locked into Java or Python than PL/SQL.

Thirdly, you are losing control of your application's performance. You have very little control over how the code will be optimised or run.

Fourthly, you are breaking abstraction. It is very, very hard to write stored procedures which aren't entirely dominated by the structure of the underlying database.

Finally, assuming you probably will have to have a middle layer between the client and the database anyway, it's a bad idea from a maintainability point of view to bits of the same functionality among your layers.

[1] have you ever written a cron job to run a query to dump a table to a file to be parsed by a Perl script to send an email? You might be an Oracle Portal user.

Re:An example of good

[imroy]

I've always wondered about the real speed difference between MySQL and PostgreSQL. You said that you were able to take out a lot of code because PostgreSQL was to do more fo the checks for you, and the stored procedures handled the remaining high-level details. Back in the day, the MySQL developers publically trashed the idea of transactions, instead recommending people emulate similar functionality in their client-side code. And that they did.

Now, consider two similar DB-based applications. One is connecting to MySQL with all sorts of client-side code to emulate transactions and referential integrity (foreign keys, etc). The other is connecting to PostgreSQL where most of the work is done in the DB engine. Foreign keys are used, as are transactions and stored procedures. Thus the number of app->db requests will be far fewer than the client-heavy MySQL app. Now, even though the MySQL server may beat the PostgreSQL server in raw speed from simple selects, how would that change when you factor in the extra work that the MySQL app has to do? And what about more complex queries? I've found that PostgreSQL can handle complex queries (sub-selects, unions, aggregrates, etc) very well. It's much more efficient to do a single big multi-row query than lots of single-row queries. It probably takes longer for the programmer to write the query, but once that's done the optimizer gobbles it up and produces a plan that gives you all your data at once. Very nice.

I'd bet that all or most of the MySQL/PostgreSQL benchmarks don't take into account the very different ways these two DB's are used in real apps. MySQL is traditionally used as a simple data store with an SQL interface. Whereas PostgreSQL can become a more intergral part of the application, with stored procedures, triggers, foreign keys, etc.

Stored procedures not complied, but separation def

[Grimace1975]

Procedural code and SQL code are two separate programming language processes. The first directs the computer from a singular point of view. For instance "do this, then that, then go here and check this. etc.". And the other deals with groups of items. such as "everyone wearing blue shirts go to room 103", or "we don't need these anymore".

As far as intermixing these code bases, your procedural business logic and data business logic should be split when it makes sense. The database is optimized for merging and managing sets of data, and procedural code is good for binding this to a functional form. The business logic should be split into these two zones and implemented appropriately. It would be inappropriate to return a set from a database then loop through that set searching for some name or value. And at the same time it would be unwise to return two sets and join them in your code. With experience it seams cleaner to maintain these two zones of code. This doesn't mean that you need to use stored procedures though.

As far as stored procedures, they are a convenient way of separating these two types of languages, another way is to in place the Sql code into your procedural code, but it seams advisable to centralize this type of code in one place for visibility, and manageability. If stored procedures are not available or undesirable, then using classes or function that are located in some central, or locatable place, is recommended.

As far as for speed, implementing the data and logic in the appropriate place will speed your application, but stored procedures will not in there own right speed anything up. At least in MsSql server, stored procedures are not precompiled. They exist as plain text, just like issued queries. They do however get their own query cache, separate for the issued query cache, which could be of a little assistance.

Anyways. I am over talking about this. Take it, as u wants it.
-- Grimace1975