Jonathan Neve wrote:

>
> 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).
> ...

You could try the two small changes below. The idea is to prevent using an
index for the sort (order by).

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

Change to:
for select f.date_fiche+0, 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

Change to:
order by 1 DESC, fc.heuredebut DESC

> into :date_fiche, :pieces_a, :heuredebut
> do begin
> if (lFirst = 'Y') then suspend;
> else exit;
> lFirst = 'N';
> end
> end

--
Aage J.