MySQL Stored Procedure Programming

Disadvantages of Stored Programs

So far, we’ve seen that stored programs can offer some significant advantages. Now let’s look at the downside of using stored programs.

They Can Be Computationally Inferior

In Chapter 22 we compare the performance of MySQL stored programs and other languages when performing computationally intensive routines. Our conclusion is that stored programs, in general, and MySQL stored programs, in particular, are slower than languages such as PHP, Java, and Perl when executing “number crunching” algorithms, complex string manipulation, and the like.

Most of the time, stored programs are dominated by database access time—where stored programs have a natural performance advantage over other programming languages because of their lower network overhead. However, if you are writing a number-crunching routine—and you have a choice between implementing it in the stored program language or in another language such as Java—you may wisely decide against using the stored program solution.

They Can Lead to Logic Fragmentation

While it is generally useful to encapsulate data access logic inside stored programs, it is usually inadvisable to “fragment” business and application logic by implementing some of it in stored programs and the rest of it in the middle tier or the application client.

Debugging application errors that involve interactions between stored program code and other application code may be many times more difficult than debugging code that is completely encapsulated ...