The Pros and Cons of Stored Programs in Modern Applications

There is a persistentand often livelydebate in the programming community about the benefits and appropriateness of using stored programs in applications.

Database stored programs first came to prominence in the late 1980s and early 1990s during what might be called the client/server revolution. In the client/server environment of that time, stored programs had some obvious advantages (aspects of which persist in N-tier and Internet-based architectures):

• Client/server applications typically had to carefully balance processing load between the client PC and the (relatively) more powerful server machine. Using stored programs was one way to reduce the load on the client, which might otherwise be overloaded.
• Network bandwidth was often a serious constraint on client/server applications ; execution of multiple server-side operations in a single stored program could reduce network traffic.
• Maintaining correct versions of client software in a client/server environment was often problematic. Centralizing at least some of the processing on the server allowed a greater measure of control over core logic.
• Stored programs offered clear security advantages, because in the client/server paradigm, end users typically connected directly to the database to run the application. By restricting access to stored programs only, users would not be able to perform ad hoc operations against tables and other database structures.

The use of stored programs in client/server applications was, and is, most prevalent in applications that use Microsoft SQL Server (and its technological predecessor, Sybase) and Oracle. The Microsoft SQL Server and Oracle stored program languages (Transact-SQL and PL/SQL, respectively) have substantially different characteristicsespecially regarding the ability of a stored program to return a result set. The differences between the two languages have resulted in somewhat different usage patterns:

SQL Server-based applications

For these applications, the dominant pattern is to encapsulate all database interaction between client and serverincluding queriesinto stored programs. This is cited as providing better security and reduced network traffic.

Oracle-based applications

For these applications, it was initially impossible to return a result set from a stored program and, although this became possible in later releases, it was never particularly convenient or easy to do so. As a result, Oracle-based applications tended to use stored programs to implement transaction processing, but would use native SQL to retrieve result sets.

With the emergence of three-tier architectures and web applications, many of the incentives to use stored programs from within applications disappeared. Application clients are now often browser-based; security is predominantly handled by a middle tier; and the middle tier possesses the ability to encapsulate business logic. Most of the functions for which stored programs were used in client/server applications can now be implemented in middle-tier code (e.g., in PHP, Java, C#, etc.). Transferring processing to the middle tier can also enhance load balancing and scalability.

Even so, many of the original advantages of stored programs (such as enhanced security and reduction in network traffic) still apply, if to a reduced degree. The use of stored programs is still regarded as a "best practice" by many application developers and architects.

Today, there are three schools of thought regarding the use of stored programs in applications:

All stored programs, all the time

This segment of the development community continues to believe that stored programs should be used for all interaction between the client (now the middle tier) and the database. They argue that this pattern provides more security to the database, and also provides a level of abstraction between the underlying data model and the business logic in the middle tier.

Stored programs only when absolutely necessary

This segment believes that stored programs should play only a minor role in a modern application development. They argue that stored programs add additional and unnecessary complexity to the application design; that they fragment the logic between the middle tier and the database; and that they get in the way of object-relational mapping schemes such as Java J2EE's CMP and Hibernate.

Use what works

This segment (probably the quiet majority) is fairly pragmaticthey use stored programs selectively when the use of a stored program seems warranted, but they tend to use native SQL when it is easier and more convenient to do so.

It's up to you to decide which model works best for you and your application. In the next few sections we will try to provide you with as much information as we can to help you make an informed decision. To sum up our personal feelings on the matter, we do think that an application that encapsulates all database interaction within stored programs is employing a valid and effective pattern. In particular, this kind of application can be made virtually immune to SQL injection attacks, and will be much less vulnerable to exploits based on compromised passwords. We also believe in separating data access logic from business logic, and the use of stored programs is a good way to do this. However, stored programs are not a natural choice for all applications; for instance, using stored programs exclusively tends to interfere with object-relational mapping schemes such as J2EE CMP and Hibernate.

In the next few sections, we'll look in some detail at the advantages stored programs offer an application and compare those to possible disadvantages. To summarize here, stored programs offer these advantages:

• Stored programs can improve the security of your database server.
• Stored programs offer a mechanism to abstract data access routines, hiding your implementation behind a procedural interface and making it easier to evolve your data structures over time.
• Stored programs can reduce network traffic.
• Stored programs can be used to implement functionality that is neededand can be calledfrom multiple applications, and from multiple places within a single application. This can be handy when applications written in frameworks that don't interoperate very well (.NET and Java for instance) access the same database.
• Stored programs allow for a convenient division of duties between those whose skills are database-centric and those whose skills are programming-centric.
• You can often improve the portability of your application code by moving logic into stored programs.

Against these possible advantages, consider the following disadvantages:

• Stored programs might be slowerespecially for computationally expensive operationsthan equivalent middle-tier code.
• The use of stored programs can lead to fragmentation of your application logiclogic may be split between the database and the application server tier, making it difficult to track down design flaws or implementation bugs.
• Stored programs are usually written in a different language from your application server tier, requiring a wider range of skills in your development team.
• Stored programs can be more difficult to debug (depending on the implementation: MySQL does not yet offer an integrated stored program debugger).
• Most object-relational mapping systems (e.g., J2EE CMP and Hibernate) cannot seamlessly exploit stored programs.
• While stored program calls may sometimes be more portable than native SQL, in practice this is not true for all implementations. Of the "big four," only DB2 and MySQL implement the ANSI standard for stored programs. As a result, MySQL stored program calls often look and act substantially different from calls made in Oracle or SQL Server.