Ha. KISS would be sticking to left joins and profiling their performance (with the appropriate indexes added) and finding alternative solutions only when it’s proven to be a problem, instead of… declaring left joins to be slow and immediately jumping the gun
Hey, thank you - already did that. Think 100 orders displayed on a page with each row having total, subtotals, discounts, tax owing, and gift card uses.
I also get to save a lot of money on server costs by doing this as DB is completely avoided.
If you need to invalidate this "cache", it's extremely expensive to find the records that need updating. It's probably better to use a summary table that's populated with triggers, or to just optimize your indexes and use a JOIN (the world won't end, I promise).
But before CTEs this sort of thing had regular use with recursive tree structures, in caching hierarchies. You'd make use of whatever SQL options you had to concatenate a group of IDs into a comma-separated string.
e.g. all the parent nodes of a given node in the hierarchy, and even (selectively) for caching all the descendent node IDs of enclosures. Though there are better ways to do this for trees that could be truly arbitrarily deep.
Yes, very possible. This is a multi-tenant application so I don't want to trigger a view refresh so frequently as most updates don't affect most people.
As the quote goes: there are 2 hard problems in computer science: cache invalidation, naming things, and off-by-1 errors. This doesn't cover the hard part yet.
Ha. KISS would be sticking to left joins and profiling their performance (with the appropriate indexes added) and finding alternative solutions only when it’s proven to be a problem, instead of… declaring left joins to be slow and immediately jumping the gun