Hi Maya!

Did you try combining specifying a plan and converting to a range? It may or may not speed things up, but it should allow you to specify a plan explicitly as long as you don't OR the fields. Though you still cannot change the plan depending on input params.

With EXECUTE STATEMENT I suppose you could try to double any quotes that you find, so that the statement at least could work.

HTH,
Set

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

Hi Ivan, Sasha,

Thanks for all your advice. Here's the results of my attempts on getting
or :IParam is null
to use an index:

Specifying a PLAN:
Doesn't work for 2 reasons, namely
- can't use an IF/CASE statement to specify different plans according to the data in the input params
- if I just test by specifying one PLAN, it ignores me anyway, as FB doesn't know how to make use of it anyway, since I still have the OR in the statement, which kills the index still regardless of the plan.

Using EXECUTE STATEMENT
- this works quite well, except for one main issue: the codes may have funny characters in them, such as apostrophes, which break the constructed SQL. (Yes, I broke the golden rule, and allowed users to specify their own codes, which are also primary keys)
- other smaller issues: lose a lot of the benefits of using SP's, which I've gotten quite used to, such as showing dependencies on tables, not compiling if there is a problem, and more.

Converting to a RANGE
- only ever uses one of the indices, depending on the selectivity of the index, not the nature of the query, so will be slow half the time in this case.

REPEATING the same SQL depending on input parameters
- created a new sub-procedure, which contains the minimum amount of coding possible. The main report procedure now joins on this one.
- the sub-procedure executes different statements depending on the input values. Out of the 4 input combinations, 6 different statements chosen (leaving the lower selectivity cases as not attempting to use the index, for eg. on the IsActive option which will one ever have 2 states).

I might return to the EXECUTE statement idea later, if we ever change the primary keys on our tables.

Anyway, thank you, I can sleep easier now, knowing the issue has been fully investigated, and there isn't some easy solution I've been missing all this time. And future versions of FB probably aren't ever going to change the situation, so it's my thinking that needs to change rather ;-P