33.2. Using Stored Routines to Enhance Performance

The use of stored routines can make a significant difference in the performance of an application. The benefits occur in terms of both the amount of network bandwidth consumed by the exchange of information between a client and the server, and the time necessary for those exchanges to occur.

Consider the case of a moderately complex operation that, without the use of stored routines, requires the client to send 10 SQL statements to the server and to process the result of each one before producing a final result. The operation involves 10 statements sent to the server, and 10 results returned to the client. Some of the results might be simple indications of success or failure, but some might be result sets containing many rows to be processed by the client. This can produce a significant amount of network traffic, and it takes time for all of it to be transmitted.

If the operation is implemented as a stored procedure, the performance characteristics change. The procedure is sent to the server once and stored there. To execute the operation later, the client invokes the routine. In this case, the server executes the statements in the routine without them having to be sent again by the client. Also, the server processes the result of each statement as it executes it without the result having to cross the network. The only traffic generated is for the statement that invokes the routine and the final result that it produces.

Moving processing to the server side can be beneficial in environments where clients are not equipped to do much computation, such as is often the case for mobile clients.