List:General Discussion« Previous MessageNext Message »
From:Rhino Date:April 15 2010 8:50pm
Subject:Re: When to use Stored Procedures
View as plain text  

Shawn Green wrote:
> Tompkins Neil wrote:
>> Hi,
>> I've used mainly of the older versions of MySQL.  However am looking 
>> to port
>> a application across to MySQL 5.  My question is when would one 
>> decide to
>> use a Stored Procedure over a query written at the application level ?
> The decision to encapsulate a particular process or query within a 
> stored procedure is usually based on your business needs.
> * Common queries that only change by parameters are good candidates
> * Complex multi-step queries are good candidates
> * If you need an unprivileged user to make a change to a sensitive 
> table, you can avoid some security problems by wrapping that process 
> in a stored procedure.  For example, maybe part of your internal HR 
> processes is an application that allows people to update  their phone 
> numbers but can't be allowed to see the private information in an 
> employee's database record. You could create a privileged routine 
> called change_phone_number() that could do that without giving the 
> application (or another user) the rights to manipulate that table 
> directly.
> Does this help?
The other situation that strongly justifies a stored procedure in place 
of an application level query is one that involves a lot of churning 
through the database to return only a small amount of data.

For example, suppose you had to determine the median grade for a test. 
Let's say there were a million separate people taking this test (perhaps 
something like college admission tests). To find the median, it's 
probably best to sort the rows in ascending order by final grade, then 
count down from the top until you are halfway through the list. Then you 
can find and return the median grade. That involves a lot of I/O: a 
million rows to read, then the sorting, then reading down a half million 
rows to find the midpoint. But all you're returning is the median. If a 
client program has to do that work, all of those I/Os are going to be 
sent over the network and cost a lot. But if you write a stored 
procedure, it will do all the heavy lifting LOCALLY on the database 
server, which will reduce costs dramatically. Only the median gets sent 
across the network back to the client program. Obviously, this will save 
on both I/O costs and network utilization.

When to use Stored ProceduresTompkins Neil30 Mar
  • Re: When to use Stored ProceduresShawn Green15 Apr
    • Re: When to use Stored ProceduresRhino15 Apr
      • Re: When to use Stored ProceduresJohan De Meersman17 Apr
        • Re: When to use Stored ProceduresShawn Green18 Apr