Embedded SQL Programming Guide

Why Use Stored Procedures?

Figure 18 shows how a normal database manager application accesses a database located on a database server.

Figure 18. Application Accessing a Database on a Server

All database access must go across the network which, in some cases, results in poor performance.

Using stored procedures allows a client application to pass control to a stored procedure on the database server. This allows the stored procedure to perform intermediate processing on the database server, without transmitting unnecessary data across the network. Only those records that are actually required at the client need to be transmitted. This can result in reduced network traffic and better overall performance. Figure 19 shows this feature.

Figure 19. Application Using a Stored Procedure

Applications using stored procedures have the following advantages:

• Reduced network traffic.

  Applications that process large amounts of data but require only a subset of the data to be returned to the user. A properly designed application using stored procedures returns only the data that is needed by the client; the amount of data transmitted across the network is reduced.

• Improved performance of server intensive work.

  Applications executing SQL statements that can be grouped together without user intervention. The more SQL statements that are grouped together, the larger the savings in network traffic. A typical application requires two trips across the network for each SQL statement, whereas an application using the stored procedure technique requires two trips across the network for each group of SQL statements. This reduces the number of trips, resulting in a savings from the overhead associated with each trip.

• Access to features that exist only on the database server.

  These features include:

  • Commands to list directories on the server (such as LIST DATABASE DIRECTORY and LIST NODE DIRECTORY) can only run on the server.
  • The stored procedure may have the advantage of increased memory and disk space if the server computer is so equipped.
  • Additional software installed only on the database server could be accessed by the stored procedure.

[ DB2 List of Books | Search the DB2 Books ]