Hello,

I get very poor performances when I try to use a variable in a select
inside a stored procedure.(See details on the table at the end). Bellow,
you can see three versions of the same select and related speeds :

Direct query :
select id_waypoint from waypoints where wp_nom like 'CHAMBER%';
prepare time 0.0204 s
execution time 0.0125 s
PLAN (WAYPOINTS INDEX (IDX_WP_NOM,IDX_WP_NOM_DESC))

Stored procedure without parameter :
begin
FOR
SELECT ID_WAYPOINT
FROM WAYPOINTS
WHERE WP_NOM LIKE 'CHAMBER%'
INTO :ID_WP
DO
BEGIN
SUSPEND;
END
end
select * from "COMMENCE_PAR"();
prepare time 0.0046 s
execution time 0.0032 s
PLAN (WAYPOINTS INDEX (IDX_WP_NOM,IDX_WP_NOM_DESC))


Stored procedure with parameter
CREATE PROCEDURE "COMMENCE_PAR"
(
"DEBUT" VARCHAR(8)
)
RETURNS
(
"ID_WP" INTEGER
)
AS
begin
FOR
SELECT ID_WAYPOINT
FROM WAYPOINTS
WHERE WP_NOM LIKE :DEBUT
INTO :ID_WP
DO
BEGIN
SUSPEND;
END

end
select * from "COMMENCE_PAR"('CHAMBER%');
prepare time 0.0031 s
execution time 2mn59 s
PLAN (WAYPOINTS NATURAL)

As you can see, the last version is very slow ... and do not use
indexes. How to get better performances?

Table :
CREATE DOMAIN "ANGLE_LATITUDE" AS DOUBLE PRECISION
CHECK (VALUE BETWEEN -1.5708 AND 1.5708) NOT NULL;
CREATE DOMAIN "ANGLE_LONGITUDE" AS DOUBLE PRECISION
CHECK (VALUE BETWEEN -3.1416 AND 3.1416) NOT NULL;

CREATE TABLE "WAYPOINTS"
(
"ID_WAYPOINT" INTEGER NOT NULL,
"WP_NOM" CHAR(8) CHARACTER SET ASCII NOT NULL,
"WP_COMMENTAIRE" VARCHAR(20) CHARACTER SET ISO8859_1,
"WP_LATITUDE" ANGLE_LATITUDE ,
"WP_LONGITUDE" ANGLE_LONGITUDE ,
"WP_ALTITUDE" INTEGER,
CONSTRAINT "PK_WAYPOINTS" PRIMARY KEY ("ID_WAYPOINT")
);

/* Index definitions for WAYPOINTS */

CREATE INDEX "IDX_WP_LAT" ON "WAYPOINTS"("WP_LATITUDE");
CREATE DESCENDING INDEX "IDX_WP_LAT_DESC" ON "WAYPOINTS"("WP_LATITUDE");
CREATE INDEX "IDX_WP_LONG" ON "WAYPOINTS"("WP_LONGITUDE");
CREATE DESCENDING INDEX "IDX_WP_LONG_DESC" ON "WAYPOINTS"("WP_LONGITUDE");
CREATE INDEX "IDX_WP_NOM" ON "WAYPOINTS"("WP_NOM");
CREATE DESCENDING INDEX "IDX_WP_NOM_DESC" ON "WAYPOINTS"("WP_NOM");


Eric