Performance Vs Principles , how much business logic should be there in the Stored Procedures

Question

I always have debate with my Architect / DB team , on having a hybrid model to move partial business logic into the SP, like give the results based on few flags like Status , Date and logged-in USER ID ROLE( might also have some basic business logic) for a huge volume of DATA. If we can do this in SP then we could improve the performance.

Now using LINQ its been a bit easier, but I still feel that if we can take some exceptions from the principle "Business logic lives in one place" , we easily can have some performance boost.

I would like to know what do you think on this, and are there any best practices followed to decide on this.

Answer 1

In my opinion, Stored Procedures should contain as little business logic as possible. But, it is sometimes necessary. Its a really a case by case basis, depending on the system being built and its architecture. My personal preference is to keep as much of the business logic in one place as possible, i.e. in the business layer. But your database should still contain constraints, keys, etc. That is business logic that does belong in the database.

I inherited a system where most of the business logic was in stored procedures and dare I say triggers! This ultimately led to poor performance and much code duplication. My task was to move the business logic out of the stored procedures and triggers. Performance improved from that point on.

Lesson learned first hand here.

Answer 2

imho, this depends on the team. In a team where you have good or above average SQL stored procedure development skills, there is nothing wrong with putting Business Logic in the DB. In fact, if done well, in an organized and structured mannner, there is much to be gained.

Any logic that involves multiple resources, of course, should remain in a code logic layer, but if a process strictly involves resources from one database, on one server, then encapsulating it in a stored procedure saves round trips, isolates the logic in one place where it can be more easily managed, (instead of having it duplicated in each app that uses it) and allows changes to the logic and/or bug fixes to be deployed without a recompile. This is especially true if the only destination for the results of the process is in the database itself (i.e., the output simply needs to be persisted into the same database the inputs came from).

Also, doing this can help mitigate the effects of small schema changes on the code base, as the stored procedures can be designed to "include" a well-defined interface between the underlying schema and the class structure of the code base, in effect implementing the "facade" pattern. In this way changes in schema may only require changes in the stored proc (the mapping portion), without changing the publicly visible interface consumed by external code.

Also, being open to placing some logic in stored procedures makes it substantially easier to actually implement a physical schema (model) that is NOT exactly the same as the logical model that came out of your business domain model analysis. Too often, this step is ignored or skipped, with detrimental results.

As to performance, anything done (with equal skill) in the database is guaranteed to be faster than if the data has to be transferred to a logic layer on an application server, and then transferred back to the database to be persisted, after processing.

Finally, although it almost always goes without saying, any time you hear folks spout a "principle" using words like "Always" or "Never", be afraid, be very very afraid...

Answer 3

It's a lot easier to adhere to simple, absolute rules. Whether the rule "don't put any business logic in stored procedures" is appropriate for your case, only you and your team can determine.

When you consider breaking that rule, do it both ways and profile it, so you know, quantitatively, what the benefit is to breaking the rule. That can make the decision much easier, on a case-by-case basis; you could even establish minimum performance improvement thresholds - that's not as simple and absolute as "don't do it", but it's better than no standard at all.

Answer 4

In single-purpose database, that serves data to a single application with well defined set of functions, it's perfectly valid to have BL in the DB. In typical enterprise scenarios though when the database is a shared pot (or should I say, shared needle?), keeping your BL isolated and nicely packaged inside each respective app or service keeps the pain minimized (both in terms of change management and in terms of scalability). IMHO. YMMV.

Answer 5

I'd say that this is something which applies across the board to a lot more than just database access, however the effects are most prominent in the database tier because this is generally where by far the most sophisticated optimisations take place.

As someone who is comfortable with SQL and database performance optimisations I probably put more "business logic" into the database than most, although I dont really see it as bussiness logic as such. I see it more as the contract that my DAL has with the database - generally speaking a lot of things that you might refer to as a business rule can also be expressed simply in terms of the data present. As a really simple example, where some people might say "I want to get all customers who are 'active'", someone else might say "I want to select all customer records that have is_deleted false" (this is obviously a trivial example).

Also the common philosophy of late optimisation also applies here. If your DAL is well designed then it should be possible to simply tweak your DAL layer to optimise as and when needed, putting more logic into stored procedures if needed.

Answer 6

There are some trade-offs here... most important ones (in order) are:

• It is usually much easier to scale non-DB layers (app server or web server), by adding more processes/threads or more hardware. So it is usually preferable to off-load any CPU intensive processing outside the database if your scale is large enough where that comes into play (though YMMV depending on exact business scenarios/architecture).

  IMHO this is the first/main consideration in your decision - all of the other considerations (e.g. change control by having only 1 copy of code implementing said business logic) can be dealt with other ways - e.g., app servers or well-designed libraries.

  Scaling a DB server is a lot more expensive and technically challenging than solving any other problems.

• The other side of trade-off is network capacity - if the result of applying "business logic" will be to return 2KB of data instead of 30MB, it might be worth doing some crunching on the database side. Again, YMMV - without exact scenario, hard to say how relevant this trade-off is, though it is rare that "business logic" would result in such drastic scale of savings in result set size.

• As a prior answer noted, carefully coded Db-side business logic can in some cases increase performance - so if the scalability of DB server is not an issue, this is a valid argument for doing it on DB side.

• The "you can utilize superior Stored Proc development skills" argument on DB side that I seem to infer from the very first answer is, IMHO, a red herring. Anyone good enough to have truly superior DB side skills is more likely than not an above average coder outside of DB as well.

Hope this helps somewhat.