Slow SELECT .. IN (singelton SELECT..) ? 1

[exys]

I would think the following two queries should be processed the same, but the first one takes over a second, while the second takes a few milliseconds.

SELECT * FROM tblData WHERE idA IN (SELECT idB FROM tblIndex WHERE idC=1234);

SELECT * FROM tblData WHERE idA = (SELECT idB FROM tblIndex WHERE idC=1234);

idA is primary key and unique.
idB is foreign key ref. idA.
idC is primary key and unique.

tblData has about 30000 records, tblIndex has about 5000 records..

The (SELECT idB...) always returns a singleton since idC is primary key, so they should be the same, right?

Why does IN take so long ?
Running IB 6.0.2.0 on Win2000.

All ideas much appreciated.
</Anders>

 

[pfournier]

That's because IB6 will run the subselect once for each row in table tblData to verify the IN clause, but (I believe) only once to verify the = clause.

It is a problem with the optimizer.

 

[exys]

Thanks for the reply.

After a lot of testing and searching I started to suspect that it had something to do with the optimizer like you say, and I even tried the same thing under the latest Firebird release (1.5) with the same result.

Today I found out that the issue is logged in the Firebird Sourceforge bugtrack system, but there are no comments or solutions..

http://sourceforge.net/tracker/index.php?func=detail&aid=447002&group_id=9028&atid=109028

The workaround presented in the description did not work though.
It's logged with low priority but to me it's a pretty major flaw...

I've tried to work around it with a PLAN, but haven't figured out how yet - I'd appreciate any hits

</A>

 

[pfournier]

Why not use the = version as it does what you want?

 

[exys]

The = version works for this particular query, but I have other queries where the sub-SELECT returns more than one result.
For those cases I haven't found a workaround yet.

Tried it on MSSQL - apparently that optimizer does the right thing.
Does anyone know if this is fixed in the commercial version of Interbase (7.1)?

 

[pfournier]

I thought it was fixed in FB1.5... I don't know about Interbase 7.x.

If you show us your queries, we can help reformulate them... Here is one way that could help:

SELECT D.* FROM tblData D
INNER JOIN tblIndex I
ON D.idA = I.idB
AND I.idC = 1234;

 

[exys]

That worked perfectly !
Thanks a bunch !

Even with &quot;...AND I.idC IN (1234,1235,1236)&quot; it works as fast as you would expect.

Thanks again for a good workaround !
</A>