a) Stored Procedures
are not standard:
The biggest drawback to Stored
Procedures is that it is not currently in the ANSI SQL standard. This results
in each DBMS vendor supporting a different flavor of Stored Procedures.
Microsoft (and Sybase) Transact-SQL, Oracle PL/SQL, and Informix SPL, though
similar, are distinct languages and not interoperable with one another. If your
shop has standardized on one particular DBMS or does not need to scale
applications across multiple platforms, then this may not be a problem. But,
then again, how many shops does this actually describe? Not many, I’d venture
The bottom line is that
scalability will suffer when applications are coded using non-standard
extensions — like SQL Stored Procedures. It is a non-trivial task to re-code
applications that were designed to use stored procedures and triggers written
using SQL Stored Procedures constructs. If an application needs to be scaled to
a platform which utilizes a DBMS that does not support SQL Stored Procedures,
then exhaustive re-coding is exactly what must be done. Consider, for example,
the steps necessary to move a SQL Server application, written in Transact-SQL
using triggers and stored procedures, to a DB2 subsystem on a mainframe. Stored
procedures must be converted from Transact-SQL into a host language such as
COBOL or C. Triggers must be coded into the application programs supporting the
DB2 database because DB2 does not yet support triggers. This process is a
nightmare that will cause any DBA to get a headache just thinking about it.
An additional potential drawback
comes in the form of the potential for performance degradation. Consider, for
example, a SQL Server stored procedure. The first time this procedure is
executed it is optimized. The optimized form of the procedure is stored in the
procedure cache and will be re-executed for subsequent users. However, the
procedure was optimized for the particular data request that was issued for the
first execution. It is very likely that future executions of the procedure are
for different amounts and types of data. If the logic were instead embedded
into an application program and compiled statically, the performance would be
optimized for all ranges of local variables. Of course, SQL Server provides an
option to always optimize (with the recompile option), but then dynamic SQL is
always used — which can cause different types of performance problems.
The solution would be to provide
a form of static SQL for stored procedures that is not optimized for a
particular type of request — but, of course, this option is not currently
Other performance drawbacks can
be realized when using SQL Stored Procedures if the developer is not careful.
For example, careless cursor coding can cause severe performance problems. But,
this can happen just as easily when cursors are used inside a host language.
The problem is more inherent to application design than it is to SQL Stored
The final drawback is that even
SQL Stored Procedures dialects are not computationally complete. Most dialects
of SQL Stored Procedures lack programming constructs to control the user’s
screen and mechanisms for data input/output (other than to relational tables).
UVN. Pardha Saradhi,