So, are Database Stored Procedures Good or Bad?

by Roland Bouman

A little less than a week ago, I opened a zoomerang survey about database stored procedures. In this post, I'm presenting the results.

In just a few days, the survey was visited 232 times, and no less than 155 people completed the survey! (There was a very small (2) number of people that answered some, but not all the items, and these are excluded from the results proper.)

I didn't really know what to expect, but I did not expect as much as this! So, I'm quite pleased, and I want to thank everybody that took the time to complete the survey. Thank You very, very much, I appreciate your efforts a lot!

8 Comments

Brianary
2007-03-13 08:36:03
Slower? Making the database parse a SQL string *in addition* to executing it cannot ever take less time. And dragging huge datasets through the wire so the app can filter/join/transform doesn't seem like it could ever improve performance, either. Maybe the fact that most of the respondents use MySQL belays a shortcoming in the maturity of sproc support in that particular engine, more than indicting the performance of sproc performance as a whole.


Less portable? Does MySQL not support the SQL-99 spec, either? Or is this a reference to the fact that MySQL only recently supported sprocs at all? Is there a reason why sprocs cannot use standard SQL-99 SELECT, INSERT, UPDATE, and DELETE statements?


I'm pretty disappointed that the survey asked nothing about security, which is the best reason to use them in the first place. If you don't want to open up access to the entire schema to an application (putting you at serious risk if someone uses the same credentials in an ad-hoc environment), sprocs allow you to gate access to just the *records* appropriate to that user, or enforce logging, or implement business rule conformance. Sprocs (when used with command objects) also give you immunity to SQL injection attacks.


Also, if the application has to perform all of your business logic, then you have to re-implement that code if you have more than a single app using that database. Each app would include its own copy of the business rules, and that is certainly not relational. With sprocs-only access to the database, you always know that all current and future apps will behave consistently. This also allows you to implement schema changes from one version to the next without changing the sprocs (usually), requiring *no code changes*. This is why this approach is a best practice in OOP.


These are the reasons to use sprocs whenever possible: security, encapsulation/reusability, and performance.


I look forward to hearing about the "other" cases when sprocs are "Bad".

Roland Bouman
2007-03-13 09:38:52
Hi Brianary,


Please realize that the survey itself is not in anyway focused on MySQL - I merely pointed out that I expect many of the respondents to be MySQL users.


"Slower? Making the database parse a SQL string *in addition* to executing it cannot ever take less time. And dragging huge datasets through the wire so the app can filter/join/transform doesn't seem like it could ever improve performance, either. Maybe the fact that most of the respondents use MySQL belays a shortcoming in the maturity of sproc support in that particular engine, more than indicting the performance of sproc performance as a whole."


Maybe you should re-read that section. I'm never saying anything is slower; Not so far at least ;) I'm merely reporting that the majority of the respondents are convinced stored procedures are *faster*.


"Less portable? Does MySQL not support the SQL-99 spec, either? Or is this a reference to the fact that MySQL only recently supported sprocs at all? Is there a reason why sprocs cannot use standard SQL-99 SELECT, INSERT, UPDATE, and DELETE statements?"


MySQL supports a subset of the SQL-2003. I don't really understand the comment about the use of INSERT, UPDATE etc in stored procedures. At least, MySQL supports all these constructs.


"I'm pretty disappointed that the survey asked nothing about security, which is the best reason to use them in the first place."


This is *your opinion* ;) It is indeed a pity I did not include an item for security, we could've tested to what extent your opinion is shared by others ;)


"If you don't want to open up access to the entire schema to an application (putting you at serious risk if someone uses the same credentials in an ad-hoc environment), sprocs allow you to gate access to just the *records* appropriate to that user, or enforce logging, or implement business rule conformance."


I would probably prefer views to restrict access to particular rows. However, it's a mystery to me how you want to safely do this if you cannot distinguish the users from their account data (user name, possibly host from which they are connecting).


"Sprocs (when used with command objects) also give you immunity to SQL injection attacks."


Well, so do prepared statements. So, that's not really a defining property of stored procedures as far as I can see :)


