Re: When to store data that could be derived

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