performance

  • 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

  • "inner join on x=Y" should be faster.

  • Even if the inner join as to return millions of rows?

  • 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, MVP, M.Sc (Comp Sci)
    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
  • 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

  • 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

  • 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......

  • 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, MVP, M.Sc (Comp Sci)
    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
  • 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, MVP, M.Sc (Comp Sci)
    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
  • 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......

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply