Suppose I have two large tables A and B, I want to perform a JOIN on column x of A and B (suppose they use same name) and finally select x and y from table B. Would it be faster if I just pre-select x of A and x, y of B and do JOIN on these reduced tables?
-
What do you mean by "pre select".Coding Flow– Coding Flow2011-09-08 14:50:23 +00:00Commented Sep 8, 2011 at 14:50
Add a comment
|
3 Answers
I assume your question is when you are writing
select B.x, B.y
from A
join B on B.x = A.x
is it faster to select
select B2.x, B2.y
from (select x from A) A2
join (select x,y from B) B2 on B2.x = A2.x
The short answer is: No. For example, if you have a covering index (x,y) on B, the query will use that index instead of selecting from the full row. If you don't have a covering index, you're just wasting memory anyways if you're hoping to write the two columns to a temporary area before doing the join. In this particular case, the database will almost always optimize the two queries to the exact same execution plan anyways.
Comments
I doubt it. You're just introducing an extra unnecessary step by doing that. You are only comparing the keys as specified in the parameters to Join() regardless of how many other properties are in your objects.
Comments
No. You would do useless projections on A. If you use the "standard" query, you'll only do projections on AxB, while predoing the projection on A you'll obtain A+AxB. If we consider that A <= B (because B has a reference to A), then it's B projections vs A+B projections.
