Business logic in stored proc vs middle layer (c#)

Question

This is more of an architecture question. I once worked at a financial company that had a web application.

Front end javascript.
Middle layer was WebAPI to access the backend SQL Server.
Back End SQL Server database.

The company put all its business logic in stored procedures. After talking with other companies, I began to hear that most put their business logic in the middle layer.

I once asked someone at the financial company why was there such a big use of stored procs but didn't get a good answer. Recently, at a job interview, they asked what the advantages and disadvantages were of putting all the business logic in the database vs the middle layer. I didn't have a good answer.

Can anyone provide some thoughts on why this is or is not a good idea?

Answer 1

A) Scale The middle tier can be scaled easily - hence the web farm concept. Scaling out the DB tier is much more difficult. While some products can do this it is not yet trivial and mainstream.

B) Cost Typically web servers are common-or-garden boxes. DB servers, however, tend to be larger, more complex and more resilient. This all translates to "expensive." A recent employer estimated a CPU tick on the DB was ten times more expensive than one on an application server.

C) Reuse Logic embodied in a stored procedure cannot simply be linked into, say, a stand-alone mobile app. Changing the SP affects every application which uses that DB, whether that application is ready for the change or not.

D) Reuse Logic embodied in a stored procedure is common to all applications which use the DB. Programmers cannot side-step the rules at a whim. Changing the SP affects every application which uses that DB, ensuring consistency across the enterprise.

E) Tooling There are more, and more sophisticated, languages, tools and techniques available for development in the application tier than there are in the database (from comments, with thanks).

F) Network traffic Typically a business function will require many reads and / or writes. Often the output of one statement will be the input to a following statement. If SQL statements are held in the application each will require a network round-trip to the server. If the SQL is held in a stored procedure there will be a single network trip, sending the parameters in and receiving only the final result back; the network cost of the intermediate results is avoided.

Answer 2

After seeing Michael Green's answer, which is full of great points, I wanted to provide some additional thoughts to weigh against his points. I'm not trying to win the argument for the side of using stored procs, per se, but I do believe they are often misunderstood and/or misrepresented in these kinds of conversations.

A) Scale - SQL statements have to be run whether you store them in Stored Procs or in a middle tier. So CRUD operations aren't going to scale any better which is probably 99% of what we're talking about here. But for real business logic that isn't just simple CRUD, yes you should generally not put that in the database unless you have a good reason to do so. And there are good reasons at times, such as to absolutely prevent bad data or for additional security.

B) Cost - I can't argue with this one in theory, but I can argue the hell out of it in practice. One should not assume that moving code from the SQL Server to a middle-tier necessarily equates to offloading work off the SQL Server. Often developers will just work with what they have to get their job done. If they need to get 20 records for a page and all they have access to is a method that gives them 1 record at a time, they will probably just use that and let it call the database 20 times before they'll think to say "hey, can I get a new method to get all the records I need in 1 go?" That's just a very simple example to get the point across, but this can show up in many ways. Different teams give their developers different levels of access across the stack so I undestand you may or may not relate to that specific example for your team; hopefully you get the point though. Having SQL in Stored Procs also opens the door to letting DBAs do what they do best to help you get the most performance out of your database. Putting it all in a middle tier closes that door and now you're more often relying on developers, who are rarely SQL experts, to tune your queries.

C) Reuse - I have to say I don't understand the point of the statement "Logic embodied in a stored procedure cannot simply be linked into, say, a stand-alone mobile app". If the logic is in a stored procedure, then there is no need to do so; so it sounds like this is saying "you can't do this thing with stored procedures that you don't need to do with stored procedures". Granted, I may be (probably am) grossly misunderstanding the use case scenario here so I welcome comments to explain because if I don't get it then probably many others don't either.

D) "Changing the SP affects every application which uses that DB, whether that application is ready for the change or not." If your middle tier is accessed through a service then this issue is not truly mitigated; you've just moved the same issue to your middle tier. However, if your middle tier is distributed (i.e. you copy a dll into each consuming project to use) then you can put off updating individual projects... and that opens up a whole 'nother can of worms. Like if you need to do a hotfix on an application that hasn't been updated now either you have to get an older version of code to make that fix to and manage a separate branch of code or you have to bite the bullet and update the application to work with the latest middle-tier code. Of course this where a middle-tier proponent would offer a solution where he has, wait for it..., multiple versions of a method! And now you're back to square one. I'm not saying one way is better or worse than another. I'm just saying that there are issues to solve either way you do it and you should consider what will work best for YOUR specific environment.

E) Tooling - "There are more, and more sophisticated, languages, tools and techniques available for development in the application tier than there are in the database (from comments, with thanks)." The flip side is that there are very sophisticated tools and talented individuals who can tune your database when they have access to the stored procs. They can find bottle necks and fix them, but that job because a heck of a lot harder when the sql is in the middle-tier and the SQL expert can't fix the the code your developer wrote which is generating crappy SQL. So again, it's a trade off. On this one, my opinion is to consider who is on your team and let what they are most comfortable with be prioritized highly when making a decision because it can DRASTICALLY affect development time which for you may outweigh the cost of your server.