MySQL 5 Stored Procedures: Relic or Revolution?

Stored procedures bring the legacy advantages and challenges to MySQL.
Data Abstraction

It is generally a good practice to separate your data access code from your business logic and presentation logic. Data access routines often are used by multiple program modules and are likely to be maintained by a separate group of developers. A very common scenario requires changes to the underlying data structures while minimizing the impact on higher-level logic. Data abstraction makes this much easier to accomplish.

The use of stored programs provides a convenient way of implementing a data access layer. By creating a set of stored programs that implement all of the data access routines required by the application, we are effectively building an API for the application to use for all database interactions.

Reducing Network Traffic

Stored programs can improve application performance radically by reducing network traffic in certain situations.

It's commonplace for an application to accept input from an end user, read some data in the database, decide what statement to execute next, retrieve a result, make a decision, execute some SQL and so on. If the application code is written entirely outside the database, each of these steps would require a network round trip between the database and the application. The time taken to perform these network trips easily can dominate overall user response time.

Consider a typical interaction between a bank customer and an ATM machine. The user requests a transfer of funds between two accounts. The application must retrieve the balance of each account from the database, check withdrawal limits and possibly other policy information, issue the relevant UPDATE statements, and finally issue a commit, all before advising the customer that the transaction has succeeded. Even for this relatively simple interaction, at least six separate database queries must be issued, each with its own network round trip between the application server and the database. Figure 1 shows the sequences of interactions that would be required without a stored program.

Figure 1. Network Round Trips without Stored Procedure

On the other hand, if a stored program is used to implement the fund transfer logic, only a single database interaction is required. The stored program takes responsibility for checking balances, withdrawal limits and so on. Figure 2 shows the reduction in network round trips that occurs as a result.

Figure 2. Network Round Trips with Stored Procedure

Network round trips also can become significant when an application is required to perform some kind of aggregate processing on very large record sets in the database. For instance, if the application needs to retrieve millions of rows in order to calculate some sort of business metric that cannot be computed easily using native SQL, such as average time to complete an order, a very large number of round trips can result. In such a case, the network delay again may become the dominant factor in application response time. Performing the calculations in a stored program will reduce network overhead, which might reduce overall response time, but you need to be sure to take into account the differences in raw computation speed, which I discuss later in this article.

Creating Common Routines across Multiple Applications

Although it is commonplace for a MySQL database to be at the service of a single application, it is not at all uncommon for multiple applications to share a single database. These applications might run on different machines and be written in different languages; it may be hard, or impossible, for these applications to share code. Implementing common code in stored programs may allow these applications to share critical common routines.

For instance, in a banking application, transfer of funds transactions might originate from multiple sources, including a bank teller's console, an Internet browser, an ATM or a phone banking application. Each of these applications could conceivably have its own database access code written in largely incompatible languages, and without stored programs we might have to replicate the transaction logic, including logging, deadlock handling and optimistic locking strategies, in multiple places and in multiple languages. In this scenario, consolidating the logic in a database stored procedure can make a lot of sense.

______________________

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