Database Reference
In-Depth Information
Figure 16-2.
Execution plans of dynamic SQL and stored procedures
Unfortunately, stored procedures complicate client code refactoring. The data access layer code in the
applications must be in sync with the stored procedure interface. Consider the situation where a stored procedure
is used to read the data and you need to add another filter to the underlying query. This requires you to add another
parameter to the stored procedure and change both the client and stored procedure code.
The extra complexity, however, is not necessarily a bad thing. It can work as a safety valve. The database
professionals who are implementing this change can detect and take care of potential performance issues at the
development stage rather than after a new version of the application is deployed to production.
Using stored procedures as part of the data access layer can be very advantageous if the team structure and
development procedures allow it. The team should have dedicated database professionals who write and/or
optimize stored procedures; however, this can slow down development due to the extra overhead it introduces
during code refactoring.
