--00151747649202b8cf0483014825
Content-Type: text/plain; charset=ISO-8859-1
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 ?
Cheers
Neil
--00151747649202b8cf0483014825--
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?
--
Shawn Green
MySQL Principle Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org
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=gcdmg-mysql-2@m.gmane.org
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
>
>
> 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=vegivamp@tuxera.be
>
>
--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org
Hi Johan,
Johan De Meersman wrote:
> 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
>>
>> ...snip...
>> For example, suppose you had to determine the median grade for a test.
>> ...
>
>
That would require running the query twice as LIMIT only accepts numeric
literals. For a large dataset, that would destroy the efficiency of the
stored procedure. It's more efficient with MySQL to capture the value in
a temporary table, count those temporary results, then create a LIMIT
query using the prepared statement syntax (dynamic SQL) against the data
in the temp table.
http://dev.mysql.com/doc/refman/5.1/en/select.html
####
The LIMIT clause can be used to constrain the number of rows returned by
the SELECT statement. LIMIT takes one or two numeric arguments, which
must both be nonnegative integer constants (except when using prepared
statements).
####
The above process could very easily be encapsulated by a stored
PROCEDURE (but not by a stored FUNCTION) so that you would not need to
implement it in your client code. Unfortunately the stored functions are
not allowed to use prepared statements, yet.
http://dev.mysql.com/doc/refman/5.1/en/stored-program-restri ctions.html
####
SQL prepared statements (PREPARE, EXECUTE, DEALLOCATE PREPARE) can be
used in stored procedures, but not stored functions or triggers.
####
Hope that helps!
--
Shawn Green
MySQL Principle Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org