53

In some organization I worked for web applications are developed basing all the business logic in Database stored procedures. For example, use html for view and servlet as controllers to divert the client request to appropriate Database stored procedures.

What are the advantages and disadvantages of this kind of designs? In my opinion if the business logic highly depends on the database than better follow this kind of design!!!!!

1
  • 1
    While there is noting wrong with your question, the term "Business Logic" has different meanings. For example, 1-M mandatory relationship is a "business logic" that must be checked on the server, whereas missing key value is also a business logic that should be checked on the GUI (at least).
    – NoChance
    Jul 28, 2012 at 16:50

6 Answers 6

71

In theory, the pros and cons are as so:

Pros:

  1. One place to contain all of the business logic
  2. Possibly faster applications because multiple SQL queries and such can be performed in one "round trip" to the database
  3. Trivial to make use of the stored procedures from multiple applications

Cons:

  1. A DBA will be required for performance tuning
  2. All developers will have to be very well versed in your particular SQL dialect(T-SQL, Pl/SQL, etc)
  3. SQL code isn't as expressive and thus harder to write when covering higher level concepts that aren't really related to data
  4. A lot more unnecessary load on the database

Now, practically, only a fool would have all business logic in the database.

  1. Very few developers will be able to create a consistent stored procedure interface that works easily across applications. Usually this is because certain assumptions are made of that calling application
  2. Same goes for documenting all of those stored procedures
  3. Database servers are generally bottlenecked enough as it is. Putting the unnecessary load on them just further narrows this bottleneck. Intricate load balancing and beefy hardware will be required for anything with a decent amount of traffic
  4. SQL is just barely a programming language. I once had the pleasure of maintaining a scripting engine written as a T-SQL stored procedure. It was slow, nearly impossible to understand, and took days to implement what would have been a trivial extension in most languages
  5. What happens when you have a client that needs their database to run a different SQL server? You'll basically have to start from scratch -- You're very tied to your database. Same goes for when Microsoft decides to deprecate a few functions you use a couple hundred times across your stored procedures
  6. Source control is extremely difficult to do properly with stored procedures, more so when you have a lot of them
  7. Databases are hard to keep in sync. What about when you have a conflict of some sort between 2 developers that are working in the database at the same time? They'll be overwriting each others code not really aware of it, depending on your "development database" setup
  8. The tools are definitely less easy to work with, no matter which database engine you use.

So, to objectively answer the question. In most cases, stored procedures are only needed in some cases. For instance, if you have a report to generate where you need to do a lot of conditional processing across a couple of big tables, you wouldn't want your application to be making a couple hundred SQL queries to the database. You'd want to make a stored procedure so you didn't have the network lag overhead. And, you'll usually only want to make stored procedures otherwise for when clients want to run a custom-ish query across your database. Stored procedures and views can make this easily possible without your clients being a database whiz.

20
  • 13
    mostly right, but 3) a SP often reduces load as it turns a handful of crap client queries into a single optimised query. 6) total nonsense. 7) happens with normal source code. 8) you'd better ask a DBA if his tools are not up to the task - Toad for example was very good. Still, if you are going to have a DB-based data tier, then hire dedicated SQL experts to create it, just like you'd hire dedicated javascript devs for a web GUI. You'd still only write data-related code in SQL though, it doesn't replace the entire stack.
    – gbjbaanb
    Jul 28, 2012 at 16:19
  • 13
    -1 disagree with "Now, practically, only a fool would have all business logic in the database" I'm sorry this is just plain bias. "Stored Proc vs App Code" is an aged old debate, your answer is CLEARLY biased.
    – Darknight
    Jul 28, 2012 at 18:16
  • 5
    -1: One place to contain all of the business logic - It's unlikely that you'll be able to accomplish this with modern-day applications.
    – Jim G.
    Jul 28, 2012 at 18:50
  • 1
    Keeping in the database is ideal for most applications but it all boils down to a single point... "1. A DBA is required... " I've been consulting and re-architecting applications for several years now and this is the main deciding factor. If this resource is not readily available, stay away from the database. Also keep in mind databases handle set operations much better than the application. Application code tends to be very iterative and can be very slow. I've once tuned an app code that ran for 7 hours... once converted to a store proc... 2 minutes.
    – sam yi
    Nov 19, 2013 at 16:09
  • 2
    @Nick.McDermaid which ones? Yes, I'm a coder, not a DBA. So, I'm going to be a bit skewed, but I think all my points are valid.
    – Earlz
    Sep 22, 2014 at 14:35
21

Cons:

  • The database is the least scalable part of your architecture.
  • Especially in an age of web services and REST-ful 3rd party web applications, you won't be able to contain all of your business logic in stored procs.
  • SQL implementations vary, some can be tricky, and none read as fluidly as a first-class application-layer programming language.
  • Remember that code is read much more often than it is written.
2
  • 1
    +1 for scalability. While most DBs are incredibly fast, and so handle a large load, they are also incredibly expensive to scale (SQLServer, Oracle, and DB2 anyway).
    – rally25rs
    Jul 28, 2012 at 22:53
  • 5
    +1 for Remember that code is read much more often than it is written
    – Ajay Bhasy
    Feb 22, 2018 at 6:16
10

Pros of holding all the business logic on stored procedures in web application for:

  • One place is easier to maintain than multiple
  • Databases are fast and optimized and can be scaled well.
  • The same procedures can be called from mutiple, different frameworks and languages.
  • The logic is decoupled from the implementation in particular applications.
  • Rework may be reduced for changing applications when the database stays the same.

