Stored Procedures

As part of your previous optimization work, described in Chapter 19, you suggested that High-Hat move toward centralizing its business logic in server-based, stored procedures. You felt that this would produce higher-quality applications, especially given the frenetic turnover and related lack of development discipline in the airline's IT department.

High-Hat took your advice and began converting significant portions of client-side application code into server-side, stored procedures. Almost immediately, the inconsistencies that had plagued their applications and data became much rarer. However, first developers and then users began noticing performance degradation for several of the applications that employ these new stored procedures, as well as some other applications that do not.

Diagnosis

Deciphering an apparent stored procedure performance problem is often far simpler than tackling a server configuration problem. All that is necessary is to look at the stored procedure code, along with any SQL invoked from the procedure; the EXPLAIN command helps with that task as well. Stored procedures are discussed in Chapter 9, "Developing High-Speed Applications."

Observations

Many of the stored procedures that you evaluate appear normal. However, several other procedures seem to follow the same design pattern. They typically

1.	Set transaction isolation level to SERIALIZABLE.
2.	Start a transaction.
3.	Conduct a range search on a single table (table A) using one or more of the stored procedure's parameters.
4.	For each fetched row from table A, declare cursors to look up information in three other tables (tables B, C, and D).
5.	When all the relevant rows from all tables (tables B, C, and D) have been retrieved, update an internal variable.
6.	After all the rows from table A have been fetched, divide this internal variable by the number of fetched rows to yield a result. Insert a single row into a statistical table, using the value from the preceding step. Commit the transaction.

Solution

Several performance and concurrency problems exist with this stored procedure design pattern. You present your recommendations in the following two categories.

Transactions

The preceding example, as well as the other procedures, appears to be not particularly sensitive to minor data alterations. Despite this indifference, the transaction isolation level is set to a superstrict value of SERIALIZABLE. Recall from Chapter 9 that this isolation level effectively blocks other processes from altering any rows that have been evaluated during the course of this transaction, regardless of whether these rows have even been changed. Given the large number of rows spread across multiple tables, there is a good chance that this procedure is negatively impacting users throughout the system. In addition, this procedure's demanding isolation level means that it will likely run into obstacles and be forced to wait for other operations to finish.

For this kind of transaction, the default isolation level of REPEATABLE READ would suffice. In fact, an even less restrictive isolation level would probably be fine. It can also be argued that the transaction should not even start until immediately before the INSERT statement. In this case, any less-restrictive isolation level change will likely yield a better-performing procedure that treads more lightly on other users and processes.

Processing Options

By failing to use basic joins and built-in functions, the authors of these procedures have been forced to reinvent the wheel. Aside from the waste of valuable development time, it's likely that their implementations will not be as speedy as the MySQL query engine and library of functions. You recommend that this group of stored procedures be rewritten to leverage the power of SQL and functions such as AVG(), freeing the developers to focus on application logic.