Business logic in stored procedures and performance RRS feed

  • Question

  • I would like to discuss two design decisions related to a business application with complex business logic:

    1 - Implementing the business logic in stored procedures.
    2 - Implementing the business logic in a specific layer (n - tier design).

    in my opinion, implementing business logic in a separate layer is better in terms of maintainability. The business logic layer would call data access layer (ADO.NEt and stored procedures) , get data and then perform business logic to format or get new results from these data and then passes them to the presentation layer. also, the debugging of stored procedures is difficult compared to the debugging of C# code, and the readability of stored procedures are much worse too. In this design choice stored procedures would contain data access operations only, performing select, update,delete,insert, join and such similar operations.

    This allows for separation of responsibilities too, where every layer has a specific role allowing much more code readability and maintainability.

    The only possible advantage of implementing business logic in stored procedures is getting better performance. This is the point that I need to discuss: How much could the performance of stored procedures be better than C# code?  in terms of performance, is it better to loop on the rows of a data table to calculate a result that is based on a business logic rule or to calculate this result in a stored procedure using a cursor?

    Sunday, May 31, 2009 9:21 AM

All replies

  • It all depends on your team's experience and your project's requirements. Bring in a DBA to your team and decide what should you do to meet the requirements.

    MSMVP VC++
    Sunday, May 31, 2009 7:05 PM
  • Dear Muhammad,

    As Sheng told you it is all depend.

    if you place the B.L. in SP, this will help you much more in preformace but at the same time, it will stuck you in one database, and if you try to migrate your application to DB this is will be ____.

    it is all depend on what is the bussiness requirment and your vision to the future.
    Mahmoud Ghoz
    Monday, June 1, 2009 9:24 AM

  • Hello Muhammad,

    Here is what I think-

    1.       Having business logic in database tied your application to particular database. Supporting the other database will be a nightmare.

    2.       During development you might take help of DBA but during support phase it might not be as easily available.

    I agree that moving of business logic to store procedure might improve the performance but I’d not prefer get such performance at the cost of flexibility and maintainability.

    Usually I use stored procedure, views to give consistent view of database to my application. Some time these procedures have database specific syntax to improve the performance but I never put business logic inside them. Such procedures can be easily migrated to other database.

    And always be cautious while approaching DBA. In most cases (I don’t know if there are exception) they start from table and normalization but we start from objects.


    Monday, June 1, 2009 4:58 PM
  • It would be best to create a rich domain model that encapsualtes the rules within the problem domain.  This will result in a more flexible solution in the future and will increase your productivity in the future.

    Stored procedures should be used for data access rather than to encapsulate knowledge about the domain.  I've seen the aftermath of projects that have placed logic in stored procedures only to find they are unmanageable as the complexity of them grows.  Moving this logic out in years to come will be a major undertaking.

    In terms of performance, looping round a recordset can be slow - and you certainly don't want to issue other SQL statements in this loop as this won't scale well.  However, if you consider what data you need carefully and how to get it you should be able to get the performance you want without needing to place business logic in the database.

    Hope this helps.

    Pl mark as answer or helpful if you found this useful
    Tuesday, June 2, 2009 9:29 AM
  • Middle tier languages like C# are much more powerful in processing business rules then the stored procedures. Also if you need to connect to other systems for implementing business rules, middle tier is better option than sp.



    Tuesday, June 2, 2009 11:23 AM
  • Thanks for all the replies here.

    I have modified one of the reports whose business logic was implemented in a stored procedure to implement the business logic in business logic layer (C# Code). I took this report as a test to see the difference between the two solutions.The business logic had a lot of looping which was done with cursors.

    My code is not the best possible code. I have implemented code in which multiple trips are made to the database . I could have brought all records in data tables and performed business logic (which included multiple data tables) on them, but I have preferred the first solution for the sake of code readability.

    In spite of this, The time needed to generate the report was less than half that of the old report! As I searched in architecture books and articles online, I found many people saying what some of you said here : Performance of cursors are exceptionally bad in SQL server. SQL is a language that suits bulk data operations, not per record operations.

    Of course the code readability and maintainability is much better than that of the stored procedure.
    Sunday, June 7, 2009 8:25 AM
  • Implementing Business Logic in stored procedure give you better performance, but its better to implement in separate layer, in case of having more trips to database get them in a single trip and store it in an in memory dataset and continue further processing. Always use repository pattern and implement Inversion of Control and Dependancy Injection.


    You can have your BL in SP's in case of small projects. but make sure you do not use cursors. and if you prefer better transactions use SP's

    Friday, July 23, 2010 10:02 AM