SQL Server Performance

Which Query is better ?

Discussion in 'T-SQL Performance Tuning for Developers' started by yhchan2005, Aug 13, 2007.

  1. yhchan2005 Member

    Hi, after check the execution plan, query are almost same in terms of query cost. Just to check whether any other different
    between below quey ? and which way is best way ?

    Select Field1, Field2 from TableA
    Where Field3 in ( Select Field3 from TableB
    Where Field4 = 'XXXX'
    and Field5 = 'YYY' and Field6 = 'ZZZZ')
    Select Field1, Field2 from TableA
    Inner Join ( Select Field3 from TableB
    Where Field4 = 'XXXX'
    and Field5 = 'YYY' and Field6 = 'ZZZZ') as Book
    On Book.Field3 = TableA.Field3
    Select Field1, Field2 from TableA
    Where Field3 in ( Select Field3 from TableB
    Where TableB.Field4 = TableA.Field4
    and Field5 = 'YYY' and Field6 = 'ZZZZ')
    and Field4 = 'XXXX'
  2. martins New Member

    You know the favourite saying "it all depends"...well in this case it is exactly that. Depending on the size of your tables, indexes, etc.
    In my opinion the first or last query is best from a coding perspective, but that is just because I don't like derived tables. Test all 3 queries and let the execution plans and execution times lead you in your decision.
  3. satya Moderator

    Sometimes querying data is not that simple and there may be the need to create temporary tables or views to predefine how the data should look prior to its final output. I would also encourage to experiment with using derived tables, views and temporary tables (if your time permits) to see what yields better performance results.

Share This Page