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 mathstored programs almost always process data from the database. In these circumstances, the difference between stored program and (for instance) Java performance is usually minimalunless 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 networkthe 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 CONTINUE HANDLER FOR NOT FOUND SET last_sale=1;

 OPEN sale_csr;
 sale_loop: LOOP
 FETCH sale_csr INTO SaleValue;
 IF last_sale=1 THEN LEAVE sale_loop; END IF;

 SET NValues=NValues+1;
 SET SumSales=SumSales+SaleValue;
 SET SumSquares=SumSquares+POWER(SaleValue,2);

 END LOOP sale_loop;
 CLOSE sale_csr;

 SET StdDev = SQRT((SumSquares - (POWER(SumSales,2) / NValues)) / NValues);
 SET Mean = SumSales / NValues;

 SELECT CONCAT('Mean=',Mean,' StdDev=',StdDev);

END

Example 22-4 shows the same logic implemented in a Java program.

Example 22-4. Java program to generate sales statistics

import java.sql.*;
import java.math.*;

public class SalesSummary {

 public static void main(String[] args)
 throws ClassNotFoundException, InstantiationException,
 IllegalAccessException {
 String Username=args[0];
 String Password=args[1];
 String Hostname=args[2];
 String Database=args[3];
 String Port=args[4];

 float SumSales,SumSquares,SaleValue,StdDev,Mean;
 int NValues=0;

 SumSales=SumSquares=0;

 try
 {
 Class.forName("com.mysql.jdbc.Driver").newInstance( );
 String ConnString=
 "jdbc:mysql://"+Hostname+":"+Port+
 "/"+Database+"?user="+Username+"&password="+Password;
 Connection MyConnect = DriverManager.getConnection(ConnString);

 String sql="select sale_value from SALES s" +
 " where sale_date >date_sub(curdate( ),interval 6 month)";

 Statement s1=MyConnect.createStatement( );
 ResultSet rs1=s1.executeQuery(sql);
 while (rs1.next( ))
 {
 SaleValue = rs1.getFloat(1);
 NValues = NValues + 1;
 SumSales = SumSales + SaleValue;
 SumSquares = SumSquares + SaleValue*SaleValue;
 }
 rs1.close( );

 Mean=SumSales/NValues;
 StdDev = (float) Math.sqrt(((SumSquares -
 ((SumSales*SumSales) / NValues)) / NValues));

 System.out.println("Mean="+Mean+" StdDev="+StdDev+" N="+NValues);

 }
 catch(SQLException Ex) {
 System.out.println(Ex.getErrorCode()+" "+Ex.getMessage( ));
 Ex.printStackTrace( );}

 }
}

As we saw earlier in this chapter, Java is much, much faster than the MySQL stored program language when it comes to performing calculations. Therefore, we expect that the Java program would be faster in this case as well. In fact, when we run the Java program on the same host as the relevant MySQL server, the Java program is fasterthough not by much: the Java program completed in about 22 seconds while the stored program took about 26 seconds (see Figure 22-2). Although Java is faster than the stored program when it comes to performing the arithmetic calculations needed, the bulk of the time is spent retrieving rows from the database, and so the difference is not very noticeable.

Figure 22-2. Java versus stored program performance across the network

However, when we invoke each program from a remote host across a network with relatively high latency, we see that while the stored program execution time stays the same, the Java program takes much longer to execute (increasing from 22 seconds to 5 minutes). The Java program has to fetch each row from the database across the network, and these network round-trips dominate the overall execution time. The lesson is clear: if your program causes a large amount of network traffic, such as those that fetch or change a large number of rows across the network, a stored program can outperform a program written in a client language such as Java or PHP.

Stored programs do not incur the network overhead of languages such as PHP or Java. If network overhead is an issue, then using a stored program can be an effective optimization.