Business Logic & SQL  
Author Message

PostPosted: Architecture General, Business Logic & SQL Top

Hi, has anyone in here got some guidance or direction for Patterns and Practices on this one

When should business logic be put into a SQL Stored procedure and when should it be put into Class object we are debating different architectural approaches and I am looking for some guidance.

Referenced post: ;SiteID=1


PostPosted: Architecture General, Business Logic & SQL Top

Depends on a lot of things, I guess.

The general rule of thumb I use in most of my projects is that if all the work done by the business logic can be done in the database, and the work is not computation-heavy, then do it in the database and save the overhead of the roundtrip.

If the work is computation heavy, or needs to do things that are tough in SQL, or may be needed by other business objects at a later date, then do it in the objects.

But, again, these are *very* general rules of thumb. You have to consider things like maintainability, version control, deployment scenarios, etc.


PostPosted: Architecture General, Business Logic & SQL Top

Another rule of thumb: If you decide to design the persistant elements as classes and then define the database, you are probably better with classes having the logic and vice versa.

Why you want to leave data access with the team which designed the model ;-)

Anyway, there is no one answer. Using stored procedures optimizes data access ( if used wisely) and gives some other benefits like having compiled resultsets.

Using objects reduces your dependance on the database, and lets you have only C# or whatever skills in the team while the DB architect could just spend a few weeks designing and optimizing the database. If you are convinced on using OR mapping, you will have the logic in objects anyway.

Javier Luna

PostPosted: Architecture General, Business Logic & SQL Top

Hi pranshu,

Never put business logic into SQL stored procedures. I had developed a Business Logic Layer.

BusinessLayer.Components released, too...!!!

Good coding!

Javier Luna


PostPosted: Architecture General, Business Logic & SQL Top

Never put business logic into SQL stored procedures.

I have to disagree. There are many times where putting business logic in stored procedures is a good decision.

The typical arguments against this are often valid as well, but not always. Some common arguments are:

  • It prevents easy migration to another database platform.

This is true, but if your application is never likely to be ported to another database platform, it's not an issue. This is the same argument many people used to bash .NET when compared with Java, because Java is "write once, run anywhere" and, for the most part, .NET is Windows only. Considering the most popular place for Java code right now is on the server, and platform on which large scale applications run rarely changes for a wide variety of other reasons, this argument was all the more silly. If portability is an issue it's usually one that’s known about at the start of the project.

  • The DB server is a precious resource and as little as possible should be done there.

Agreed. The DB server is often the most expensive part of a solution. But sometimes business logic in the database will actually *reduce* load on the server. I've encountered many scenarios in which the multiple roundtrips involved in doing business logic in code instead of in a sproc results in *more* load on the DB server than if I had just done it in a sproc.

In addition, sometimes the business logic just isn't very intensive and the time and effort to do that logic in business objects just isn't justifiable.

  • Business logic in sprocs is hard to version, hard to maintain, hard to... etc.

Sometimes yes, sometimes no. It's on a case by case basis. It depends on how you do DB development, as well. Do you have a dedicated DBA How do you version the rest of the DB In general, I've found these issues to be straw man arguments.

The fact that Microsoft added CLR support directly into SQL Server 2005 suggests that they think that business logic in the database isn't always a bad thing, either.

Architecture is often about compromises. You can write your applications to be "pure", but you'll often find that your no-compromises attitude results in poor performance, long development time, and very little bang for your buck.

It seems to me that you're post is more about pushing your BLL product than actually giving this guy advice. You've repeated this exact same post in several other threads, apparently without regard to their particular situation.

Diego Dagum

PostPosted: Architecture General, Business Logic & SQL Top

It seems to be a consensus about taking advantage of the strenghts and weaknesses of, both, OO paradigm and RDBMS

Very intensive computing activity would be better carried out by classes' methods than stored procedures, while intensive record-oriented activities fit better in stored procedures

Why In the case of computing activity, because OO paradigm helps (without warrantying it) to achieve better component reuse and flexibility through interface-based programming, dependency injection and object composition, among other facilities still not present -or partially present- in databases

Why intensive data access better in stored procedures Because we pay a performance penalty every time we make a round trip to the database, so by putting such logic all together in a stored procedures, we are applying coarse-grained granularity (a component design pattern). Maarten Mullender talks about fine-grained problems in his "CRUD, Only When You Can Afford It"

I particularly model behaviour in classes more associated with use cases than data; I also model entities as an OO representation of the database (so, I mean I model the database schema first) and usually get and store data by interchanging XML messages with the database (my RDBMS handles XML pretty well). Thus, I can store a graph of objects (for example, a purchase order with its items) in just one round trip

A good place to find info about Data Access is the MSDN Solution Architecture Centre


PostPosted: Architecture General, Business Logic & SQL Top

Well Thanks for the support RMD.

In my experience, I tend to avoid SP, till performance becomes an issue as there is a slight shortage of developers who know SP well. And also, logging/tracing gets distributed to multiple places ( We cant use entlib from Stored procedures AFAIK)

Typical instance where performance will force SP is if a page requires execution of 100s of SQL queries, OR if SQL where clause cannot be used to filter results.

Cases where performanc will force app layer logic is - if a cartesian join is required -lets say for a report. Then app layer can process records one by one instead of making the large cartesian join.

There are some other cases where we have used Stored procedure as well. An application required dynamic change in some part of business logic - 2-3 times a day. I felt it better to make that a SP and use alter statements, Rather than distributing DLLs and doing IISREST to load them. Now - if designing the same again - I  probably would go for a rule engine instead.

Another case was two applications (one of them legacy) interfacing by sharing the same DB tables. In that case, using SP to provide "interfaces" (if you will) to the new application made the development shielded from maintenance changes in Legacy applications.



PostPosted: Architecture General, Business Logic & SQL Top

Thanks all of you for your Posts to date. I will leave this one open for a while.