Speeding up SQL queries by orders of magnitude using UNION – Foxhound Systems
When SQL queries join multiple table branches with OR conditions—a "diamond-shaped" schema—query optimizers often struggle to create efficient plans, causing dramatic performance degradation. Using UNION ALL to split such queries into separate simple queries, each optimized independently, then combining the results can improve performance by orders of magnitude; the author demonstrates this with a real example where a 3,264ms query becomes 112ms when rewritten this way.