We are on Firebird 1.5.4, so will see if it is possible to move to 1.5.6.
Ideally we should move to the latest version, but this is not under our
control.
Will go through the bug fixes and see if this behaviour changed from 1.5.4
onwards.
In fact in this particular case it is the like query that produces the
better query plan.

RBS

On Wed, Feb 2, 2011 at 9:02 PM, Helen Borrie <helebor@...> wrote:

>
>
> At 07:56 AM 3/02/2011, Bart Smissaert wrote:
> >Have seen a situation now where there is big performance difference
> between
> >like 'Z%' and starting with 'Z' due to it picking a very different query
> >plan.
>
> [ ... ]
>
> >The second query plan is the right one and run much faster than the first.
> >This is all on Firebird 1.5 and I expect that Firebird 2 handles this
> >better, but we can't change Firebird.
>
> Typos aside, Firebird 1.5.6 should resolve LIKE 'Z%' to STARTING WITH 'Z'
> under the hood and give you the optimal plan. Are you using an old
> sub-release of 1.5? (There are lots of other reasons why you shouldn't keep
> running old sub-releases!)
>
>
> >Now, my questions are:
> >Is there any way we can deduct when to do like or starting with in this
> kind
> >of query.
> >Is there a simple way to force the right query plan?
>
> The first thing I'd want to try is updating your server to v.1.5.6.
>
>
> >Is this indeed dealt with better in Firebird 2?
>
> That's a bit of a "When did you stop beating your wife?" question. The
> behaviour of the optimizer changed significantly in Firebird 2, improved
> again in 2.1 and again in 2.5. Where feasible, optimizer improvements have
> been backported all the way down the chain....that said, v.1.5.6 was the
> last-ever of the 1.5.x series and v.2.0.6 was the last-ever of the 2.0.x
> series...so no more backports.
>
> If you need insight into changes, reading the bug-fix reports for each
> sub-release after your one would be a good way to research it.
>
> ./heLen
>
>
>


[Non-text portions of this message have been removed]