cost trade-offs for deploying changes to prod, stored procs vs. LINQ

Question

Some context for thisi question: it is not about development time over the entire iteration of development, but just the stage of deploying changes from dev to prod environment. "LINQ" here really means LINQ, ORM, or C# where T-SQL would otherwise be used. And here "stored procs" is just a general term that encompasses database objects which include stored procs, functions, and views. Question was just phrased as "LINQ", "stored procs" since the two are commonly compared in questions. To narrow the scope of the discussion we can assume web applications. Now finally the question is,

What are the trade-offs in the cost of deployment (from dev to prod) between the two approaches?

Some say it's easier to rollback Db schema changes than rollback changes across C# files in a solution. Others say it's easier to version or source control C# code than Db schema objects. If you make changes to LINQ queries, you'll most likely need to recompile the app to push the changes to prod. If you make changes to database schema, then that may require having a T-SQL script run in order to sync the changes to prod.

As with many decisions with programming, the chosen solution may be context-sensitive. e.g. for data structures some situations may call for a hash map, and for others, it may be best to use a stack. Each has its own advantages/disadvantages.

Answer 1

You are asking the wrong question, it isn't about cost it is about RISK.

Database code changes ripple upward and outward where as non-Database code changes ( Application Logic ) ripple downward and inward. This means changes to the things that ripple upward and outward will always be risker.

Change a stored procedure, function, table or constraint or anything really and you can easily break every application that uses that database without any way to know the complete extent of what will be affected. You would have to get every application that touches the database to regression test, and face it, in the real world just having Unit tests much less comprehensive Integration Tests is a rare thing.

Here is a real world example that shows that the cost is determined by the risk. This is just an example, every situation will be different.

A well meaning DBA reordered some columns of a table when doing some other un-related work that was requested. It completely broke a bunch of apps silently that had SELECT * queries and referred to the returned fields by index, since all the columns were mapped to String types, the application slowly and silently corrupted tonnes of data as data was read and re-written to the database in the wrong locations.

This really happened, I had to clean up the mess personally. I was a hired gun contractor at the time so I got the tedious work of fixing the broken code and writing clean up programs to re-order the data. Imagine how much that cost!

Whereas if you change some Application logic, the only thing you directly affect is the application you are working on. (Barring doing something that corrupts the data in the database and other silly things, that can be deterministically tested).

The riskier one will always be orders of magnatitude more expensive to fix when it blows up in production.

Your cost is determined by which is riskier?

Answer 2

In 2012, I actually would argue they are the same -- presuming you have a solid way of deploying database migrations and solid deployment procedures. If you are dealing with deploying database changes -- which do not compile with but are part and parcel with source code -- you take on the same overhead costs weather it be an ORM-based solution or a RPC-based solution (stored procedures are remote procedure calls).

Typically, code changes are cleaner and easier to deploy and especially to roll back in production. Worst case scenario is knocking the app down for 5 seconds to replace files and spin it up. Database schema updates, or even worse restores, can be ugly affairs. Depending on scale, downtime can measure hours.

Answer 3

Your question implies that a new feature of your web app can be deployed either by changes to stored procs or by changes to the app. To my experience, this is very seldom the case. Most often you have either

• just exchange the app (and keep the database as it is)
• or exchange the app and change the db

Obviously the second step needs more efford than the first.

Only exception from that is when you app is especially designed for generically executing queries or stored procs, where the queries itself are considered to be a form of data, from among the user or the app can choose dynamically which one to execute Do you have such an architecture in mind?.