performance of IN clause

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

performance of IN clause

JayeshLalwani
Is there  a significant differrence in how a IN clause performs when compared to a JOIN?

Let's say I have 2 tables, A and B/ B has 50million rows and A has 1 million

Will this query?
Select * from A where join_key in (Select join_key from B)
perform much worse than
 Select * from A
INNER join on A.join_key = B.join_key

Will the first query always trigger a broadcast of B?


The information contained in this e-mail is confidential and/or proprietary to Capital One and/or its affiliates and may only be used solely in performance of work or services for Capital One. The information transmitted herewith is intended only for use by the individual or entity to which it is addressed. If the reader of this message is not the intended recipient, you are hereby notified that any review, retransmission, dissemination, distribution, copying or other use of, or taking of any action in reliance upon this information is strictly prohibited. If you have received this communication in error, please contact the sender and delete the material from your computer.

Reply | Threaded
Open this post in threaded view
|

Re: performance of IN clause

Silvio Fiorito

Have you run explain for each query? If you look at the physical query plan it’s most likely the same. If the inner-query/join-table is small enough it should end up as a broadcast join.

 

From: Jayesh Lalwani <[hidden email]>
Date: Wednesday, October 17, 2018 at 5:03 PM
To: "[hidden email]" <[hidden email]>
Subject: performance of IN clause

 

Is there  a significant differrence in how a IN clause performs when compared to a JOIN?

 

Let's say I have 2 tables, A and B/ B has 50million rows and A has 1 million

 

Will this query?

Select * from A where join_key in (Select join_key from B)

perform much worse than

 Select * from A

INNER join on A.join_key = B.join_key

 

Will the first query always trigger a broadcast of B?

 


The information contained in this e-mail is confidential and/or proprietary to Capital One and/or its affiliates and may only be used solely in performance of work or services for Capital One. The information transmitted herewith is intended only for use by the individual or entity to which it is addressed. If the reader of this message is not the intended recipient, you are hereby notified that any review, retransmission, dissemination, distribution, copying or other use of, or taking of any action in reliance upon this information is strictly prohibited. If you have received this communication in error, please contact the sender and delete the material from your computer.