T-SQL Performance question

  • Which will execute faster:

    SELECT

    FIELD1,

    SUB.FIELD2

    FROM TABLE LEFT OUTER JOIN (SELECT FIELD2...FROM...) ON ...

    vs.

    SELECT

    FIELD1,

    (SELECT FIELD2...FROM...WHERE...) AS FIELD2

     

     

  • You are being very vage on description but I would usually say The First!

     


    * Noel

  • Same here, the left join will have less work to do than the subquery... or at least do it in a more efficient way.

  • To clarify, the first uses subqueries, e.g., subtables and the second uses an in-line select statement in the select clause.

    Essentially I'm getting lookup descriptions for values of fields in the 'main' table.

  • Really?  It looks to me like your LEFT JOIN query uses a derived table and your other query uses a subquery.  I can't really tell what you are trying to accomplish without DDL, but the LEFT JOIN will generally be more efficient than the subquery because the subquery is executed once for each row in the outer table.  Also, if you use a WHERE clause that filters rows in the outer table, that will reduce the size of the result set that must be examined.

    There is no "i" in team, but idiot has two.
  • Let's just say, it all depends. Look at the execution plan and test both. Sometimes one way works best, sometimes the other.

  • Depeneding in the size of the input the first, the second gets slower and slower compared to the first as the number of items in the WHERE gets bigger (on SQL server that is, strangly Access is still reasonably quick)

  • To all respondees, I've run the execution plan, statistics, etc., and have the answer I needed, no further responses needed.  Thank you all for your time and responses.

  • Well whats the answer then?

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

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