On 2019-03-24 9:25 AM, Ron wrote:
On 3/24/19 1:42 AM, Frank wrote:
Hi all
As I understand it, a general rule of thumb is that you should never
create a physical column if the data could be derived from existing
columns. A possible reason for breaking this rule is for performance
reasons.
I have a situation where I am considering breaking the rule, but I am
not experienced enough in SQL to know if my reason is valid. I would
appreciate it if someone could glance at my 'before' and 'after'
scenarios and see if, from a 'gut-feel' point of view, I should proceed.
[snip]
Sure the second query joins a lot of tables, but is pretty straightforward.
What REALLY worries me is whether or not the query optimiser would look
at the WHERE CASE, run away screaming and then make it use sequential
scans. Thus, even query #1 would be slow.
I had not realised that. I hope someone else chimes in on this.
Is this a historical data set that's never updated, or current data
that's constantly added to?
It is the latter - current data constantly added to.
Frank
