My questions about computed columns concern a database that will be created
daily from about 5gb of compressed data. The DB will have about 10 tables.
The primary use of the table will be to produce summary reports of all of
the data. It's unlikely, but possible, that the computed fields will be
referenced in the where clause of a select statement.



Assuming that say 20% of the columns could be defined using the computed by
option, would doing so decrease the time it takes to insert the data into
the DB? If so, by how much? 10%? 20%?



How would using computed fields impact a select statement that accesses the
majority of the rows in the table if computed fields were referenced by the
select statement? If they were not referenced by the select statement?



How much overhead is there in accessing a computed column that only
references columns in the same row as the computed column?



Thanks

Hardy

_____

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Adam
Sent: Thursday, April 26, 2007 5:42 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 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));

I don't imagine that the CPU cost of calculating a simple
multiplication or substring is a significant overhead (by the way
substring is built into 1.5 or higher).

If you need to index col2 and you need to support use Firebird 1.5,
alternative 1 may be faster for such queries. Certainly alternative 2
is easier to maintain.

There is also the option of a creating a view on your base table with
these fields.

You have still not told us whether the select will be run millions of
times and the values never change, or whether the values constantly
change and are only selected occasionally. In stable data with lots of
selects, there may eventually be a pay off for not using computed
fields. In constantly changing data with few selects, there may
eventually be a pay off for using computed fields.

I doubt either approach is measurably faster for simple manipulations.
But only you can test it fairly against the usage you expect.

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]