Thanks I will try that. Is there any way I can change a simillar NOT
IN query. Like

select * from TABLE_A where TABLE_A_ID NOT IN (select TABLE_A_ID from
TABLE_B where TABLE_B.COL_B = 'something' or TABLE_B.COL_B
= 'something2' or TABLE_B.COL_B = 'something3' or TABLE_B.COL_B
= 'something4'...)


Navneet

--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...>
wrote:

>
> At 09:30 PM 13/02/2008, you wrote:
> >I am using the latest version firebird server on windows. I have a
> >query like
> >
> >select * from TABLE_A where TABLE_A_ID in (select TABLE_A_ID from
> >TABLE_B where TABLE_B.COL_B = 'something' or TABLE_B.COL_B
> >= 'something2' or TABLE_B.COL_B = 'something3' or TABLE_B.COL_B
> >= 'something4'...)
> >
> >Both TABLE_A_ID and COL_B has an index. TABLE_B has about 26000

records

> >and the query statistics says more than 900000 index reads for

TABLE_B

> >and the query takes about a minute to complete.
> >
> >The more OR i have in the subquery the longer it takes.
> >
> >If I run the subquery seperately it returns almost instantly. Is

this

> >how it is suppose to work.
>
> Yes. This is a case where multiple consecutive correlated queries

are almost certainly a lot more costly than a clean inner join:

>
> select a.* from TABLE_A a
> join TABLE_B b
> on
> a.TABLE_A_ID = b.TABLE_A_ID
> where b.COL_B in

('something', 'something2','something3','something4'...)

>
> You'll still get an indexed read (an OR search) on each member of

the IN() list but not with all the repetition that the correlated
subqueries require. (b.COL_B should be indexed, of course...)

>
> ./heLen
>