How much of your database application should be in stored procedures?

Question

I'm writing a second interface to a database application to get around some shortcomings of the original interface. Unfortunately, if I create new records, expected audit trail records will not be generated. It sure would have been nice if the database design had worked such details into table triggers, or else provided a stored procedure API for such operations as inserting new records into these tables.

Should applications be designed this way, in general? How much of a database application should be in stored procedures?

Answer 1

IMHO you should create SPs for any logic that is dedicated to handling data and can serve as a good abstraction between the app and the database. A good example of this is in-app hashing of passwords. I always create an SP that creates the record and hashes the password for me....

because the hashed password is really the databases domain. I also create an SP to check if a password string is the same hash (yes this is transmitted to the database, but I trust my database and network).

Forgetting the weak-ish example: when you can separate concerns without damaging the design and integrity of your system... make the abstraction and do it!

The financial sector uses alot of SPs to create an effective API on-top of the database for handling the down-and-dirty financial operations, because they can build-in auditing outside the application in the stored procedures.

In a typical design, what should be RDBMS-side and app-side can be quite obvious when you think ... 'Does the application need to do this with the data, does it care??'

Answer 2

Our teams have a rule - any data going into or out of a database must go through a stored procedure. We have even gone so far as to build restrictions into our data access components to enforce this.

As for other things such as book keeping, audit trails, etc, we put them in stored procedures as well. Triggers are convenient, but we found that for the purposes of audit logs involving the who, what, and when of an update, not all the data we want is redily available to the trigger. The only time we've ever used triggers is to keep a full change history of every record in a table, but even then, in hindsight, it caused maintenance problems and a sproc would have been better for us.

Answer 3

As a general rule, I prefer to keep as much of the data-twiddling in SPs; a semi-generic data-access layer will handle firing off the requests, and presenting the data to the interface.

The old wisdom of "increased performance" with SPs is really no longer relevant*, but I've found maintenance to be much simpler with this method.

However ... I avoid triggers like the plague. They're difficult to maintain, nearly impossible to debug, and lead to all kinds of unintended side effects.

---

*The common perception is that Stored Procedures perform better than ad-hoc sql queries. However, this is false:

SQL Server 2000 and SQL Server version 7.0 incorporate a number of changes to statement processing that extend many of the performance benefits of stored procedures to all SQL statements. SQL Server 2000 and SQL Server 7.0 do not save a partially compiled plan for stored procedures when they are created. A stored procedure is compiled at execution time, like any other Transact-SQL statement. SQL Server 2000 and SQL Server 7.0 retain execution plans for all SQL statements in the procedure cache, not just stored procedure execution plans.

-- SqlServer's Books Online

Answer 4

Different philosophies have different answers, but here is mine

1. Data intensive calculations -- in particular, anything that is very expensive to do outside of the database.

2. Shared code. My last job that used a relational database was connecting to a library of database functions. The Java code knew nothing of this connection. To avoid duplication any function needed by both the code talking to the library and used by Java were stored procedures.

The other concern is making certain that difficult or critical code be under test. This does not mean it can't be a stored procedure, just that it may be easier at least at first to write it where testing is easier.

Obviously if the function needs to talk to external services (that the database does not have easy access to), you might not want to make that a stored procedure.

Answer 5

Here's the other point of view: None.

A database is for persistent data.

Your application is for processing. All logic, all code, all decision-making, all calculations, all referential integrity, everything should be in your application.

It makes life very simple if you have a simple database that has just the data.

In the long run, many people find that their stored procedures and triggers have created a tangled mess that is hard (or impossible) to maintain. This is easy to prevent by using the database as a fast, large, easy-to-backup repository for data. Indexes and views and a proper part of the database.

Everything else doesn't belong there.

Folks lift up "issues" like the following.

1. The database is "central". Somehow an application server isn't central. I don't get it. If you want centralized processing, use an application server. If you want a lot of complex, centralized processing, use an ESB.

2. Some processing is "closer to the database." There are many bad examples. The good examples often has to do with surrogate key assignment. I prefer to use an ORM layer (iBatis, or SQLAlchemy or something) to do this. You get the same effect without writing or maintaining a stored procedure.

3. Some processing requires auditing. Actually, all processing requires auditing. Use Business Domain objects with an ORM and do your audit processing in your application.

   The RDBMS-based built-in auditing is good. Any auditing you write with stored procedures is not going to be as good. Solution -- use logging in your application, and the built-in audit features of the database engine. Avoid triggers and SP's for this.

