"how costly are joins in sql? and/or, what's the trade off between performance and normalization?" Code Answer

the best practice is to always start with 3nf, and then only consider denormalistion if you find a specific performance problem.

performance is just one of the issues you have to deal with with databases. by duplicating data, you run the risk of allowing inconsistent data to be in your database, thus nullifying one of the core principles of relational databases, consistency (the c in acid) ^a.

yes, joins have a cost, there's no getting around that. however, the cost is usually a lot less than you'd think, and can often be swamped by other factors like network transmission times. by making sure the relevant columns are indexed properly, you can avoid a lot of those costs.

and, remember the optimisation mantra: measure, don't guess! and measure in a production-like environment. and keep measuring (and tuning) periodically - optimisation is only a set and forget operation if your schema and data never change (very unlikely).

^a) reversion for performance can usually be made safe by using triggers to maintain consistency. this will, of course, slow down your updates, but may still let your selects run faster.

By MonTea on August 17 2022