Cons of holding all the business logic on stored procedures in web application: against:

  • Good SQL knowledge can be hard to find in many locations.
  • Good SQL coders can be expensive.
  • All application developers will need to be able to either change it or request a quick change.
  • Some data that is less well suitable to a SQL database, e.g. image data or document data may not be available in the database and it may be hard to integrate it with the stored procedures..
3
  • 4
    "All application developers will need to be able to either change it or request a quick change." This assumes a dba. I think a decent developer should know SQL, for me if you are a programmer I expect you to know sql + at least 5 other paradigms. Why set the bar low? Other than that +1
    – Darknight
    Jul 28, 2012 at 18:18
  • 3
    So basically the only significant con is the availability of qualified SQL programmers? Jun 2, 2015 at 21:13
  • "One place is easier to maintain than multiple" I guess I'd say this is what a web service is for. With micro-services it makes them little contained business logic machines and decouples the caller from the database which would have been handy for a number of apps I've worked on.
    – user441521
    Aug 8, 2017 at 17:51
7

There are 2 big cons that I usually encounter:

  1. Inability to unit test your stored procedures. Sure there are some unit test frameworks out there, but you usually run into issues of running more than 1 set of tests at once (concurrency and transaction troubles).
  2. Most companies will want to put a lot of login in to stored procs, but then hire 5x more application programmers (be it C#, Ruby, Java, whatever) than database programmers or DBAs, and for some reason expect that all their app coders should fully understand and be able to write performant, scalable database procedures (in PL/SQL, T-SQL, or whatever). Management usually doesn't realize that doing that is a lot like hiring a C# expert to write you some Python code, and wondering why they aren't writing amazing applications.
5
  • 5
    I thought SPs were easy to unit test - they're already discrete units, you just need to work out what data to pre-populate the tables with to make them work.
    – gbjbaanb
    Jul 28, 2012 at 16:21
  • 1
    Stored procedures are pretty easy to unit-test, and every dev should have their own database, so there won't be any "concurrency and transaction troubles" Jul 28, 2012 at 17:16
  • Well, in my situation dev's can't have their own DB, both in Oracle and DB2, because the "free" ones don't support all the features of the "real" ones.
    – rally25rs
    Jul 28, 2012 at 22:48
  • A agree this preference is generally tied to db resource scarcity. But it's also due to the "top" developers thinking they are dbas. Most good programmers are a confident bunch and they sell themselves as sql experts. They do not realize their shortcomings until they hire a competent dba... but most shops never get there.
    – sam yi
    Nov 19, 2013 at 16:16
  • Why cant one unit test stored procs ?? I user TSQLT to unit test my TSQL
    – Pushpendra
    May 26, 2018 at 8:56
6

It depends

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.However, with multitier application design it might not be the good decision to encapsulate business logic in stored procedure.

It will NOT matter

As long as business logic:

  • lives in one place
  • where it is properly documented
  • proper access is provided through services that can be loosely coupled
  • through a published abstracted interface

I think that business logic in programming space makes more sense when Power of Expression is important in your team/project.

I belief that SQL space is not as expressive, but it can do the job. Use the best tools you have on hand for the most appropriate tasks. Fiddling with logic and higher order concepts is best done at the highest level. Consequently, storage and mass data manipulation is best done at the server level, probably in stored procedures.

1
  • 3
    +1, for the first bit, -1 for multiple applications advice. There is no need to put everything in the database when multiple apps are involved. Just make all applications go through the same business layer. Three tier development wasn't invented just for the fun of it. Jul 28, 2012 at 6:33
5

In the modern age this isn't really a common approach anymore. There was a time that putting business logic in stored procedures was a common thing to do early in web programming because the alternatives where few and often difficult to use. Today I would recommend against it.

I would have to say in 2012 there is nothing about business logic in stored procedures that could be justified as somehow a choice having beat out other approaches after having weighed the pro's and cons's. Any PRO points would be academic at best.

CON's It's not linq code. The future of TSQL is compiled linq, mine as well start to learn it.

Compiled code is more stable and easier to work with. If I'm debugging something there is so much information available if I used C# or Java.

Stored procedures are likely to end up with too many responsibilities, it's a best practice to try to keep your responsibilities to 1.

Lack of separation of responsibilities will lead to an application that just gets harder and harder to work on and less and less stable as it's core classes just get bigger and bigger.

If you like Microsoft Asp.Net MVC combined with Entity Framework Code First will allow you to rapidly build any application and you won't need any stored procedures.

7
  • 3
    -1 this is complete non-sense. LINQ eventually translates to SQL. You end up with the N+1 problem. Yes LINQ maybe ok for simple tasks. But once you have some complex problem, you need to take of the training wheels and do some real SQL.
    – Darknight
    Jul 28, 2012 at 18:25
  • 1
    LINQ is in the process of being deprecated. SQL is not.
    – Jay
    Jun 12, 2013 at 16:04
  • 2
    @Jay Where in the world did you hear that in 2013? It's 2016 and it's stronger than ever.
    – user441521
    Aug 26, 2016 at 20:05
  • 1
    The world is larger than what you see
    – Jay
    Aug 27, 2016 at 12:05
  • 1
    @Jay - I think you're confused. LINQ to SQL is no longer recommended, but not yet deprecated. LINQ itself is strong. MS's recommended approach to data access is now Entity Framework, and that is self evidently under development, and works well with LINQ.
    – Rob G
    Oct 26, 2016 at 13:11

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