March 8, 2005 at 11:29 am
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
March 8, 2005 at 11:33 am
You are being very vage on description but I would usually say The First!
* Noel
March 8, 2005 at 11:47 am
Same here, the left join will have less work to do than the subquery... or at least do it in a more efficient way.
March 8, 2005 at 11:57 am
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.
March 8, 2005 at 5:32 pm
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.
March 9, 2005 at 6:15 am
Let's just say, it all depends. Look at the execution plan and test both. Sometimes one way works best, sometimes the other.
March 10, 2005 at 8:23 am
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)
March 10, 2005 at 9:34 am
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.
March 14, 2005 at 3:36 am
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