4 ms·

But denormalization is one strategy to improve the performance of a rdbms.

bunderbunder 8y ago
Standard rules about performance optimization apply. Denormalization can improve performance, but it can just as easily harm it. For code, I think by now we all understand that you should always start with clean, well-factored code, and then optimize only as much as is necessary, which is usually not at all, and always under profiler guidance. It's the same with DBs: You start with a clean, well-normalized schema, and then de-normalize only as much as is necessary, which is usually not at all, and always under profiler guidance. Also, keep in mind that improvements in compiler technology over time mean that the performance tricks of old can be useless or even actively harmful nowadays. This is true of SQL every bit as much as C.
trhway 8y ago
Denormalization as way of dealing with performance issues is like guilottin to cure headache. And more frequently than not the headache is still there even after that. It is true that for some narrow class of analytical workloads 20-25 years ago (behold BW of 199x) the denormalized case performance was better compare with straight non-optimized running of the same queries over normalized schema. Since then, the exponential availability of RAM and huge increase in streaming speed of HDD with stagnating IOPS (the main mistake in analytical workloads on HDD in the last 10-15 years - using nested loop join with indexed lookup into the large facts tables :) have made denormalization obsolete and harmful. If anything, the emergence of SSD and the huge RAMs moved things even further toward and beyond normalization, by making the "super-normalization", i.e. columnar tables, a viable everyday thing.
- h1d 8y ago
  What do you suggest in place?
  - bunderbunder 8y ago
    I would suggest not denormalizing at all. The idea that joins are slow is a holdover from the bad old days when everyone used MySQL and MySQL sucked at joins. On a more robust DBMS, a normalized schema will often yield better performance than the denormalized one. Less time will be lost to disk I/O thanks to the more compact format, the working set will be smaller so the DBMS can make more efficient use of its cache, and the optimizer will have more degrees of freedom to work with when trying to work out the most efficient way to execute the query. (edited to add: If you're having performance problems with a normalized schema, the first place to look isn't denormalization, it's the indexing strategy. And also making sure the queries aren't doing anything to prevent proper index usage. Use the Index, Luke! is a great, DB-agnostic resource for understanding how to go about this stuff: https://use-the-index-luke.com https://use-the-index-luke.com )
jfb 8y ago
Sure. But before one makes that argument, one must measure. It is not in any sense a panacea.
manigandham 8y ago
It's not, it's a strategy to improve the performance of a particular query or access pattern, and is usually the last resort after things like proper indexing, aggregations and materialized views. JOINS are fast and it all comes down to how much data you're moving. If it's a large table joining to a small set of values, then the joined data is quickly retrieved and applied to the bigger table, with great performance. If the join is between two large tables where every combination is unique then that's the unique case where the joined table is just adding another hop to get the full set of data for each row and is a perfect candidate for denormalization, although in that case it probably should've been a single table to begin with. Of course there's a spectrum between these two scenarios but it takes a lot before denormalization makes sense on any modern RDBMS.
jacques_chester 8y ago
True, if 1. You start from a normalized schema. 2. You denormalize in a structured way (eg dimensional modelling), rather than any old how. 3. You test the change. Database query planners work better when they can take logically-safe shortcuts in their work. In large part that comes down to a properly-constructed schema. Denormalizing makes it harder for the query planner. It also means you will probably lose out on future query planner enhancements. Generally speaking, if someone wants to denormalize, I want to know the actual business value created and that the business risk is properly understood.

Standard rules about performance optimization apply. Denormalization can improve performance, but it can just as easily harm it. For code, I think by now we all understand that you should always start with clean, well-factored code, and then optimize only as much as is necessary, which is usually not at all, and always under profiler guidance. It's the same with DBs: You start with a clean, well-normalized schema, and then de-normalize only as much as is necessary, which is usually not at all, and always under profiler guidance. Also, keep in mind that improvements in compiler technology over time mean that the performance tricks of old can be useless or even actively harmful nowadays. This is true of SQL every bit as much as C.

trhway 8y ago

Denormalization as way of dealing with performance issues is like guilottin to cure headache. And more frequently than not the headache is still there even after that. It is true that for some narrow class of analytical workloads 20-25 years ago (behold BW of 199x) the denormalized case performance was better compare with straight non-optimized running of the same queries over normalized schema. Since then, the exponential availability of RAM and huge increase in streaming speed of HDD with stagnating IOPS (the main mistake in analytical workloads on HDD in the last 10-15 years - using nested loop join with indexed lookup into the large facts tables :) have made denormalization obsolete and harmful. If anything, the emergence of SSD and the huge RAMs moved things even further toward and beyond normalization, by making the "super-normalization", i.e. columnar tables, a viable everyday thing.

h1d 8y ago
What do you suggest in place?
- bunderbunder 8y ago
  I would suggest not denormalizing at all. The idea that joins are slow is a holdover from the bad old days when everyone used MySQL and MySQL sucked at joins. On a more robust DBMS, a normalized schema will often yield better performance than the denormalized one. Less time will be lost to disk I/O thanks to the more compact format, the working set will be smaller so the DBMS can make more efficient use of its cache, and the optimizer will have more degrees of freedom to work with when trying to work out the most efficient way to execute the query. (edited to add: If you're having performance problems with a normalized schema, the first place to look isn't denormalization, it's the indexing strategy. And also making sure the queries aren't doing anything to prevent proper index usage. Use the Index, Luke! is a great, DB-agnostic resource for understanding how to go about this stuff: https://use-the-index-luke.com https://use-the-index-luke.com )

jfb 8y ago

Sure. But before one makes that argument, one must measure. It is not in any sense a panacea.

manigandham 8y ago

It's not, it's a strategy to improve the performance of a particular query or access pattern, and is usually the last resort after things like proper indexing, aggregations and materialized views. JOINS are fast and it all comes down to how much data you're moving. If it's a large table joining to a small set of values, then the joined data is quickly retrieved and applied to the bigger table, with great performance. If the join is between two large tables where every combination is unique then that's the unique case where the joined table is just adding another hop to get the full set of data for each row and is a perfect candidate for denormalization, although in that case it probably should've been a single table to begin with. Of course there's a spectrum between these two scenarios but it takes a lot before denormalization makes sense on any modern RDBMS.

jacques_chester 8y ago

True, if 1. You start from a normalized schema. 2. You denormalize in a structured way (eg dimensional modelling), rather than any old how. 3. You test the change. Database query planners work better when they can take logically-safe shortcuts in their work. In large part that comes down to a properly-constructed schema. Denormalizing makes it harder for the query planner. It also means you will probably lose out on future query planner enhancements. Generally speaking, if someone wants to denormalize, I want to know the actual business value created and that the business risk is properly understood.