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