4. Stored procedures are faster. This is untrue. Good transaction design makes things faster. Stored procedures impose a number of constraints on your architecture, which sometimes forces you to create small, tidy transactions. Try writing the same small, focused transaction in Java and you'll find the PL/SQL isn't actually faster.

You can live without stored procedures. In the long run, you'll find that the design discipline of focused transactions has more value than the procedure itself.

Example

"if I create new records, expected audit trail records will not be generated"

If you had a proper Business Domain class definition to create your records, it would also create audit records for you in your application.

Answer 6

This depends entirely on your environment. The answer to the question really isn't a coding problem, or even an analysis issue, but a business decision.

If your database supports just one application, and is reasonably tightly integrated with it, then it's better, for reasons of flexibility, to place your logic inside your application program. Under these circumstances handling the database simply as a plain data repository using common functionality looses you little and gains flexibility - with vendors, implementation, deployment and much else - and many of the purist arguments that the 'databases are for data' crowd make are demonstratively true.

On the other hand if your are handling a corporate database, which can generally be identified by having multiple access paths too it, then it is highly advisable to screw down the security as far as you can. At the very least all appropriate constraints should enabled, and if possible access to the data should be through views and procedures only. Whining programmers should be ignored in these cases as...

1. With a corporate database the asset is valuable and invalid data or actions can have business-threatening consequences. Your primary concern is safeguarding the business, not how convenient access is for your coders.
2. Such databases are by definition accessed by more than one application. You need to use the abstraction that stored procedures offer so the database can be changed when application A is upgraded and you don't have the resource to upgrade application B.
3. Similarly the encapsulation of business logic in SPs rather than in application code allows changes to such logic to be implemented across the business more easily and reliably than if such logic is embedded in application code. For example if a tax calculation changes it's less work, and more robust, if the calculation has to be changed in one SP than multiple applications. The rule of thumb here is that the business rule should be implemented at the closest point to the data where it is unique - so if you have a specialist application then the logic for that app can be implemented in that app, but logic more widely applicable to the business should be implemented in SPs.

In your case you now have multiple applications accessing the same database so you should be moving logic and audit functions from the application level into the database. Myself I would usually put audit functionality into the database itself anyway because if I am ever then called on to investigate some fraud issue (and it has happened more than once) I simply feel I can stand up and be certain of my findings with considerably more confidence than if it's in the application layer - there's simply less opportunities for caveats that way.

Coders who dive into religious wars over the use or not of SPs generally have worked in only one environment or the other so they extrapolate their limited experience into a cast-iron position - which indeed will be perfectly defensible and correct in the context from which they come but misses the big picture. As always, you should make you decision on the needs of the business/customers/users and not on the which type of coding methodology you prefer.

Answer 7

Everything should be in its appropriate place.

The database has to protect its perimeter. It cannot assume that the only people allowed to connect to it is a trusted and tested application.

Sooner or later someone is going to connect to it with a buggy application or a buggy client library (Oracle has some clients which allow users to create invalid dates). Or they are going to connect with a report writer or Access or Excel.

My preference is not to allow direct table access, but to only allow access to views, SPs etc., so role-based permission is managed there and an interface contract is formed very similarly to the way you define interfaces for your classes or services at the application layer. This does not mean that all/much application logic is in stored procedures, but it does mean that the database enforces certain rules - usually basic ones like referential integrity - so that the application CAN make some assumptions about how the database was used in the past by previous versions of itself safely.

There was a great panel discussion of this at Tech Ed 2009 which you can watch here.

Answer 8

Fraud is much easier to commit in a database that has dynamic sql coming from an application because permissions are set at the table level. Most fraud is committed by current employees who often have easy access to the server. Also disgruntled employees like to do things to destroy data as well. Exactly two people should be able to access the tables in your production datbase if any financial data is in it, a dba and one alternate dba. Everyone else should have only the tasks which are described by using the stored procs and nothing else. Auditing must be at the datbase level or it is essentially worthless. If I audit at the application level in an application that does not use stored procs (these two usually run together), it is easy for someone to directly access the tables, change things and then be untraceable. Audit logs are to catch the changes to the data, that can only be done fully at the point where the data changes.

If the data manipulation is complex, stored procs offer the best method for performance tuning as well.

Another reason to use procs is that it is far easier to refactor the database when needed if all the calls to a table are encapsulated in procs. It is also far easier to upload small changes to prod.