Stored procedures have been viewed as the de facto standard for
applications to access and manipulate database information through the use of
codified methods, or "procedures." This is largely due to what they
offer developers: the opportunity to couple the set-based power of SQL with the
iterative and conditional processing control of code development. Developers
couldn't be happier about this; finally, instead of writing inline SQL and then
attempting to manipulate the data from within the code, developers could take
advantage of:
·
Familiar
Coding Principles
o Iterative
Loops
o Conditionals
o Method
Calls (the stored procedure itself is built and similarly called like a method)
·
One-time,
One-place Processing
o Instead
of having inline SQL code spread throughout the application, now sections of
SQL code can be encapsulated into chunks of named methods that are easily
identifiable and accessible all within one location – the "Stored
Procedure" folder of the database.
o All
complex data processing can now be performed on the server, allowing the client
processing to focus more on presentation rather than manipulation of data.
Of
course, just because something is popular doesn't always mean that it's the
best tool in all situations. The efficiency, efficacy and utility of Stored
Procedures, just like the implementation of all programming languages and
platforms, are all dependent on the needs of the client and the subsequent
architecture of the application.
Advantages of Using Stored Procedures
Stored
procedures are so popular and have become so widely used and therefore expected of Relational Database
Management Systems (RDBMS) that even MySQL finally caved to developer peer
pressure and added the ability to utilize stored procedures to their very
popular open source database.
The list below details why stored procedures have gained such a stalwart
following among application developers (and even Database Administrators for
that matter):
·
Maintainability
o Because
scripts are in one location, updates and tracking of dependencies based on
schema changes becomes easier
·
Testing
o Can
be tested independent of the application
·
Isolation
of Business Rules
o Having
Stored Procedures in one location means that there's no confusion of having
business rules spread over potentially disparate code files in the application
·
Speed
/ Optimization
o Stored
procedures are cached on the server
o Execution
plans for the process are easily reviewable without having to run the
application
·
Utilization
of Set-based Processing
o The
power of SQL is its ability to quickly and efficiently perform set-based
processing on large amounts of data; the coding equivalent is usually iterative
looping, which is generally much slower
·
Security
o Limit
direct access to tables via defined roles in the database
o Provide
an "interface" to the underlying data structure so that all
implementation and even the data itself is shielded.
o Securing
just the data and the code that accesses it is easier than applying that
security within the application code itself
Drawbacks to Using Stored Procedures
There
are certainly drawbacks to Stored Procedures that preclude them from being the
one-stop shop solution to application database access. The list below contains
some reasons why Stored Procedures might not be right for your application
solution. Interestingly, you'll probably recognize some headings that also
appear in the "Advantages" section above; this is because what one
developer views as affirmative evidence for their use might cause another to
see the same evidence to disprove their viability as a solution.
·
Limited
Coding Functionality
o Stored
procedure code is not as robust as app code, particularly in the area of
looping (not to mention that iterative constructs, like cursors, are slow and
processor intensive)
·
Portability
o Complex
Stored Procedures that utilize complex, core functionality of the RDBMS used
for their creation will not always port to upgraded versions of the same
database. This is especially true if moving from one database type (Oracle) to
another (MS SQL Server).
·
Testing
o Any
data errors in handling Stored Procedures are not generated until runtime
·
Location
of Business Rules
o Since
SP's are not as easily grouped/encapsulated together in single files, this also
means that business rules are spread throughout different Stored Procedures.
App code architecture helps to ensure that business rules are encapsulated in
single objects.
o There
is a general opinion that business rules / logic should not be housed in the
data tier
·
Utilization
of Set-based Processing
o Too
much overhead is incurred from maintaining Stored Procedures that are notcomplex enough. As a result, the
general consensus is that simple SELECT statements should not be bound to Stored Procedures
and instead implemented as inline SQL.
·
Cost
o Depending
on your corporate structure and separation of concern for development, there is
the potential that Stored Procedure development could potentially require a
dedicated database developer. Some businesses will not allow developers access
to the database at all, requiring instead a separate DBA. This will
automatically incur added cost.
o Some
companies believe (and sometimes it's true, but not always) that a DBA is more
of a SQL expert than an application developer, and therefore will write better
Stored Procedures. In that case, an extra developer in the form of a DBA is
required.
Alternatives to Stored Procedures
Because
Stored Procedures are not always the perfect solution nor do they satisfy all
the needs of all developers, other solutions exist that attempt to provide most of what a developer wants to do
when accessing a database backend. These include:
·
In-line
or Parameterized Queries
o These
are written within the application code itself
·
Object
Relational Mapping (ORM)
o Provides
an abstraction to the database without having to manually write data access
classes. At this point, most all major platforms offer some form of ORM
software, as illustrated at this site.
When Should You Use Stored Procedures?
Stored
Procedures may not always be the right answer for processing data, but there's
also not enough compelling evidence to not use them either. Whether or not
to use them determines on your particular situation and ability to develop the
Stored Procedure(s) to match. Just like with writing a good, quality
application, if you or your developers can write good, quality Stored
Procedures, then by all means implement them. If they can't, then another
solution might be best for you.
No comments:
Post a Comment