Re: When to store data that could be derived

On Sun, Mar 24, 2019 at 9:05 AM Frank <frank(at)chagford(dot)com> wrote:

>
>
> 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.
>

There are a few other things to note here.

1 If the data is frequently used in its derivative form, putting it in a
function helps, and
2. You can index the output of the function which means you run it on
insert/update time and can often avoid running it on selection time if it
is just a part of the where clause.

In my experience usually we have used trigger-updated functions when the
field values are very large or expensive, and may need to be part of the
column list, and functions with functional indexes when we don't need to
ever put them in the select column list or where the columns are small and
easy to calculate.

--
Best Wishes,
Chris Travers

Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor
lock-in.
http://www.efficito.com/learn_more