Hi Maya,
it can still be a 'problem' that Firebird will only choose the most selective indexes, if you want to, you may of course specify the entire PLAN explicitly in your select statement (I know of a few tricks how to prevent an index from being used, but short of specifying a plan explicitly, I know of no good way to force additional indexes to be used).

Ivan's solution will of course not work if fields can be NULL, but is worth trying in other cases. Though the underlying problem that Firebird has to choose the PLAN at prepare time and that you're not willing to help Firebird through your SQL statement is still present, and you're not going to get equally good performance as you would if your WHERE clause were determined dynamically.

You may end up choosing to do things in a way that users 'normally' will be content with. E.g. if SuplCde is somewhat less selective than WrhseCde, but specified in 90% of queries as opposed to 1% where WrhseCde is specified without SuplCde being specified, then

where D.SuplCde BETWEEN :ISupplierCodeMIN AND :ISupplierCodeMAX
and D.WrhseCde = coalesce(:IWarehouseCode, D.WrhseCde)

may be what you end up with (even though it will slow things down for that 1% of cases mentioned above).

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Maya Opperman
Sent: 16. mai 2008 08:06
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Possible to write this in a way that indices will be used

>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