>Ivan Wrote:>
>> 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?
>
>What about this
>
> where (D.SuplCde BETWEEN :ISupplierCodeMIN AND :ISupplierCodeMAX) and

...

>
>i.e. instead of :ISupplierCode containing normal value or Null,
>you either assign the same value to both ISupplierCodeMIN and

ISupplierCodeMAX,

>or you assign minimal possible value to ISupplierCodeMIN and maximal

possible

>value to ISupplierCodeMAX.

Ah, yes, this is looking promising - and you've jogged my memory too.
Last release I started doing something similar for some of the input
parameters, on some of the stored procs, using local variables to
convert the user's value-or-NULL input into a Min/Max range.
This has caused some new speed degradation problems now though if the
selectivity on the index happens to be greater than others (and the rest
are still using the index killer code). So, even though the user
specified null for a JobNumber, that is now the index getting used every
time. So the index is now getting used to retrieve a vast amount of
records, but it still looks good in IBExpert's performance analysis tool
;-P

I think the trick here will be to convert each and every parameter to
the range option, and see if FB is then able to choose the appropriate
index... I'll give the a bash and let you know how it goes.

Thanks
Maya