Business Layer vs SQL Server

Question

I have an application that does complex calculations for members. Each member can have multiple US states linked to their profile. Each state has got different calculations for each course a member completes.

As of now I have been performing the calculations in the DB (SQL Server 2008) and then sending data back to app layer where they can see their history and then download a certificate for each course.

I have a business logic layer but not a lot happens there. I know this has been asked a lot but where do you think I should perform these calculations: business layer or database? I am going back and forth!!

Careful

I would always do calculations in the database, if your calculation would need to return a lot of data to the middle tier, just to have e.g. a SUM calculated. Why bother sending all that data around?? The database is well equipped to sum up some values, and just send back a single result - much more efficient! So whenever you need to do a lot - but return little - do it on the server. – Thacker

I agree with @Thacker SQL Server is designed to process data quickly and more efficiently. – Homoio

Answer 1

I would basically do anything in SQL Server that:

• does a lot of summing, counting, averaging etc. of data and returns only a single value. There's really no point in transferring large volumes of data to the middle tier, just to sum up a column

• does a lot of row / set-based manipulation; if you need to copy, insert, update lots of data, again, there's really no point in dragging all that data to the middle tier and then sending it all back to the server - do it on the server right from the get go. Also: T-SQL is significantly faster in dealing with set-based operations (like shuffling data around) than anything in your middle tier could be

In brief: try to avoid sending large volumes of data to the client/middle-tier/business layer unless you absolutely have to (e.g. because you want to download a file stored in a database, or if you really need to have those 200 rows materialized into objects in your app to be displayed somewhere or operated on)

One feature that's often overlooked are computed columns right in your database table - those are really good at e.g. summing up your order total plus tax and shipping into a grand total, or they are great to put together your first and last name into a display name. Those kinds of things really shouldn't be handled only in the business logic layer - if you do those in the database directly, those "computed" values are also available to you when you inspect the database tables and look at the data in SQL Server Mgmt Studio ...

---

I would put it into the middle tier / business logic layer

• if it required extensive logic checking, string parsing, pattern matching and so forth; T-SQL sucks at those things

• if it required things like calling a web service to get some data to validate your data against, or something like that

• if it required more of a business logic operation (rather than strict "atomic" database operations)

But those are just "rough" guidelines - it's always a design decision for each case, and I don't believe in strict rules; your mileage may vary, from case to case - so pick the one approach that works best for any given task at hand.

Thacker

Answer 2

It helps to not have business logic code inside of the database (stored procedures). It is much better to have it directly in the application so it fits right in the architecture. This SQL code contains your business logic and there is nothing wrong with it. (There is nothing wrong with having data or maintenance related code in sprocs though).

If your business logic layer is not doing much and is just passing the data from SQL Server to the caller, maybe you don't need it at all.

Blunderbuss

Stored procedures can be very helpful - as long as they handle database-operations and don't "hide" business logic. But for lots of operations, having a stored procedure can be very useful. I wouldn't just globally "ban" them – Thacker

Thanks guys, Basically once I capture the data as to what course was attended I send back a report to the member for each state they are registered with along with all their credits. I believe the DB is the correct place to perform these calculations. It was a pure vb.net/c# guy that got me wondering if I was doing this correct. Of course he maintains it should all be done at BLL. Each situation is different. – Careful

Don't strictly apply "best-practices" like BLL and DAL without common sense. Have them in your toolbox and use them when you see fit. – Blunderbuss

Answer 3

The business logic layer is not there to do the heavy lifting, it's purpose is to provide an abstraction with entities in the language of the subject matter. Thus the business layer can be used to provide as shared and consistent approach across for any layers/applications that are required to work in that space.

To over engineer things to make a point, the ultimate goal would be that the business logic layer is used across an organisation for all applications working in that subject space. I.e. wrapped in a service etc.

In the real world of small apps to do this or that, the business logic layer does feel like an appendix some times. The trick is to also remember that any uses cases should be implemented as tests against the business layer, giving you another way to think of how it's public interface should look.

How the business logic layer gets it's work done should be hidden from those parts of the application calling it.

Thus it is perfectly acceptable to calculate the data in the most efficient manner (i.e. sql), so long as these calculations are given an appropriate representation in the business logic layer.

Nerves