×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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>

RE: Slow SELECT .. IN (singelton SELECT..) ?

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.

RE: Slow SELECT .. IN (singelton SELECT..) ?

(OP)
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&ai...
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>

RE: Slow SELECT .. IN (singelton SELECT..) ?

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

RE: Slow SELECT .. IN (singelton SELECT..) ?

(OP)
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)?

RE: Slow SELECT .. IN (singelton SELECT..) ?

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;

RE: Slow SELECT .. IN (singelton SELECT..) ?

(OP)
That worked perfectly !
Thanks a bunch !

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

Thanks again for a good workaround !
</A>

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close