Thanks, now I understand.
I replaced LIKE with STARTING WITH and now is everything is OK and
very fast.

--- In firebird-support@yahoogroups.com, Lucas Franzen <luc@r...> wrote:
>
> Dorin,
>
> Dorin Vasilescu schrieb:
>
> > Hi
> > Anyone can explain to me why?
> >
> > This way:
> >
> > for select ... from ... where COLUMN like '100-50%' ...
> >
> > an index on COLUMN is used in query PLAN in a stored procedure and the
> > execution is very fast
> > and this way:
> >
> > for select ... from ... where COLUMN like :sp_parameter ...
> >
> > no index is used and the query takes very much compared with the first
> > variant
>
> Because SPs can only use a plan that is valid/existent at the time the
> stored procedure is compiled.
>
> In your first case you use a like 'abc%' which will result in using an
> index, since like 'SOMETHING%' is treated equivalent to STARTING WITH.
> (If there would have been no index on your COLUMN when you were
> compiling the stored proc it wouldn't use an index even if you added
the
> index afterwards. You would have got to recompile the procedure to make
> it "aware" of the existence of the new index).
>
> But if you send a parameter to the stored proc it can't know at compile
> time what parameter you're sending so it can't use an index.
>
> Luc.