MySQL Stored Procedure Programming

Reducing Network Traffic with Stored Programs

If the previous section left you feeling less than enthusiastic about stored program performance, this section should cheer you right up. Although stored programs aren’t particularly zippy when it comes to number crunching, you don’t normally write stored programs that simply perform math—stored programs almost always process data from the database. In these circumstances, the difference between stored program and (for instance) Java performance is usually minimal—unless network overhead is a big factor. When a program is required to process large numbers of rows from the database, a stored program can substantially outperform programs written in client languages, because it does not have to wait for rows to be transferred across the network—the stored program runs inside the database.

Consider the stored program shown in Example 22-3; this stored program retrieves all sales rows for the past five months and generates some statistical measurements (mean and standard deviation) against those rows.

Example 22-3. Stored program to generate statistics

CREATE PROCEDURE sales_summary( ) READS SQL DATA BEGIN DECLARE SumSales FLOAT DEFAULT 0; DECLARE SumSquares FLOAT DEFAULT 0; DECLARE NValues INT DEFAULT 0; DECLARE SaleValue FLOAT DEFAULT 0; DECLARE Mean FLOAT; DECLARE StdDev FLOAT; DECLARE last_sale INT DEFAULT 0; DECLARE sale_csr CURSOR FOR SELECT sale_value FROM SALES s WHERE sale_date >date_sub(curdate( ),INTERVAL 6 MONTH); DECLARE ...