"Also, if the application has to perform all of your business logic, then you have to re-implement that code if you have more than a single app using that database. Each app would include its own copy of the business rules, and that is certainly not relational."


Well, exactly for this reason, I highlighted a few individual responses. The item about "Portability" is intended to be imprecise, and it leaves the respondent to decide: "portability of what, database or application".


Clearly, your focus seems to be on porting application code more than on porting to another database platform. If you are in the business of selling a particular application (SAP comes to mind) you are probably better off building as much of the logic as you can in the application, or inside some middleware between the application and the database.


I don't really see what putting business logic in the application has to do with not being relational though...Or do you mean with "business logic" plain database constraints? I mean, usually, there's a long way between business logic and database constraints ;)


"This also allows you to implement schema changes from one version to the next without changing the sprocs (usually), requiring *no code changes*."


Mmm, I'm having some trouble understanding exactly how the stored procedures can benefit from the changed schema structure if you won't touch the code. I mean, I can see a scenario where the stored procedures implement (steps of) the business processes and the application does not have to change, or is at least somewhat shielded. However, usually adding functionality goes hand in had with changing table structures to convey more or other data. The application still needs a way to communicate with the database in order to benefit from that. So, your application (and procedures) need to be changed anyway.


"These are the reasons to use sprocs whenever possible: security, encapsulation/reusability, and performance."


Well, at least, it is *your opinion* that this are the reasons ;)


"I look forward to hearing about the "other" cases when sprocs are "Bad"."


I never said they were Bad. Maybe you should reread the introductory section. I very clearly stated there that I think they are Good in some cases and Bad in others and that I think it is usually pretty easy to figure out which one. I will be doing a write up about that Real Soon Now. As ever, I'll be glad to receive your comments on that too :)

Paul
2007-04-02 20:50:44
Hi Roland, the survey is great despite the statistical limitations you mention.
Your results seem to demolish the popular stereotypes that "mid-tier" developers (using PHP, Perl, Java etc) are averse to leveraging programmatic features of the database tier.


Regarding "portability", I think the answer to the question "portability of what, database or application?" is usually: neither! In all the cases I've seen of teams striving for portability, it seems the only actual value captured is in terms of "developer skills portability". Not an insignificant concern, but one often obscured by proponents of high-faluting architectural aesthetics. Database portability was a real concern 5-10 years ago when vendor stability was by no means assured and standards were still evolving, nowdays I think the pragmatic architect realises that a well-factored data access layer means proprietary database features can be exploited not avoided. But it is the hidden desire to maximise (perhaps my own) "developer skills portability" that keeps the database independence imperitive alive.

BodiZaffa
2007-04-04 11:00:54
I enjoyed the survey. I was suprised at the majority who still love stored procedures. Since 2005, every org I've been at as an application arch, we've moved every stored procedure off of the databases, letting DBs do what they do best. Stored Procs coded into enterprise objects that can be called by clustered portals with HA, and all fiber intrastructure. The business case has to exist in order to move from stored procs to alternatives.
Lal
2007-05-08 11:35:52
survey interesting.


sp's have their place. I've seen bad ones, no ones and good ones. All depends on the 'human' involved.


they work well within a good architected system. they work very poorly when created by non-dba's (i.e. those that don't know a 'sarg' from a clustered index)


Lal

Alireza
2007-12-30 01:29:50
i had a project some months age, it worked with lots of tables, and i had added lot's of queries inside my code, when we started the beta test with a mass of data, the performance decreased a lot(e.g. 10sec wait for a form to load!!!) it was disappointing. after hanging around the web for some days i decided to move all queries to seperate sql stored procedures. after it, the result was wonderful, we speed up the project up to 10 times, that's why i always prefer SPs
Roland Bouman
2007-12-30 02:54:16
Hi Alireza!


Interesting result! What database and programming language did you use in your project?

Fabricio
2008-03-01 11:57:23
@Roland:
This normally means that some of the her queries are:
1) full table scans (select * from table) without a (or with a poor or badly index covered) where clause used for processing large resultsets (probably reports)
2) very large and complex queries, which sometimes need to complemented by minor queries
3) complex updates


These cases can be really covered with advantages by stored procedures, because the procedural approach of sproc can simplify the code and perform better.