How & Where to use Stored Procedures
1. One good reason to use a stored procedure is when you must make a complex, multi-staged query that pulls from multiple collated sources.
2. Your application requires data that needs to be transposed or aggregated from multiple queries or views. You can offload that from the application into the db. Here you have to do a performance analysis since a) database engines are more efficient that app servers in doing these things, but b) app servers are (sometimes) easier to scale horizontally.
3. Use stored procedures for fine grain access control. You do not want someone running Cartesian joins in your DB, and you cannot just forbid people from executing arbitrary SQL statements like that either. A typical solution is to allow arbitrary SQL statements in development and UAT environments, while forbidding them in system test and production environments. Any statement that must make it to system test or production goes into a stored procedure, code-reviewed by both developers and DBAs.
4. Any valid need to run a SQL statement not in a stored procedure goes through a different username/account and connection pool (with the usage highly monitored and discouraged.)
5. In systems like Oracle, you can get access to LDAP, or create symlinks to external databases (say calling a stored procedure on a business partner's DB via VPN.) This is an easy route to spaghetti code. (This is true for all programming paradigms, and sometimes you have specific business/environment requirements for which this is the only solution.) Stored procedures help encapsulate that nastiness in one place alone, close to the data and without having to traverse to the app server.
6. Whether you run this on the DB as a stored procedure or on your app server depends on the trade-off analysis that you, as an engineer, have to make. Both options have to be analyzed and justified with some type of analysis. Saying putting business logic in the logic-tier is a best practice does not mean that all stored procedures in the data-tier is a bad practice; saying otherwise is an engineering copout.
7. In situations where you simply cannot scale up your application server (i.e. no budget for new hardware or cloud instances) but with plenty of capacity on the DB back-end (this is more typical that many people care to admit), it pays to move business logic to stored procedures. This can lead to anemic domain models, but sometimes you can make a business case for bad engineering decisions
Business Logic Surprisingly,
the business logic is not always the same across the enterprise. In an ideal world you could put all the logic in stored procedures and share that logic between applications. But quite often the logic differs based on the applications and your stored procedures end up becoming overly complex monoliths that people are afraid to change, because they cannot determine the implications of changing. However, with a good object oriented language you can code a data access layer which has some standard interface/hooks that each application can override to their own needs
Security Stored procedures
can be good for security. You can cut all the access to the underlying tables and only allow access through the stored procedures. With some modern techniques like XML you can have stored procedures that do batch updates. Then all access is controlled through the stored procedures so as long as they are secure/correct the data can have more integrity. However, security at the DB level isn't granular enough to make context-aware decisions. Because of performance and management overhead, it's unusual to have per-user connections as well - so you still need some level of authorization in your app code. You can use role based logins, but you will need to create them for new roles, maintain which role you're running as, switch connections to do "system level" work like logging, etc. And, in the end, if your app is pwned - so is your connection to the DB, meaning you’ve gained nothing.
SQL Injection
The SQL injection argument doesn't really apply so much anymore since we have parameterized queries on the programming language side. Also, really even before parameterized queries, a little replace ("'", "''") worked most of the time as well (although there are still tricks to use to go past the end of the string to get what you want).
Large Datasets
On the plus side, stored procedures are more efficient for working with a big dataset and applying a multiple queries/criteria to shrink it down before returning it to the business layer. If you have to send a bunch of huge datasets to the client application and break down the data at the client it will be much more inefficient than just doing all the work at the server.
Consultants, Vendors, and Lock-in What's best for a company is very often not best for a consulting firm or other software vendor. A smart company desires to have a permanent advantage over its competitors. By contrast a software vendor wants to be able to hawk the same solution to all the businesses in a particular industry, for the lowest cost. If they are successful in this, there will be no net competitive advantage for the client. Applications developed by consultants come and go, but the corporate database lives forever. One of the primary things an RDBMS does is to keep junk data from entering the database. This can involve stored procedures. If the logic is good logic and highly unlikely to change from year to year, why should it not be in the database, keeping it internally consistent, irrespective of whatever application is written to use the database? Years later
someone will have a question they want to ask of the database and it will be answerable if junk has been prevented from entering the DB. On the other hand, DB vendors want to promote lock-in, and could well encourage the use of things like triggers and stored procedures. While there may be good technical reasons for this, it could also be a case of the software vendor securing your long-term commitment to their product, and thereby enhancing their long-term bottom line.
Comments
Post a Comment