OK, thanks for the information about those query plan hacks and will have a
look at that.
Unless I have some simple way of knowing what query will produce query plan
I can't use
it in my app though. Will if we can move to 1.5.6.

Bart


On Wed, Feb 2, 2011 at 10:15 PM, Svein Erling <
svein.erling.tysvaer@...> wrote:

>
>
> --- In firebird-support@yahoogroups.com<firebird-support%40yahoogroups.com>,
> 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.
> > This is with a normal, direct SQL (done in Database Workbench and
> > also via ADO and ODBC), so not with any parameters.
>
> Sure, the optimizer should be the same regardless of whether you use DBW,
> ADO or ODBC.
>
>
> > The queries that give the different query plans here are:
> >
> > SELECT
> > K.TERM_KEY,
> > R.READ_CODE,
> > R.TERM30,
> > R.USAGE_COUNT
> > FROM
> > KEYWORD K
> > INNER JOIN READCODE R ON
> > (K.TERM_ID = R.TERM_ID)
> > WHERE
> > K.TERM_KEY STARTING WITH 'Z'
> >
> > query plan:
> > JOIN (R NATURAL,K INDEX (KEYWORD_KEY,KEYWORD_KEY2))
> >
> > and
> >
> > SELECT
> > K.TERM_KEY,
> > R.READ_CODE,
> > R.TERM30,
> > R.USAGE_COUNT
> > FROM
> > KEYWORD K
> > INNER JOIN READCODE R ON
> > (K.TERM_ID = R.TERM_ID)
> > WHERE
> > K.TERM_KEY LIKE WITH 'Z%'
> >
> > query plan:
> > JOIN (K INDEX (KEYWORD_KEY),R INDEX (READCODE_KEY))
> >
> > 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.
> >
> > Now, my questions are:
> > Is there any way we can deduct when to do like or starting with in
> > this kind of query.
>
> No, not with any reasonable effort. I thought they would be identical, but,
> unlike Helen, I didn't know that change came about in 1.5.6 (I thought it
> was 1.5.0 or something). Now, I am wondering whether LIKE always chooses the
> same index as you report or if it was coincidental that it is better than
> STARTING WITH.
>
>
> > Is there a simple way to force the right query plan?
>
> Well, almost. If you add ||'' it will prevent the KEYWORD_KEY2 index from
> being used and be force the optimizer to choose differently (although,
> theoretically, it might come up with another bad plan):
>
>
> FROM KEYWORD K
> INNER JOIN READCODE R ON K.TERM_ID||'' = R.TERM_ID
> WHERE K.TERM_KEY STARTING WITH 'Z'
>
> > Is this indeed dealt with better in Firebird 2?
>
> I cannot tell for this particular query, but the need for hacks like +0 or
> ||'' has gradually been reduced and the optimizer is generally better now
> than it was with Firebird 1.5.
>
> HTH,
> Set
>
>
>

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