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
(keycol));



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
(keycol);



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



Thanks,

Hardy



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

_____

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Adam
Sent: Wednesday, April 25, 2007 6:31 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Computed Columns



--- In firebird-support@ <mailto:firebird-support%40yahoogroups.com>
yahoogroups.com, "Hardy Sherwood" <HardyS@...>
wrote:

>
> I've been advised by several people that database columns defined

via the

> "computed" options should be avoided. I've been told that they

extremely

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

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

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.

Adam




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