7

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?

16

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.

  • 1
    Good points. It is also easier use source code versioning e.g. git on the middle tier than on the SP. – topher Jun 15 '16 at 13:25
  • 1
    I would add that it's much easier to debug something in the application layer vs a stored proc in the db layer. – nick Jun 15 '16 at 13:36
  • Besides what @nick said about debugging, I would also add that logic becomes very unwieldy in SQL. It's more difficult to break logic into smaller functions and scope those appropriately. Also, your only real data structure is a table, which can be limiting. – Kris Harper Jun 15 '16 at 14:15
  • @topher I'd would be neutral on the source control point. I've always had my teams hold executables (SP, functions, triggers) in source control and deploy them like any other code. Of course anyone with access can change an SP outside of the release process, but the same is true for application code too. – Michael Green Jun 16 '16 at 0:42
  • @Nick True. While SQL Server Management Studio has a step-through debugger, procedural language statements are much more granular than SQL so it's easier to isolate individual problematic values. On the flip side, many more procedural statements have to be stepped through to cover the equivalent functionality so there's more opportunity for confusion or side effects. – Michael Green Jun 16 '16 at 0:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.