--- In firebird-support@yahoogroups.com, Jason Dodson <jasond@b...> wrote:

> Hey all,
>
> I have just attempted to add a computed field to one of our tables.
> Consistantly, it sends the server process to 100% cpu utilization. All
> current connections stop working (They don't drop, they just "freeze"),
> and the server no longer can accept connections (It doesn't outright
> refuse them, it just never connects). On windows, the server is so
> unresponsive that it doesn't cooperate with the Services manager. It is
> signaled to stop, but just sits there, tearing up the cpu, constantly
> with the status message "stopping".
>
> Here is my SQL, though it won't be of perticular use by itself (I don't
> think):
>
> Alter Table NAInvoices Add MaintInvoiceTotal Computed By (Case When
> Coalesce(CustDiv, "") = "" Then (Select MaintInvoiceTotal From
> GetInvoiceTotalsByCompany(NAInvoices.InvNo, NAInvoices.CustID)) Else
> (Select MaintInvoiceTotal From
> GetInvoiceTotalsByCompanyDiv(NAInvoices.InvNo, NAInvoices.CustID,
> NAInvoices.CustDiv)) End)
>
> Now, up until forever ago, I would loop through the NAInvoices table,
> and execute the procedure
> GetInvoiceTotalsByCompany/GetInvoiceTotalsByCompanyDiv for each row.
> This took seconds. Nothing is complex, and the amount of data is small.
>
> I can accept that maybe I have bad indexes or too much data, or
> something along those lines... but here is the kicker. Even when I am
> logged in as a user with absolutely no privledges to NAInvoices, it
> STILL locks up as explained above.
>
> Is this a bug? Or is there an obvious no-no that I am touching on?
>

Its a no-no. A computed field is calculated for every record of a
seclect, so for every record selected, it needs to run both stored
procedures. We found that computed fields that reference more than the
data in the current record work fine up to about 15 records, beyond
that it gets exponentially bad.

As a general rule, do not use a computed by field that needs to run
another query.

Now for a work-around that does have good performance.
Instead of creating a computed field, create a normal field that holds
that information. Create a stored procedure that calculates the
correct data. Then create a trigger on that record before insert or
before update that calls the SP and fills the record correctly. You
will need to also check what other tables the
GetInvoiceTotalsByCompany and GetInvoiceTotalsByCompanyDiv are
impacted by, and place triggers on them so that they also re-set that
field.

Often though it is cheaper to not include the calculated field at all,
but rather to join to it in a query or view, but it really depends on
what your data looks like, whether you are updating a lot of these
records in a go and seldom select, or whether you are selecting
frequently and less often updating or inserting.

Adam