select a.* from TABLE_A a
left join TABLE_B b on a.TABLE_A_ID = b.TABLE_A_ID
and b.COL_B IN ('something', 'something2', 'something3', 'something4')
where b.PRIMARY_KEY_FIELD is null

Though this will return any records of TABLE_A that has <null> in TABLE_A_ID, which your original statement didn't return, and has to put TABLE_A before TABLE_B in your plan, so translating NOT IN won't show the same speed improvement as IN. Also, if b.COL_B is less selective than b.TABLE_A_ID, you may find that using an index for COL_B slows down the query. You could check whether the above is slower or quicker than

select a.* from TABLE_A a
left join TABLE_B b on a.TABLE_A_ID = b.TABLE_A_ID
and b.COL_B||'' IN ('something', 'something2', 'something3', 'something4')
where b.PRIMARY_KEY_FIELD is null

Alternatively, you could use

select a.* from TABLE_A a
where not exists(select * from TABLE_B b
where a.TABLE_A_ID = b.TABLE_A_ID
and b.COL_B||'' IN ('something', 'something2', 'something3', 'something4'))

which I would expect to be equally quick as a left join (but I'm no expert on Fb 2).

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of navneet77
Sent: 13. februar 2008 13:55
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: select IN subquery with lots of OR in subquery is very slow

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
>

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links