Disclaimer: When I say “Scale” I mean a couple of ideas:
- Accommodating a large increases in load, with less then half a couple of hours worth of work.
- Growing your technology team up to hundreds or thousands of application developers.
- The cost of your hardware infrastructure, does on correlate linearly with how much your product gets used. Rather the relationship should be Log(n).
Also in this post deals with propriety database management systems as well. Specifically I mention SQL Server, however the same can be said about Oracle, and etc.
Now with that out of the way lets get going.
Store Procedures don’t scale (in terms of people, cost, and infrastructure)
And here’s why:
Points “1” and “2” won’t really matter, if you work for an industry that has more money than it knows what to do with, and or has no competition, like government agencies, hospitals, big telecom, and etc.
1) The supply of people who know TSQL, and know it well isn’t as large as, the number of people who know modern object-oriented programming languages like Java, Python, etc. If you start dumping business logic into TSQL that sits in a propriety SQL Server Database, you are going to have to start hiring DBAs who specialize in SQL Server which is expensive
2) If you don’t hire people who specialize in TSQL and SQL Server tuning, you’re going to have to increase size of the hardware that the SQL Server runs on. However, since SQL server is proprietary software, its licensing is tied to the amount of hardware it runs on. So, if you want to run a bunch of poorly optimized TSQL on bigger infrastructure, you must pay more for additional hardware, and licensing (since its per core).
3) Let’s say you’re okay with both points “1” and “2”. There is going to be a hard limit of 128 cores and a terabyte of ram that you can run a SQL server instance on. At that point if the TSQL is not optimized, and your data set is big enough you’re still not going to have lighting fast query times. Which really means that dumping core business logic into TSQL for performance reasons, was just covering up an underlying problem of bad schema design.
4) When you use TSQL store procedures, everything is inside SQL Server. Now there are some problems with that:
4 – A) Your business logic does not live in GIT, its changes are not tracked, and there is no autonomous log of who changed what, when. You may say “My store procs do live in GIT, inside a DB project”, that may be true. However that does not guarantee, what’s in source control is any one of the SQL server instances you may have. Since someone could always login to SQL Server and change a store proc, without anyone knowing. Now to stop this from happening, you could have an experienced DBA that acts as the guardian of SQL Servers, however that again is expensive.
4 – B) Now if you have multiple “Staging”, “Prod”, and “Dev” SQL server instances. You can never be 100% sure that all the Store Procs are the same across the environments, or reliably determine how out of date a store proc is.
4 – C) Using TSQL a lot leads to abusing TSQL (unless you have a good DBAs), with no checks or balances in your data tier. Like cross DB actions, synonyms, sending out emails, doing large ETL jobs, creating functions, etc. At this point the database stops being becoming you Data Tier, and is pretty much you application tier. Except its a lot more expensive to maintain and run (SQL server licensing and DBAs), then a web app that does the same sort of stuff.