Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

performance Expand / Collapse
Author
Message
Posted Thursday, February 18, 2010 10:39 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, July 11, 2014 8:22 AM
Points: 742, Visits: 1,053
Hi,

I have a dought about performance in t-sql.
What is faster:


Make a "inner join on x=Y" or make "select ..... where x not in (select y)"

Is it faster to make a select where value not in table or make a inner join and see the result?


Thank you
Post #868256
Posted Thursday, February 18, 2010 10:48 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, February 15, 2012 8:42 PM
Points: 63, Visits: 121
"inner join on x=Y" should be faster.
Post #868271
Posted Thursday, February 18, 2010 10:51 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, July 11, 2014 8:22 AM
Points: 742, Visits: 1,053
Even if the inner join as to return millions of rows?
Post #868278
Posted Thursday, February 18, 2010 10:56 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:54 AM
Points: 42,440, Visits: 35,494
river1 (2/18/2010)

Make a "inner join on x=Y" or make "select ..... where x not in (select y)"


Unless I'm missing something, those will not return the same result.

Table1 INNER JOIN Table2 ON X = y
and
WHERE Table1.X IN (SELECT Y from Table2)
are equivalent, but INNER JOIN and NOT IN do not do the same thing.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #868285
Posted Thursday, February 18, 2010 11:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 7:35 AM
Points: 33, Visits: 442
GilaMonster (2/18/2010)
river1 (2/18/2010)

Make a "inner join on x=Y" or make "select ..... where x not in (select y)"


Unless I'm missing something, those will not return the same result.



I was thinking the same thing. The 'inner join' and 'not in' logic are antonyms in this case.


Karl Lambert
SQL Server Database Administration
Business Intelligence Development
Post #868308
Posted Friday, February 19, 2010 2:41 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, July 11, 2014 8:22 AM
Points: 742, Visits: 1,053
Suppose this:

I want to select all the names and addresses of people who have their name in table1 and table2. i can achieve this, for instance, with this two select statements:


1) select a.name, a.address from teste1 as a inner join test2 as b on a.name=b.name


2) select name,address from table1 where name in (select name from table2)


Wich of this two querys is faster? and why?


Thank you
Post #868858
Posted Friday, February 19, 2010 2:45 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 26, 2011 8:46 AM
Points: 197, Visits: 1,274
Obviously first one
because in first query both the table will be scanned only for condition a.name=b.name

In second query whole table2 will be scanned then it will be compared with table1.

Also it depends on the size of the tables too


Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
Post #868861
Posted Friday, February 19, 2010 2:53 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:54 AM
Points: 42,440, Visits: 35,494
sanketahir1985 (2/19/2010)
Obviously first one
because in first query both the table will be scanned only for condition a.name=b.name

In second query whole table2 will be scanned then it will be compared with table1.


Are you sure about that?

The query optimiser is not stupid. It is capable of recognising that the IN is a condition, just like the inner join, and optimising appropriately. In the absence of other conditions, it's likely that both tables will be scanned regardless (unless one of them is very, very small)



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #868867
Posted Friday, February 19, 2010 2:54 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:54 AM
Points: 42,440, Visits: 35,494
Take a look at this blog post. http://sqlinthewild.co.za/index.php/2010/01/12/in-vs-inner-join/


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #868868
Posted Friday, February 19, 2010 5:02 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 26, 2011 8:46 AM
Points: 197, Visits: 1,274
GilaMonster (2/19/2010)
sanketahir1985 (2/19/2010)
Obviously first one
because in first query both the table will be scanned only for condition a.name=b.name

In second query whole table2 will be scanned then it will be compared with table1.


Are you sure about that?

The query optimiser is not stupid. It is capable of recognising that the IN is a condition, just like the inner join, and optimising appropriately. In the absence of other conditions, it's likely that both tables will be scanned regardless (unless one of them is very, very small)


thats why i said it depends on the table size too


Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
Post #868950
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse