Subject RE: [firebird-support] Re: Computed Columns
Author Hardy Sherwood
I guess I was not all that clear on my questions concerning computed
columns. The expressions in the computed columns I'm concerned about would
only reference columns in the same row.

Here's a simple example.

Alternative I. Create table alt1 (keycol integer not null, col1
integer, col2 integer, primary key (keycol));

Alternative II. Create table alt1 (keycol integer not null, col1
integer, col2 computed (100*col1), primary key

Here's another example.

Alternative I. Create table alt2 (keycol char(7) not null, col1
char(2), col2 char(5),
primary key (keycol);

Alternative II. Create table alt2 (keycol char(7) not null, col1
computed (substr(keycol,1,2)), col2
computed(substr(keycol,3,7)), primary key

The data in the columns for alternative I would be such that the results of
any selects would for alternative I would be the same as selects for
alternative II.

I'd appreciate if you would let me know the pros & cons of each of these



PS: Thank you for the quick response to my prior inquiry.


[] On Behalf Of Adam
Sent: Wednesday, April 25, 2007 6:31 PM
Subject: [firebird-support] Re: Computed Columns

--- In firebird-support@ <>, "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 col requires looking up data from
other records, you are better off performance-wise with a redundant
col, using a trigger mechanism to keep it up-to-date.

If the computed col is simply a subtraction of a few cols or
perhaps some parsing on a particular col into constituent parts, a
computed col 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 col
could not have been indexed. This may also be what they were talking

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


This message and contents are confidential and intended solely for
the use of the individual or entity to whom they are addressed. If you
have received this email in error please notify the system manager.

The original email message has been scanned for computer viruses.

[Non-text portions of this message have been removed]