Hi Maya, Sasha is right.

This part can benefit from an index:
(D.SuplCde = :ISupplierCode)

This part cannot use an index:
(:ISupplierCode is null)

The plan has to be determined before the value of ISupplierCode can be
set, hence Firebird has to prepare for the 'worst' case scenario, i.e.
no index.

If performance matters, I'd recommend building the query dynamically -
normally it isn't all that difficult to add '(D.SuplCde =
:ISupplierCode)' when you know that your users wants to use a parameter,
but still before prepare time.

When performance doesn't matter (or I know that other parts of the query
will be more selective), I sometimes use the simple

WHERE D.SuplCde = coalesce(:ISupplierCode, D.SuplCde)

which basically is identical to your ORed statement (and doesn't use an
index).

HTH,
Set

Maya Opperman wrote:

> Hi,
>
> I have a query written as follows:
>
> select blah..,
> from MyTable D
> where ((D.SuplCde = :ISupplierCode) or (:ISupplierCode is null))
> and ((D.WrhseCde = :IWarehouseCode) or (:IWarehouseCode is null))
> and ((D.StkCde = :IStockCode) or (:IStockCode is null))
> and ((D.IsActve = 'Y') or (:IIncludeInactive = 'Y'))
>
> Is there any elegant way that I can rewrite this so that it will make
> use of indexes, but without me having repeat vast volumes of code over
> and over?
>
> Thanks
> Maya