Does

select first 1 *
from test;

work?

If so it is not in an infinite loop, it is just doing a LOT of work,
and perhaps doing it in a silly way. My guess is that you are
encountering the same sort of speed issues that hit us. It worked fine
for 10 records, 20 it started to struggle, 50 forget it. (Just as well
we changed, we have several customers with > 20,000 records in that
table now).

The SP will be executed for every record of test. If the SP itself
does anything other than basic UDF or concatenation work on the
inputs, my guess is that performance will be horrible.

You can use a selectable SP to compute it if that will help you, but
if the problem is that it takes too long to do that at select time,
then I do not see how this will help.

Often, it is better to compute the FullName when the other fields are
entered or changed (because this doesn't happen anywhere near as often
as a select in most cases). You are also then able to put an index on
FullName, so

Select *
from test
where FullName like 'Ad%';

could work very efficiently using an index, rather than a table scan.

Create a varchar field called FullName, then create a trigger before
insert and before update that updates this field. To be even more
efficient, you should only call this function if at least one of the
following has changed (TEST_ID, NAME, FIRSTNAME, COMPANY, FULLNAME).
This is obviously a test case, so all the fields are used, but in the
real world you have fields that have no bearing on the fullname, and a
change to these fields should not trigger the SP_GET_FULLNAME to run.

ie

IF (old.TestID <> New.TestID) OR .... etc


Adam

--- In firebird-support@yahoogroups.com, <zedalaye@n...> wrote:
> Hi all,
>
> This statement :
>
> create table TEST (
> TEST_ID integer not null,
> NAME varchar(80),
> FIRSTNAME varchar(80),
> COMPANY varchar(80),
> FULLNAME computed by
> (
> (select FULLNAME from SP_GET_FULLNAME (TEST.TEST_ID,
> TEST.NAME,
> TEST.FIRSTNAME,
> TEST.COMPANY)
> )
> )
> );
>
> As suggested by Adam "Compiles" (no SQL error) but the server "infinite
> loops" (99% of processor used, I had to kill fbserver process.)
>
> I will create a stored proc to compute this field at select time.
>
> Regards
>
> Pierre Y.
>
>
> ________________________________________
> De : firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com] De la part de Jason Dodson
> Envoyé : vendredi 22 avril 2005 15:42
> À : firebird-support@yahoogroups.com
> Objet : Re: [firebird-support] Field computed by a stored proc
>
> For a computed field, you are more or less restricted to using the
> fields in the current record, any results from a UDF function, and/or
> any evaluations contained there of.
>
> In short, I don't believe you can use a recordset in a computed field.
> Honestly, thinking about it, most cases, the performance of such a
> method would be ... not good.
>
> Jason
>
> zedalaye@n... wrote:
> > Hi,
> >
> > How can I create a field computed by a stored proc ?
> >
> > Create table test (
> > Test_id Integer not null,
> > Name VarChar(80),
> > FirstName VarChar(80),
> > Company VarChar(80),
> > FullName computed by ((select fullname from sp_get_fullname(test_id,
> name,
> > first_name, company)))
> > );
> >
> > Firebird refuses to compile that statement.
> >
> > Can you help me ?
> >
> > Thank you very much, regards,
> >
> > Pierre Y.
> >
> >
> >
> >
> >
> > Yahoo! Groups Links
> >
> >
> >
> >
> >
> >
> >
> >
> >
>
>
> ________________________________________
> Yahoo! Groups Links
> • To visit your group on the web, go to:
> http://groups.yahoo.com/group/firebird-support/
>
> • To unsubscribe from this group, send an email to:
> firebird-support-unsubscribe@yahoogroups.com
>
> • Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.