List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:April 17 2010 9:06am
Subject:Re: When to use Stored Procedures
View as plain text  
as a totally off-topc question, wouldn't something along the lines of
LIMIT COUNT(*)/2, 1 do that trick?

On 4/15/10, Rhino <rhino1@stripped> wrote:
>
>
> 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.
>
> --
> Rhino
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel
Thread
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