Re:Speeding up SQL queries by orders of magnitude using UNION
by jaredsohn on Saturday March 20, 2021 @09:57PM
(#26527558)
I've found denormalization to be helpful for such performance issues in that it reduces the number of joins. It can also be useful for debugging if you want to keep track of values from other tables at the time that data was processed within the primary table.
[ Parent
| on HN ]
Re:Speeding up SQL queries by orders of magnitude using UNION
by monoideism on Saturday March 20, 2021 @10:05PM
(#26527587)
A certain amount of denormalization can be helpful, but often there's a trade-off in terms of maintainability.
Re:Speeding up SQL queries by orders of magnitude using UNION
by rovr138 on Saturday March 20, 2021 @10:17PM
(#26527632)
Not who you replied to.
But yes,
As an example, we keep our data normalized and add extra tables to do the operations we need. It’s expensive since we have to precompute, etc. But then on lookups it’s quicker.
Like everything, it depends on what you want to optimize for.
Re:Speeding up SQL queries by orders of magnitude using UNION
by jaredsohn on Sunday March 21, 2021 @12:21AM
(#26528083)
Yep, in my case we wanted to make user-facing queries fast (i.e. a reports page). The extra complexity (not much; we just have to remember when to update certain values in other tables when they change) is definitely worth it since load time dropped from 30 seconds sometimes to almost instant.
Denormalizing everything is definitely a pain; keeping data in sync in mongodb (which lacks traditional joins) on a previous project was not fun; now using postgresql.
Re:Speeding up SQL queries by orders of magnitude using UNION
by epr on Sunday March 21, 2021 @12:05PM
(#26531401)
denormalization should only be seen as a last resort
Re:Speeding up SQL queries by orders of magnitude using UNION
by slt2021 on Saturday March 20, 2021 @10:03PM
(#26527580)
denormalization aka memoization aka "dynamic programming of SQL" :-) - all three provide speed-up because they compute value once and reuse it later
Re:Speeding up SQL queries by orders of magnitude using UNION
by mtone on Sunday March 21, 2021 @12:44AM
(#26528179)
Except that your source of truth aka your raw data is now memoized, hardly a win. This is not a goal of memoization or dynamic programming at all.
Perfect for a reporting database though.