What's the benefit of using a lot of complex stored procedures

Question

For typical 3-tiered application, I have seen that in many cases they use a lot of complex stored procedures in the database. I cannot quite get the benefit of this approach. In my personal understanding, there are following disadvantages on this approach:

1. Transactions become coarse.
2. Business logic goes into database.
3. Lots of computation is done in the database server, rather than in the application server. Meanwhile, the database still needs to do its original work: maintain data. The database server may become a bottleneck.

I can guess there may be 2 benefits of it:

1. Change the business logic without compile. But the SPs are much more harder to maintain and test than Java/C# code.
2. Reduce the number of DB connection. However, in the common case, the bottleneck of database is hard disk io rather than network io.

Could anyone please tell me the benefits of using a lot of stored procedures rather than letting the work be done in business logic layer?

Answer 1

You listed one of the main ones putting business logic in the Db often gives the impression of making it easier to maintain.

Generally complex SP logic in the db allows for cheaper implementation of the actual implementation code, which may be beneficial if its a transitional application (say being ported from legacy code), its code which needs to be implemented in several languages (for instance to market on different platforms or devices) or because the problem is simpler to solve in the db.

One other reason for this is often there is a general "best practice" to encapsulate all access to the db in sps for security or performance reasons. Depending on your platform and what you are doing with it this may or may not be marginally true.

Answer 2

I don't think there are any. You are very correct that moving the BL to the database is bad, but not for everything. Try taking a look at Domain Driven Design. This is the antidote to massive numbers of SPROCs. I think you should be using your database as somewhere to store you business objects, nothing more.

However, SPROCs can be much more efficient on certain, simple functions. For instance, you might want to increase the salary to every employee in your database by a fixed percentage. This is quicker to do via a SPROC than getting all the employees from the db, updating them and then saving them back.

Answer 3

I worked in a project where every thing is literally done in database level. We wrote lot of stored procedures and did lot of business validation / logic in the database. Most of the times it became a big overhead for us to debug.

The advantages I felt may be

• Take advantage of full DB features.
• Database intense activities like lot of insertion/updation can be better done in DB level. Call an SP and let it do all the work instead of hitting DB several times.
• New DB servers can accommodate complex operations so they no longer see this as a bottleneck. Oh yeah, we used Oracle.

Looking at it now, I think few things could have been better done at application level and lesser at DB level.

Answer 4

Network between appServer and sqlServer is the bottle neck very often. Stored procedures are needed when you need to do complex query. For example you want collect some data about employee by his surname. Especially imagine, that data in DB looks like some kind of tree - you have 3 records about this employee in table A. You have 10 records in table B for each record in table A. You have 100 records in table C for each record in table B. And you want to get only special 5 records from table C about that employee. Without stored procedures you will get a lot of queries traffic between appServer and sqlServer, and a lot of code in appServer. With stored procedure which accepts employee surname, fetches those 5 records and returns them to appServer you 1) decrease traffic by hundreds times, 2) greatly simplify appServer code.

Answer 5

Basically, the benefit is #2 of your problem list - if you do a lot of processing in your database backend, then it's handled there and doesn't depend on the application accessing the database.

Sure - if your application does all the right things in its business logic layer, things will be fine. But as soon as a second and a third application need to connect to your database, suddenly they too have to make sure to respect all the business rules etc. - or they might not.

Putting your business rules and business logic in the database ensures that no matter how an app, a script, a manager with Excel accesses your database, your business rules will be enforced and your data integrity will be protected.

That's the main reason to have stored procs instead of code-based BLL.

Also, using Views for read and Stored Procs for update/insert, the DBA can remove any direct permissions on the underlying tables. Your users do no longer need to have all the rights on the tables, and thus, your data in your tables is better protected from unadvertent or malicious changes.

Using a stored proc approach also gives you the ability to monitor and audit database access through the stored procs - no one will be able to claim they didn't alter that data - you can easily prove it.

So all in all: the more business critical your data, the more protection layer you want to build around it. That's what using stored procs is for - and they don't need to be complex, either - and most of those stored procs can be generated based on table structure using code generation, so it's not a big typing effort, either.

Answer 6

Don't fear the DB.

Let's also not confuse business logic with data logic which has its rightful place at the DB.

Good systems designers will encompass flexible business logic through data logic, i.e. abstract business rule definitions which can be driven by the (non)existence or in attributes of data rows.

Just FYI, the most successful and scalable "enterprise/commercial" software implementations with which I have worked put all projection queries into views and all data management either into DB procedures or triggers on staged tables.

Answer 7

It depends almost entirely on the context.

Doing work on the server rather than on the clients is generally a bad idea as it makes your server less scalable. However, you have to balance this against the expected workload (if you know you will only have 100 users in a closed enironment, you may not need a scalable server) and network traffic costs (if you have to read a lot of data to apply calculations/processes to, then it can be cheaper/faster overall to run those calculations on the server and only send the results over the net).

Also, if you have custom client applications (as opposed to web browsers etc) it makes it very easy to push updates out to your clients, because you don't need to recompile and deploy the client code, you simply upgrade the database stored procedures.

Of course, using stored procedures rather than executing dynamically compiled SQL statements can be more efficient (it's precompiled, and the code doesn't need to be uploaded to the server) and aids encapsulation to give the database better integrity/security. But by the sound of it, you're talking about masses of busines logic, not simple efficiency and security measures.

As with most things, a sensible compromise/balance is needed. Stored Procedures should be used enough to enhance efficiency and security, but you don't want your server to become unscalable.

Answer 8

It seems like your question is based on the notion that three-tier is "the way". Philip Greenspun wrote a fairly scathing critic of the benefit of the three-tier architecture. While some of his arguments are a bit dated, they still hold some truth, even today.

Answer 9

The life time of our data exceeds that of our applications. Also data gets shared between applications. So many applications will insert data into the database, many applications will retrieve data from it. The database is responsible for the completeness, integrity and correctness of the data. Therefore it needs to have the authority to enforce the business rules relating to the data.

Taking you specific points:

1. Transactions are Units Of Work. I fail to see why implementing transactions in stored procedures should change their granularity.
2. Business logic which applies to the data belongs with the data: that maximises cohesion.
3. It is hard to write good SQL and to learn to think in sets. Therefore it may appear that the database is the bottleneck. In fact, if we are undertaking lots of work which relates to the data the database is probably the most efficient place to do.

As for maintenance: if we are familiar with PL/SQL, T-SQL, etc maintenance is easier than it might appear from the outside. But I concede that tool support for things like refactoring lags behind that of other languages.

Answer 10

"there are following disadvantages on this approach: ... Business logic goes into database."

Insofar as by "busines logic" you mean "enforcement of business rules", the DBMS is EXACTLY where "business logic" belongs.