The problem is likely to be the ORDER BY in the SP. In my
experience, if you do a FOR SELECT ... ORDER BY ... in an SP, the SP
reads the entire table for sorting purposes before returning its
first record on SUSPEND. In most tools and middleware a
straightforward query is normally happy to display as soon as it has
fetched the first x records.

--- In ib-support@y..., "Ben Johnson" <ben_johns@r...> wrote:
>
> Hi,
>
> The Stored Procedure and SELECT statement given below performs
differently when run from IB Console as well
> as client software.
>
> CREATE PROCEDURE PRODUCT_MASTER_LOOKUP(sPrNm VARCHAR(50))
> RETURNS(PRODUCT_NM VARCHAR(50),MFR CHAR(5),RACK CHAR(3),STOCK
DECIMAL(5, 0),ID DECIMAL(5, 0))
> AS
> BEGIN
> FOR SELECT product_nm, mfr, rack, stock, id
> FROM product
> WHERE product_nm LIKE :sPrNm
> ORDER BY product_nm
> INTO :product_nm, :mfr, :rack, :stock, :id
> DO
> SUSPEND;
> END
>
> SELECT product_nm, mfr, rack, stock, id
> FROM product
> WHERE product_nm LIKE :sPrNm
> ORDER BY product_nm
>
> The query plans for both are same, but the SELECT statement is a
lot faster than the SP. Why it is so?
>
> O.S. - W2k
> Database - Firbird-1.0.0.-RC2-Win32
>
> Thank you.
>
> Ben