MySQL 5 Stored Procedures: Relic or Revolution?

Stored procedures bring the legacy advantages and challenges to MySQL.
Are Stored Procedures Portable?

Although all relational databases implement a common set of SQL syntax, each RDBMS offers proprietary extensions to this standard SQL, and MySQL is no exception. If you are attempting to write an application that is designed to be independent of the underlying database, you probably will want to avoid these extensions in your application. However, sometimes you'll need to use specific syntax to get the most out of the server. For instance, in MySQL, you often will want to employ MySQL hints, execute non-ANSI statements, such as LOCK TABLES, or use the REPLACE statement.

Using stored programs can help you avoid RDBMS-dependent code in your application layer while allowing you to continue to take advantage of RDBMS-specific optimizations. In theory, stored program calls against different databases can be made to look and behave identically from the application's perspective. You can encapsulate all the database-dependent code inside the stored procedures. Of course, the underlying stored program code will need to be rewritten for each RDBMS, but at least your application code will be relatively portable.

However, there are differences between the various database servers in how they handle stored procedure calls, especially if those calls return result sets. MySQL, SQL Server and DB2 stored procedures behave very similarly from the application's point of view. However, Oracle and Postgres calls can look and act differently, especially if your stored procedure call returns one or more result sets.

So, although using stored procedures can improve the portability of your application while still allowing you to exploit vendor-specific syntax, they don't make your application totally portable.

Other Considerations

MySQL stored programs can be used for a variety of tasks in addition to traditional application logic:

  • Triggers are stored programs that fire when data modification language (DML) statements execute. Triggers can automate denormalization and enforce business rules without requiring application code changes and will take effect for all applications that access the database, including ad hoc SQL.

  • The MySQL event scheduler introduced in the 5.1 release allows stored procedure code to be executed at regular intervals. This is handy for running regular application maintenance tasks, such as purging and archiving.

  • The MySQL stored program language can be used to create functions that can be called from standard SQL. This allows you to encapsulate complex application calculations in a function and then use that function within SQL calls. This can centralize logic, improve maintainability and, if used carefully, improve performance.

You Decide!

The bottom line is that MySQL stored procedures give you more options for implementing your application and, therefore, are undeniably a “good thing”. Judicious use of stored procedures can result in a more secure, higher performing and maintainable application. However, the degree to which an application might benefit from stored procedures is greatly dependent on the nature of that application. I hope this article helps you make a decision that works for your situation.

Guy Harrison is chief architect for Database Solutions at Quest Software (www.quest.com). This article uses some material from his book MySQL Stored Procedure Programming (O'Reilly 2006; with Steven Feuerstein). Guy can be contacted at guy.harrison@quest.com.

______________________

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.

White Paper
Linux Management with Red Hat Satellite: Measuring Business Impact and ROI

Linux has become a key foundation for supporting today's rapidly growing IT environments. Linux is being used to deploy business applications and databases, trading on its reputation as a low-cost operating environment. For many IT organizations, Linux is a mainstay for deploying Web servers and has evolved from handling basic file, print, and utility workloads to running mission-critical applications and databases, physically, virtually, and in the cloud. As Linux grows in importance in terms of value to the business, managing Linux environments to high standards of service quality — availability, security, and performance — becomes an essential requirement for business success.

Learn More

Sponsored by Red Hat

White Paper
Private PaaS for the Agile Enterprise

If you already use virtualized infrastructure, you are well on your way to leveraging the power of the cloud. Virtualization offers the promise of limitless resources, but how do you manage that scalability when your DevOps team doesn’t scale? In today’s hypercompetitive markets, fast results can make a difference between leading the pack vs. obsolescence. Organizations need more benefits from cloud computing than just raw resources. They need agility, flexibility, convenience, ROI, and control.

Stackato private Platform-as-a-Service technology from ActiveState extends your private cloud infrastructure by creating a private PaaS to provide on-demand availability, flexibility, control, and ultimately, faster time-to-market for your enterprise.

Learn More

Sponsored by ActiveState