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.