Hi all,

The following procedure is very slow (about 13 seconds). I have found
that removing an index on FICHES (DATE_FICHE) makes it go significantly
faster (about 300 ms). The problem however, is that removing this index
makes other queries perform much slower... So my question is, is there
any workaround, to avoid the optimizer choosing to use this index (since
in this case it's not beneficial) without altogether removing it (since
in other cases it is)?

(Also, as a side note, I would be interested if anyone has a better way
to code the query in my procedure. Basically, I'm simply trying to
retrieve the most recent record in FICHECORPS where PRODUIT = :PRODUIT.
That sounds simple, but, well, it's not!)

So here's the procedure:

CREATE PROCEDURE PR_FETCHPIECES_A (
W_PRODUIT VARCHAR(50),
W_DATE_FICHE DATE,
W_HEUREDEBUT NUMERIC(15, 2)
) RETURNS (
DATE_FICHE DATE,
PIECES_A NUMERIC(15, 2),
HEUREDEBUT NUMERIC(15, 2)
) AS
declare variable lFirst char(1);
begin
lFirst = 'Y';
for select f.date_fiche, fc.pieces_a, fc.heuredebut
from fichescorps fc
join fiches f on f.code = fc.code
where fc.produit = :w_produit
and (f.date_fiche < :w_date_fiche or ((f.date_fiche = :w_date_fiche) and
(fc.heuredebut < :w_heuredebut)))
order by f.date_fiche DESC, fc.heuredebut DESC into :date_fiche,
:pieces_a, :heuredebut
do begin
if (lFirst = 'Y') then suspend;
else exit;
lFirst = 'N';
end
end

And here are the tables:

/* Table FICHES */
CREATE TABLE FICHES (
CODE FICHES_CODE NOT NULL,
EMPLOYE EMPLOYES_CODE NOT NULL,
DATE_FICHE DATE NOT NULL,
ETAT VARCHAR(20),
HEURESPAYEES NUMERIC(15, 2) DEFAULT 0,
TOTAL_TP NUMERIC(15, 2) DEFAULT 0,
TOTAL_TA NUMERIC(15, 2) DEFAULT 0,
ACTIVITE NUMERIC(15, 2),
TP_EFFECT NUMERIC(15, 2),
NEW_TA NUMERIC(15, 2)
)
---

/* PrimaryKey INTEG_14 */
ALTER TABLE FICHES
ADD CONSTRAINT INTEG_14
PRIMARY KEY (CODE)
---

/* ForeignKey FK_FICHES_EMPLOYE */
ALTER TABLE FICHES
ADD CONSTRAINT FK_FICHES_EMPLOYE
FOREIGN KEY (EMPLOYE)
REFERENCES EMPLOYES
---
CREATE ASCENDING INDEX I_FICHES_DATE_FICHE ON FICHES (DATE_FICHE)
---
CREATE TABLE FICHESCORPS (
CODE FICHES_CODE NOT NULL,
HEUREDEBUT NUMERIC(15, 2) NOT NULL,
HEUREFIN NUMERIC(15, 2),
TP NUMERIC(15, 2),
TA NUMERIC(15, 2),
PRODUIT VARCHAR(50),
PIECES NUMERIC(15, 2) DEFAULT 0,
PIECES_A NUMERIC(15, 2) DEFAULT 0,
PAUSE NUMERIC(15, 2) DEFAULT 0
)
---

/* Index I_FICHESCORPS_PRODUIT */
CREATE ASCENDING INDEX I_FICHESCORPS_PRODUIT ON FICHESCORPS (PRODUIT)
---

/* PrimaryKey INTEG_5 */
ALTER TABLE FICHESCORPS
ADD CONSTRAINT INTEG_5
PRIMARY KEY (CODE, HEUREDEBUT)
---

/* ForeignKey INTEG_6 */
ALTER TABLE FICHESCORPS
ADD CONSTRAINT INTEG_6
FOREIGN KEY (CODE)
REFERENCES FICHES

Any help would be much appreciated!

Thanks!
Jonathan Neve.