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