""Ben Johnson"" <ben_johns@...> wrote in message
news:20011224124249.15467.qmail@......

>
> 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?

When you do a plain SELECT, it should be compiled into BLR, the internal
binary language representation, kind of byte code. The DSQL layer should
define parameters to communicate with the core engine, to be able to send
and retrieve information.
When you create a selectable stored procedure, it's compiled into BLR and
stored in this way. When you execute it, it doesn't have to be translated
from SQL into BLR again. The procedure's arguments are represented as BLR
parameters, so again there's nothing radically different.
There may be an overhead when putting the SELECT's results in the INTO
variables, but they usually are the same output parameters as in your
example.

Generally speaking, stored procedures that use FOR/INTO, when being executed
are as fast or slow as the plain request (SELECT), so I don't know why you
get a big difference between the two cases.

C.
--
Claudio Valderrama C. - http://www.cvalde.com - http://www.firebirdSql.org
Independent developer
Owner of the Interbase® WebRing