Introduction to Stored Procedures
Like the Pass-Through queries we examined in
our last article, Stored Procedures are coded in the dialect of the back
end database. Indeed, a Stored Procedure consists, in many cases, of one
or more queries just like the one we examined in our last
lesson. Stored Procedures are precompiled sets of SQL statements
that can perform complex operations on the back-end server that include queries,
but can encompass far more.
The Stored Procedure resides on the back-end database
server, and exists to perform some action there. The set of SQL statements is
given a name, to which we can refer to call the procedure on a recurring basis,
to perform its designated action without having to pass the SQL statements
repeatedly. This collection and storage of the statements in a modular,
reusable object serves as the conceptual foundation of the efficiencies that Stored
Procedures offer. A diagram of the execution of the Stored Procedure,
on a SQL Server back end from MS Access, is presented in Illustration 1.
Illustration 1: MS Access Executes a Stored Procedure on a
MSSQL Server Back End
Stored Procedures are an attractive option in cases
that include one or more of the following considerations:
-
Our query will be run repeatedly;
-
Our query involves processing that, while potentially complicated,
requires little or no user intervention;
-
We encounter needs that can only be met with complicated logic,
or lengthy coding.
While we cannot exploit all the server-based attributes
of Stored Procedures from an MS Access client, we can certainly
participate in many of the resulting efficiencies within the attributes that we
can employ. The advantages of Stored Procedures that are
relevant to us from the MS Access perspective include the following:
-
Batching of multiple statements into a single, reusable object;
-
Pre-compilation of an execution plan that can be executed
repeatedly;
-
Reduction in network traffic;
-
Control of location of execution;
-
Central maintenance considerations.
Some of these advantages accrue to the use of Pass-Through
queries in general, while some make the Stored Procedure even more
advantageous than an ad hoc Pass-Through query that accomplishes the
same actions. In the simplest sense, Stored Procedures provide a
readily accessible storage location for our coding: we often lose external
files, forget logic from the past or run into multiple version conflicts when
maintaining code in external locations. Moreover, the capability to combine
multiple statements in a single, reusable object allows us to batch various "steps"
that we do together on a recurring basis, so as to permit us to employ the same
steps within the execution of a "single motion." As we shall see,
calling the Stored Procedure from MS Access is quite simple, and much
easier than typing in a query, or set of queries, that we use repetitively.
In addition to the capability of combining multiple
statements into a "one-step call," the Stored Procedure object
will run more efficiently on the database server, which means earlier
completion of its intended actions and, therefore, a more rapid return of the product
of those actions (information) to the MS Access client. Because it is
parsed and optimized when it is first executed, and a compiled version
of the Stored Procedure remains in the memory cache for ready future
use, the Stored Procedure does not need to be reparsed and reoptimized
with each use. The result is an often significantly faster execution time.
Additionally, Network traffic is reduced when we call
a Stored Procedure, rather than pass the code enclosed within the
procedure to the server from the client, because, instead of passing a query
through Access JET, then ODBC, to the back-end server, we are passing a much
smaller EXECUTE command. This command simply calls the Stored
Procedure on the server, and all processing takes place on the back-end
thereafter, until results are returned to the client. The benefits of using
the Pass-Through query (see the list of advantages in Extend Access with Pass-Through Queries)
still accrue in that we avoid the operational "taxes" imposed by the
JET / ODBC translation processes, but we gain even more efficiency in that we
are not even passing a server-sensitive query, but we are passing a mere
command to execute a query, etc., that is fully housed on the back-end
server.
The advantage of control of processing location is
another benefit we gain from using Stored Procedures that we execute
from the client. We can center the processing on the server, which is likely
to provide substantially greater processing resources. Moreover, the fact that
the Stored Procedure resides on the client provides us with the
advantage of central maintenance. If we need to modify the code to meet
changing business needs, for example, the Stored Procedure provides a
single point of maintenance versus the alternative of making changes to every
individual client in a client / server application.
Stored Procedures clearly offer advantages over other
options when it is possible to use them instead of ordinary queries. Let's
examine the steps involved in setting up Stored Procedures, and expose
some of the considerations that accompany their use, in the following sections.