Subject Re: Computed Columns
Author Adam
--- In, "Hardy Sherwood" <HardyS@...>
> I've been advised by several people that database columns defined
via the
> "computed" options should be avoided. I've been told that they
> inefficient. I was advised that instead of using the computed
option for a
> column, I should store the result of the calculation in its own column.

They are a tool. They should not be avoided nor encouraged, but used
as required with an understanding of their behaviour, benefits and

My advice is that if the computed field requires looking up data from
other records, you are better off performance-wise with a redundant
field, using a trigger mechanism to keep it up-to-date.

If the computed field is simply a subtraction of a few fields or
perhaps some parsing on a particular field into constituent parts, a
computed field is better.

It is not clear from your question how you derive your office number
and account number within office.

Under Firebird 2, you can now create an index on an expression. Under
Firebird 1.5 or earlier, you could not, meaning that a computed field
could not have been indexed. This may also be what they were talking

Computed fields are calculated every time you perform a select. The
other approach the cost of calculation is only when something is
changed. If your product is select-heavy, and your calculation is
non-trivial, this is a real issue.

> This seems incredible to me. I would think that adding redundant
data to
> the database would reduce, not improve performance.

Consider an index, which is made up entirely of redundant data.