Skip to navigation

PCPro-Computing in the Real World Printed from www.pcpro.co.uk

Register to receive our regular email newsletter at http://www.pcpro.co.uk/registration.

The newsletter contains links to our latest PC news, product reviews, features and how-to guides, plus special offers and competitions.

// Home / Blogs

Posted on May 18th, 2008 by Kevin Partner

When to use Stored Procedures

Version 5 of MySQL added Stored Procedures and, as is often the case when you’ve been using earlier versions, I didn’t notice at all, until a client asked whether the site I’m building for him should be using this technique for interacting with a database. In this case, the answer is a resounding no since it would add an unnecessary level of complexity to a site in which the MySQL queries rarely involve more than one table at a time.

So, when should they be used?

A Stored Procedure is, as its name suggests, a series of SQL statements saved for use later, sort of like an old fashioned .bat Batch file on steroids. They’re particularly useful where you have a complex sequence of actions that never changes: you can get it right once and then run it with much lower overheads in future.

I’ve also heard claims that Stored Procedures improve security against SQL Injection but it’s hard to see why where dynamic data is concerned and, after all, PHP includes mysql_real_escape_string which protects against this form of attack.

So, it seems to me that Stored Procedures would be useful for busy sites with repetitive actions (for example interfacing a shopping cart with a stock management system) but do they have uses for more typical web applications? Or would you suggest that they should be used for every SQL query?

I really don’t know where the line should be drawn: at the extremes it’s pretty clear but the line is extremely fuzzy. Any thoughts?

Tags: , , , ,

Posted in: Real World Computing

Permalink | Trackback

Follow any responses to this entry through the RSS 2.0 feed.

5 Responses to “ When to use Stored Procedures ”

  1. David Wright Says:
    May 19th, 2008 at 7:59 am

    Stored procedures can be useful. It also makes a central point for accessing tables or queries. This means that, even if you have a db class PHP handler routine, you are not repeating SQL code throughout your application – and giving yourself more chances to forget to escape those strings.

    Testing is also easier. You can test the SQL queries in isolation, knowing that they work 100% correctly. If you have code which is PHP calling a dynamic query, it can take more time trying to work out why the PHP code isn’t working, when the culprit is the query.

    Likewise, changes to the database require hunting through the code on the front-end(s) to find all instances where a table or field is used and altering them. If they call through stored procedure, you only need to change the query once and everything should still work – unless it is a radical change, the input parameters to the stored procedure shouldn’t change and unless a field is removed from the output, no remedial action is immediately required on the front end code.

    It also means that the front-end developers don’t need to know SQL, and the database developer doesn’t need to know PHP/ASP/Ruby/whatever. On a small team, or one-man-band, this isn’t so important, but on a large project, it means that each developer can concentrate on their own area of expertise.

    The anaylst defines which information needs to pass between the two layers, and as long as both development teams stick to the definitions, they never need to interfere with each other. The “black box” approach can remove a lot of headaches.

     
  2. Kevin Partner Says:
    May 19th, 2008 at 8:14 am

    I see what you’re saying David: clearly the more sophisticated the application (and therefore the greater the number of database queries, the more developers and the greater their specialisation) the more this approach is of benefit.

    For smaller scale projects, many of these benefits can be achieved using good object design, of course, and I’m as keen as you are on the black box approach because it means that once a class is working properly, it can then be left alone.

    I’m certainly going to take a look at MySQL’s implementation. It’s simply been a non-starter until now but the time has come to see how version 5 goes about it.

     
  3. David Wright Says:
    May 19th, 2008 at 8:57 am

    http://www.linuxjournal.com/article/9652

    Linux Journal had an interesting feature on it.

    The only point I didn’t totally agree on was the “Logic Fragmentation”, where they said tracking errors was harder. I’d say it makes it easier, in that the SP can be written, then a test harness written for the SP, tested to death, then, when it is finished, the resultant middle-tier class/routine can pick it up and run with it, in the knowledge that it is fully tested and working.

    They are correct, in that you can’t debug SQL using a debugger in the middle tier, but they drew the wrong conclusion, in my opinion. If the SQL has been compartmentalised and thoroughly tested, it means that the error is either in the class, or in the parameters it is trying to pass over – and if the stored procedure is checking the parameters and returning the appropriate error codes when they are incorrect, it should make tracking down the problem easier…

    I agree, classes and encapsulation of business logic, and a central point for queries makes the need for SPs less of a requirement – although load-balancing across a server farm could be a reason to use them, if you have a dozen servers handling requests and shunting traffic back and forth, if they need to filter through a result set, performing logic on it, that pure SQL can’t, then the network traffic between the load balancers and the database server(s) can be significant and breaking that logic out to a SP could significantly reduce load on the LBs and the internal network.

    I think it is very much a case of looking at the size of the project, the number of predicted users, network traffic issues and number of client applications. Only once you have all of that information an you make a proper decision on whether SPs will be a benefit or not.

    They aren’t a universal panacea, but they shouldn’t be dismissed out-of-hand.

     
  4. Thought Dan Says:
    May 19th, 2008 at 9:15 am

    In my Thought Dan head I think I’ll be using them more for actions in CMS Admin sections, where I have to do batch like queries in user functions.

    I suppose it’s balanced on how long does it take to write and what speed enhancement will it allow you.

    Thought Dan : http://www.thoughtden.co.uk

     
  5. Logic Express 7 Mac Says:
    July 21st, 2008 at 10:28 am

    Logic Express 7 Mac…

    An interesting post by a bloger made me……

     

Leave a Reply

Spam Protection by WP-SpamFree

* required fields

* Will not be published

Authors

Categories

Archives

advertisement

SEARCH
SIGN UP

Your email:

Your password:

remember me

advertisement


Hitwise Top 10 Website 2010