MySQL Stored Procedures vs Layered Architecture

There is no single solution but it all depends on multiple factors whether to chose layered architecture or put most of business logic in database server. For Web Applications, it is recommended approach to follow layered architecture and avoid putting business login in the database for scalability and performance. Stored Procedures used to be a good approach for client server application with thick client applications.

For Web based applications, Stored Procedures offers hardly any advantages other than the following while there are numerous disadvantages:

Advantages:

1. You can write all business logic in one place
2. Performance benefits if the user base for the application is not big because all logic and multiple SQL queries can be executed in one go and avoid multiple round trips to database. There is a trade off between scalability vs performance
3. No need to deploy new version of application for trivial changes
4. Good for reports generation and doing batch processing
5. The same procedures can be called from mutiple, different frameworks and languages

Disadvantages:

There is a long list of disadvantages for Web/scalable applications and can be a bottleneck as data grows.

1. You need to have a dedicated DBA for performance tuning
2. No debugging support is available
3. Most important point is to adding Load on Database Server and it can be a big bottleneck for scalability of the application and can work only for a limited set of users. Load balancing and costly hardware required to scale the application
4. Most of the developers cannot create consistent code for Stored Procedures
5. SQL has limited set of options to write code
6. Application is not database agnostic