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.

12.3.1. 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 timewhere 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 routineand you have a choice between implementing it in the stored program language or in another language such as Javayou may wisely decide against using the stored program solution.

12.3.2. 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 in the application layer. For instance, there is currently no debugger that can trace program flow from the application code into the MySQL stored program code.

12.3.3. They Do Not Provide Portability

We said earlier that stored programs could be used to build RDBMS-independent applications by encapsulating RDBMS-dependent SQL in stored program calls. Unfortunately, this is only possible for RDBMS types that support similar semantics for processing parameters and returning result sets.

The stored programs implemented by MySQL, DB2, and Microsoft SQL Server all behave in a very similar wayall can return multiple result sets, and for most languages, the calls for accessing these result sets are compatible.

Unfortunately, Oracle is an exception in this regard; Oracle stored programs can return result sets, but they are returned as references in output parameters, rather than as result sets in their own right. In order to retrieve these result sets, you have to write application code that is highly Oracle specific.

So while applications that use only stored programs are reasonably portable between MySQL and either DB2 or SQL Server, if portability between MySQL and Oracle is your objective, you are probably better advised to use ANSI-standard SQL calls, rather than stored program calls, at least when implementing calls that will return result sets.