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:
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.
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.