Hi,

> I have several questions about the above QA....
> 1. I always thought that in relational databases, 'server side queries'
> are synonimous with views, and that Selectable SPs are a feature
> unique to IB/FB only. Is the 'recommendation' above still
> correct,
> in the sense that Selectable SPs are the recommended way in
> IB/FB
> to create 'server side queries' ?

SPs are precompiled and Views are prepared each time you use them. I
use views when I know that a certain query will be used multiple
times in my application and returns a subset of the data. Views can
also be used to limit access rights to tables by assigning the user
rights to the view instead of the table. I use SPs for more complex
operations, i.e. statistical data compiled from the financial data
from the whole database or to concatenate more than one varchar field
together, so one null field would return a null if concatenated with a
non-null field.

> 2. There was a post recently mentioning the (bad) performance of joining

> selectable SPs with a table or view. I believe the message said
> something about the result set of SPs not using index so join
> operations will be slow. Is there any definitive documentation
> the developers on this subject ?

SPs use the table indices for the DML operations within the SP. But,
the SP doesn't have an index on its own result set.

> Another question popped up recently, still related to the above subject.
> Suppose I'd need to calculate some value for every record in
> a table. There are at least two ways to write the sp for this:
> ...
> create procedure calc_one( record_id integer )
> returns ( somevalue numeric(18,4) )
> as begin
> ...
> end;
> ...
> as opposed to
> ...
> create procedure calc_two
> returns ( record_id integer, somevalue numeric(18,4) )
> as begin
> ...
> end;
> ...

> CALC_ONE is like a function, you give it a record_id (primary key) and
> it
> will return just one value for the calculation. Normally I'd use the
> following construct to retrieve the data.
> ...
> select record_id, (select somevalue from
> calc_one(mytable.record_id)
> from mytable;
> ...
> CALC_TWO is a more typical select procedure, returning a result set
> containing two fields, so it can be treated like a table. Calling this
> one is a bit simpler.
> ...
> select record_id, somevalue
> from calc_two;
> ...

> Now the main question I have at this point is which approach is the
> recommended practice in FB? I suppose there must be some difference
> in performance, but my limited testing did not reveal any, probably
> because my test dataset is not big enough. Please advise.

I use CALC_TWO style when the only data I want is returned by the SP.
And, I use CALC_ONE style when the result set would require more
field from mytable. If you would want to do the same with CALC_TWO,
you'll need to include a JOIN in your query.

If you have more questions don't hesitate.

Daniel Rail
Senior System Engineer
ACCRA Group Inc. (www.accra.ca)
ACCRA Med Software Inc. (www.accramed.ca)