Advanced Stored Procedures

MySQL 5.0 and higher versions support stored routines (procedures and functions) that are based on the SQL:2003 standard. The introduction of stored programs in MySLQ 5 has been a significant milestone of the MySQL language.

This tutorial will offer a brief overview of the stored program language and focus on how to improve and tune stored programs. It will include profiling, debugging, and optimizations of stored programs.

The tutorial assumes that attendees are already familiar with standard MySQL statements tuning.

Outline The tutorial will present an overview on how to create a stored program, pass information in and out of the stored program, how to interact with the database, and will introduce all the basic SQL Language mechanisms (begin, local and session variables, branching, loops, cursor, trigger, etc.).

The tutorial will focus on performance optimization and tuning and will look in detail to the following topics of stored programs.

1. Advantages and disadvantages of stored programs. Stored routines can provide improved performance because less information needs to be sent between the server and the client. The tradeoff is that this does increase the load on the database server because more of the work is done on the server side and less is done on the client side.
2. Reducing network traffic with stored procedures. In those scenarios where a program is required to process large number of rows from the database, a stored procedure can outperform better than a client program because it does not have to wait for the data to be transferred through the nertwork.
3. Stored procedures as an alternative to expensive SQL. We will provide an example of a stored program that avoids self-joins.
4. Loop optimization. Optimizing loop processing is a basic step when optimizing the performamce of a program, including stored programs. We will show a few examples to demonstrate loop tuning.
5. Branching optimization and the best choice. Testing for the more likely matches in the branching statements, minimizing the number of comparisons, avoiding for unnecessary comparisons, using case statement versus if statement are some of the scenarios that will be examined.
6. Cursor and triggers overhead.
7. Dynamic SQL. Utilize the best feature of dynamic SQL to include the prevention of code injection. Dynamic SQL statements are constructed, parsed, and compiled at runtime, not at the time the code is compiled. Dynamic SQL offers flexibility and you can process dynamic SQL using MySQL PREPARE statements.
8. Exception and error handling and improving an application effectively utilizing the exceptions and errors capabilities of stored procedures. Examples will be provided on how to use exception and error handling properly; you can predict that a certain error will occur and you can include a handler for the error to allow a graceful iinformative failure.
9. Inspecting stored procedures. MySQL provides a list or statements to provide information about created stored procedures.
10. Debugging stored procedures. MySQL does not provide native debugging capabilities, but it is possible to create debugging tools for stored procedures.
11. Profiling stored procedures. MySQL 5.0 provides a profiling capability (SHOW PROFILE utility) that will be used to profile stored programs. A few stored programs will be profiled and for some of them their profile will be compared with the execution of the equivalent statement without the stored procedure.