MySQL 5 Stored Procedures: Relic or Revolution?

Stored procedures bring the legacy advantages and challenges to MySQL.
Not Built for Speed?

It would be terribly unfair of us to expect the first release of the MySQL stored program language to be blisteringly fast. After all, languages such as Perl and PHP have been the subject of tweaking and optimization for about a decade, while the latest generation of programming languages—.NET and Java—has been the subject of a shorter, but more intensive optimization process by some of the biggest software companies in the world. So, right from the start, we might expect that the MySQL stored program language would lag in comparison with the other languages commonly used in the MySQL world.

Still, it's important to get a sense of the raw performance of the language. First, let's see how quickly the stored program language can crunch numbers. The first example compares a stored procedure calculating prime numbers against an identical algorithm implemented in alternative languages.

In this computationally intensive trial, MySQL performed poorly compared with other languages—five times slower than PHP or Perl, and dozens of times slower than Java, .NET or C (Figure 3).

Figure 3. Stored procedures are a poor choice for number crunching.

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 PHP or Java, you may wisely decide against using the stored program solution.

If the previous example left you feeling less than enthusiastic about stored program performance, this next example should cheer you right up. Although stored programs aren't particularly zippy when it comes to number crunching, it is definitely true that you don't normally write stored programs simply to perform math; stored programs almost always process data from the database. In these circumstances, the difference between stored program and PHP or Java performance is usually minimal, unless 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 network—the stored program runs inside the database. Figure 4 shows how a stored procedure that aggregates millions of rows can perform well even when called from a remote host across the network, while a Java program with identical logic suffers from severe network-driven response time degradation.

Figure 4. Stored procedures outperform when the network is a factor.

Logic Fragmentation

Although 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.

Also, if your application relies on stored procedures, that's an additional skill that you or your team will have to acquire and maintain.

Object-Relational Mapping

It's becoming increasingly common for an Object-Relational Mapping (ORM) framework to mediate interactions between the application and the database. ORM is very common in Java (Hibernate and EJB), almost unavoidable in Ruby on Rails (ActiveRecord) and far less common in PHP (though there are an increasing number of PHP ORM packages available). ORM systems generate SQL to maintain a mapping between program objects and database tables. Although most ORM systems allow you to overwrite the ORM SQL with your own code, such as a stored procedure call, doing so negates some of the advantages of the ORM system. In short, stored procedures become harder to use and a lot less attractive when used in combination with ORM.

______________________

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Excellent Article

Jassem Shakil's picture

Great article covering all points.

network traffic related issue

Yuan's picture

I also have the same question as Sachin, assuming store procedure perform at the same speed as a serial of raw sql code. If one (person or ORM) send all sql at once, it shouldn't make any difference right?

deneme

Anonymous's picture

very good

About network traffic using stored procedures and SQL statement

Sachin's picture

I have a confusion about the example you have given above. Suppose if i write a bunch of sql statements in my code and send them once over network. then the all statements will go to the server once. All the execution and condition checking will be done on the server and in the end i will get the status as the stored procedure will return. then can you tell me how will SP reduce network traffic??????

Also if i give an dll name that contain an SQL statement that load on server side. Then i think this does not increase network traffic.

Webinar
One Click, Universal Protection: Implementing Centralized Security Policies on Linux Systems

As Linux continues to play an ever increasing role in corporate data centers and institutions, ensuring the integrity and protection of these systems must be a priority. With 60% of the world's websites and an increasing share of organization's mission-critical workloads running on Linux, failing to stop malware and other advanced threats on Linux can increasingly impact an organization's reputation and bottom line.

Learn More

Sponsored by Bit9

Webinar
Linux Backup and Recovery Webinar

Most companies incorporate backup procedures for critical data, which can be restored quickly if a loss occurs. However, fewer companies are prepared for catastrophic system failures, in which they lose all data, the entire operating system, applications, settings, patches and more, reducing their system(s) to “bare metal.” After all, before data can be restored to a system, there must be a system to restore it to.

In this one hour webinar, learn how to enhance your existing backup strategies for better disaster recovery preparedness using Storix System Backup Administrator (SBAdmin), a highly flexible bare-metal recovery solution for UNIX and Linux systems.

Learn More

Sponsored by Storix