--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:

>
> At 20:44 13/10/2008, you wrote:
> >Dear experts!
> >I supposed that a stored procedure execute and return a temporary
> >table. However, I am wrong!
>
> Yes, you are wrong. ;-)
>
> The way a "select" procedure works is that each SUSPEND call you

make causes the most recent created set of output variables to be sent
to an output buffer. The procedure then waits (is suspended) until
the caller FETCHES that record from the buffer.

>
> >When I execute the following SQL:
> >select * from mysp();
> >it costs about 1 second.
>
> That is the length of time it takes for your application to fetch

enough rows to fill its own (client-side) buffer. In the IBExpert
interface, this probably means "all the output that will fill the grid".

>
> >select * from mysp() where t=100
> >it costs about 1 second
>
> ..for the first output row that has 100 in t.
>
> >but
> >select * from mysp() where t=110
>
> ...all output is rejected, row by row, until the first output row

has 110 in t.

>
> >it costs 4 second.
> >I try many times and get same results.
>
> That is not surprising - since you are repeatedly doing exactly the

same thing. These are not records on pages, they are rows of variables.

>
> >I use IBExpert to make analysis, I found that the number of indices
> >read when the stored procedure executes are differnts at last two

cases.

>
> Any indexes used are those that apply to the queries *within* the

stored procedure. A WHERE clause in a SELECT from a stored procedure
is expensive, since the entire procedure must complete in order to get
the matching rows. Write SELECT stored procedures with input
parameters to pass your search criteria - don't use a WHERE clause on
its output.

>
> ./heLen
>

OK! Thank Helen!
You make me clear now.
Very clear answer!
Thank again!
NHP