Hello Farias and Sean,

thanks for your work!

I tested the select you posted and it has exactly the described
problem at execution time. Your right that the problem could reduced
to this select.

Plan:
PLAN JOIN (IWADISOBJE_1 INDEX (IDXIWADISOBJECT1), WORD_1 INDEX
(IDXWORD3))

Prepare time: 1 ms
Execution time: 490640 ms
Fetch time: 2319 ms


- how many entries are in the entire IwadisObject table
77225
- how many entries have IwadisObject BOLD_TYPE IN (122)
52281
- how many entries are in the whole Word table
9666470
- how many Word table entries have Word_1.attribute = 'NAME'
332000
- how many Word table entries have Word_1.data LIKE 'ANFRAGE%'
1715
- how many Word table entries have Word_1.attribute = 'NAME' *AND*
Word_1.data LIKE 'ANFRAGE%'
295


To get the above record-count I did a select count and all select
runs in milliseconds even the select count(data) from word where data
like 'ANFRAGE%'. Firebird uses the index for that select to so index
for LIKE ist used but not in the 'problem'-select from the
beginning....

Im out at customer today and will be back in 12 hours and could test
more if you need more informations.

Thanks

Stefan


--- In firebird-support@yahoogroups.com, "Leyne, Sean" <Sean@...>
wrote:

>
>
> > >This is the single most complicated SQL statement that I have

seen

> > > through my 14 years of using/supporting IB/FB!!!
> >
> > Layne, I *think* that for the problem being, we can reduce the

SQL to

> >
> > SELECT IwadisObje_1.BOLD_ID, IwadisObje_1.BOLD_TYPE
> > FROM IwadisObject IwadisObje_1
> > JOIN Word Word_1 ON (IwadisObje_1.BOLD_ID = Word_1.indexedObject)
> > WHERE ((Word_1.attribute = 'NAME') and (Word_1.data

LIKE 'ANFRAGE%'))

> > AND (IwadisObje_1.BOLD_TYPE IN (122))
>
> I agree this SQL is a good test for the problem at hand.
>
>
> > Stefan, could you test this against your data and confirm if it
> reproduces
> > the same bad behavior?
>
> I'd also like to know:
>
> - how many entries are in the entire IwadisObject table
>
> - how many entries have IwadisObject BOLD_TYPE IN (122)
>
> - how many entries are in the whole Word table
>
> - how many Word table entries have Word_1.attribute = 'NAME'
>
> - how many Word table entries have Word_1.data LIKE 'ANFRAGE%'
>
> - how many Word table entries have Word_1.attribute = 'NAME' *AND*
> Word_1.data LIKE 'ANFRAGE%'
>
>
> Sean
>