Which is better, filtering results at db or application?

Question

A simple question. There are cases when I fetch the data and then process it in my BLL. But I realized that the same processing/filtering can be done in my stored procedure and the filtered results returned to BLL.

Which is better, processing at DB or processing in BLL? And Why?

consider the scenario, I want to check whether a product exists in my db and if it exists add that to the order (Example taken from answer by Nour Sabony below)now i can do this checking at my BLL or I an do this at the stored procedure as well. If I combine things to one procedure, i reduce the whole operation to one db call. Is that better?

Answer 1

at the database.

Answer 2

At the database level.

Databases should be optimized for querying, so why go through all the effort to return a large dataset, and then do filtering in your application afterwards?

Answer 3

As a general rule: Anything that reasonably belongs to the db's responsibilities and can be done at your db, do it at your db.

Answer 4

I have consistently been taught that data should be handled in the data layer whenever possible. Filtering data is what a DB is specifically there to do and is optimized to do. Therefore that is where it should occur.

This also prevents the duplication of effort. If the data filtering occurs in a data layer then other code/systems could benefit from the same stored procedure instead of duplicating the work in your BLL.

I have found that people like to put the primary logic in the section that they are most comfortable working in. Regardless of a developer's strengths, data processing should go in the data layer whenever possible.

Answer 5

I asked a similar question in a SSRS class earlier this year when I wanted to know "best practice" for having a stored procedure retrieve queried data instead of executing TSQL in the report's data set. The response I received was: Allow the database engine to do the "heavy lifting." That is, do all the selecting, grouping, sorting in the stored procedure and allow SSRS to concentrate on delivering the results.

The same is true for your scenario. By all means, my vote would be to do all the filtering in your stored procedure. Call the sproc from your application and let the database do the work.

Answer 6

If you filter at the database, then only the data that is required by the application is actually sent on the wire between the database and the application server. For this reason, I suggest that it is better to filter at the database.

Answer 7

To throw a differing view point out there it also really depends upon your data abstraction model if you have a large 2nd level cache that sits on top of your database and the majority (or entirety) of your data you will be filtering on is in the cache then yes stay inside the application and use the cached data.

Answer 8

well, the fatest answer is at database, but you may consider something like Linq2Sql, I mean to write an expression at the presentation layer, and it will be parsed as Sql Statement at Data Access Layer.

of course there are some situation BLL should get some data from DAL ,process it ,and then return it to DAL. take an example : PutOrder(Order value) procedure , which should check for the availability of the ordered product.

public void PutOrder(Order _order)
{
foreach (OrderDetail _orderDetail in _order.Details)
   {
    int count = dalOrder.GetProductCount(_orderDetail.Product.ProductID);
    if (count == 0)
    throw new Exception (string.Format("Product {0} is not available",_orderDetail.Product.Name));
    }
    dalOrder.PutOrder(_order);
}

but if you are making a Browse view, it is not a good idea (from a performance viewpoint) to bring all the data from Dal and then choose what to display in the Browse view.

may be the following could help:

public List<Product> SearchProduts(Criteria _criteria)
{
string sql = Parser.Parse(_criteria);
///code to pass the sql statement to Database procedure and get the corresponding data.
}

Answer 9

The difference is not very important when you consider tables that will never contain more than a few dozen rows.

Filtering at the db becomes the obvious choice when you consider tables that will grow to thousands, hundreds of thousands or millions of rows. It would make zero sense to transfer that much data to filter for a subset of records.