Specific call reduction approaches – stored procedures

When I drop into discussing specific approaches to doing thing, I’m going to have to use the terms and facilities that are commonly available in the enterprise deployment environment that I’ve spent most of my time in, which is Java/J2EE/Oracle. That said, the reasons why these approaches improve performance are about the physical structure of common deployment environments, so they should hold true on any technology stack.

In general, an enterprise application’s business logic and database tiers will reside on different platforms in the data center with a network segment in between. That separation means that calls between the tiers are going to have durations hundreds or thousands of times higher than calls within each tier. Also, when application data needs to be written to the database, SQL will probably be what is being executed and in modern applications that SQL will be prepared/compiled and will use bind variables.

When SQL using bind variables is used, there are several interactions between the DB client and the DB server:

prepare – the SQL text is passed to the server and compiled
bind/execute – bind variable values are passed to the server and the statement is executed
fetch – in the case of queries, one or more batches of rows are returned to the client

Certainly, there are optimizations available in certain deployment environments, but in the end these are three separate DB client/server interactions and they will generally all occur. So, for each statement executed we may have three separate network calls and each will be hundreds of times longer that an intra-tier call.

What we need is a way to increase the granularity of the statements being executed, which will decrease the number of executions necessary to implement a particular application use case. Stored procedures, which are basically blocks of code that can be called with parameters in the DB tier, are a very good way to accomplish this. What they allow is the accumulation of all the bind variables that would have been passed to the separate statement executions into a single parameter list to be passed in a single execution to the DB where the original statements can then be executed.

In situations where the statements to be executed are queries, stored procedures can increase the benefit you see from platform specific optimizations. The main one I’m thinking of here is what Oracle calls ‘row pre-fetch’, which returns the first batch of returned rows from the ‘execute’ step in the statement life cycle. For a single query being executed, particularly queries that return few rows, dropping the fetch call across the network is an important gain. But when many queries are accumulated into a stored procedure with multiple result sets returned as output parameters (Oracle calls these cursor references), the single execution of the procedure call statement can return all of the query result sets pre-fetched, thus completely avoiding additional network calls to support the multiple queries being run.