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=arch...@jab.org
