Stored Procedures vs. SQL Calls

This has gotten bandied around a lot in a development group I’m in, so here’s my take. There’s lots of arguments for and against stored procedures. The linked article is quite old, but the article and the comments quite well cover the issue.

Basically, the arguments against amount to: There’s no real performance boost, it moves your code out of the code layer and into the database layer and/or otherwise creates an additional tier, and it doesn’t add any security.

I use stored procedures sparingly in places where I either need to assemble a large chunk of data using multiple queries, but just want to provide a function call to the program to get the result set. (AKA: A view). I also use them when I want to hide information in the database from other developers (ex: Social security numbers or credit card info in transaction logging tables is hidden by not giving anyone select permission on the table, but giving people access to a view.

I use stored procedures in places where I’d have to write a lot of two-way communication. For instance, when creating a user in a game I’m working on, you insert the user, get the ID back, update the invite code record to show it’s been used and is now associated with an actual user, create the user’s HQ, create a warehouse and hangar in that HQ, create ships and put them in the hangar, create equipment and put it in the warehouse… That’s not just a lot of code to have down in the application level, but it’s a lot of back and forth communication as a query gets sent and then the response comes back and then more queries get generated and sent. It’s not something you can batch, even — you have to wait for the last_insert_id() to come back and get the result out of the dataset. I’ve reduced about 300 lines of application code down to a 50 line stored procedure. Nice, huh?

I also use stored procedures to abstract complex calculations — like fetching information about n-dimensional space around an object. In one case, I need to know what’s on either side, in three dimensions, out to a range of two ‘zones’ around a particular object. The code to do that is big and hairy and messy, even wrapped up as a library function. It makes sense to put it in a stored procedure because the queries are all dependent, and the application doesn’t care about anything except the resultset.

When you’re using compiled languages in a client-server program, stored procedures can save you a lot of time. Imagine a beta period where you’re frequently releasing bugfix releases. Imagine if all you had to do instead was to update a stored procedure in a database. Much nicer, ‘eh?

Definitely don’t use sprocs as your garden-variety crud. That’s not worth it… there’s better libraries for that, and using stored procedures with them just gets in the way. Look at the words I’ve put in bold above. Those are the real reasons to use stored procedures. Adding another layer to your application can be a two-edged sword. Over-use it, and you’ll end up slicing yourself to ribbons. But it is there to be used, and it is a viable choice!


Related:

8 thoughts on “Stored Procedures vs. SQL Calls

  1. Anon says:

    Worst blog ever. Putting business/application logic on the data layer what a faux pas. Typical mistake of the php script kiddies.

  2. Anon – If that’s the case, then why do Microsoft, Oracle, etc. et. al. recommend stored procedures as standard operating procedure? Why is having stored procedures an essential part of the specification of an ‘enterprise’ RDBMS? Why did people complain when MySQL didn’t support stored procedures?

    Considering that MySQL is the database used most frequently with PHP, I would think that the ‘PHP script kiddies’ are a little late to the stored procedure party.

  3. Lesiu says:

    I agree with Anon. Another issue – What about the most important things – security? Stored procedurals are good in secure your code.

  4. Frank Silbermann says:

    Couldn’t you just move the stored procedure logic into EJBs running on the same server? Isn’t that what EJBs are for?

  5. David Lee says:

    I agree, Good article. Whenever I’ve had this discussion with other java developers it turns out that often many don’t have much experience with SQL, less w/SPs and only really know ORM tools.

    There is definitely a time and place for stored procedures and you’ve articulated most of them very well.

  6. Bart Summar says:

    Stored procedures offer speed and security advantages as long as you don’t do dynamic sql inside of the procedures. Also, business logic should be in the business layer, not in the database. I just worked on a stored procedure that was over 3000 lines and it was a mess to debug. If that business logic had been placed in a C# class, it would have been much easier to find and fix the bug. I find that most mistakes like this are done by persons who have not had formal training.

Comments are closed.