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.

There are 2 tables here:

KEYWORD:
CREATE TABLE KEYWORD(
TERM_KEY CHAR(10) NOT NULL,
TERM_ID CHAR(5) NOT NULL)

having some 300000 records with non-unique indexes on both fields.

The second table is called READCODE:

CREATE TABLE READCODE(
READ_CODE CHAR(5) NOT NULL,
TERM_ID CHAR(5) NOT NULL,
TERM_TYPE SMALLINT,
CONCEPT_STATUS SMALLINT,
SUBJECT_TYPE CHAR(5),
SUBJECT_TYPE_2 CHAR(5),
TERM30 VARCHAR(30),
TERM60 VARCHAR(60),
USAGE_COUNT INTEGER,
USER_DORMANT INTEGER,
ISUSERDEFINED CHAR(1),
MAPPEDREAD_CODE CHAR(5))

This table has 200000 records and has 2 unique indexes on READ_CODE, TERM_ID
and the reverse TERM_ID, READ_CODE

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.
Is there a simple way to force the right query plan?
Is this indeed dealt with better in Firebird 2?

Thanks in advance for any advice/insight.


RBS

On Wed, Feb 2, 2011 at 12:24 PM, Bart Smissaert <bart.smissaert@...>wrote:

> Ah, sorry, I misunderstood and see now what you meant.
> I won't be using parameters for where conditions, so I think for me then
> like and starting with in this particular situation will be the same.
> Thanks again for explaining.
>
> RBS
>
> 2011/2/2 Svein Erling Tysv�r <svein.erling.tysvaer@...>
>
>>
>>
>> >Thanks for that thorough information.
>>
>> >So if I get you right no difference in normal direct SQL, but there can
>> be a
>> >difference in stored procedures?
>>
>> No, that is not what I said. I said there can be a difference if you use
>> parameters as opposed to constants. So, to use Delphi syntax (at least if
>> used with IBO):
>>
>> MyCursor.SQL.Add('select * from MyTable where field1 starting ''Z''');
>> MyCursor.Prepare;
>>
>> Should get the same plan as:
>>
>> MyCursor.SQL.Add('select * from MyTable where field1 like ''Z%''');
>> MyCursor.Prepare;
>>
>> Whereas
>>
>> MyCursor.SQL.Add('select * from MyTable where field1 starting :Param1');
>> MyCursor.Prepare;
>> MyCursor.ParamByName(Param1).AsString:='Z'
>>
>> Is very different from
>>
>> MyCursor.SQL.Add('select * from MyTable where field1 like :Param1');
>> MyCursor.Prepare;
>> MyCursor.ParamByName(Param1).AsString:='Z%'
>>
>> The point is that the plan is generated at prepare time, and at that point
>> in time, the last SQL doesn't know whether it can use an index or not (i.e.
>> it doesn't know whether the parameter will be Z% (which can use an index) or
>> %Z (which cannot)). LIKE gives the programmer more freedom than STARTING
>> WITH, but at the cost of limiting the choices available to the optimizer.
>>
>> HTH,
>> Set
>>
>>
>>
>
>


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