Application Development Guide

Advantages of Stored Procedures

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

Figure 3. 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 4 shows this feature.

Figure 4. Application Using a Stored Procedure

Applications using stored procedures have the following advantages:

• Reduced network traffic

  A properly designed application that processes large amounts of data using stored procedures returns only the data that is needed by the client. This reduces the amount of data transmitted across the network.

• Improved performance of server intensive work

  The more SQL statements that are grouped together for execution, 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, including:
  • 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.