November 20, 2014 at 10:20 pm
Hi There,
Please go through the following query, and help me to understand which one is efficient and how?
Query 1:
select a.*,b.* from table1 a join table2 b on a.colA = b.colA
where a.ColB = 'Somevalue'
Query 2:
select a.*,b.* from (select * from table1 where ColB = 'Somevalue' ) a
join table2 b on a.colA = b.colA
Thanks in advance
November 21, 2014 at 1:26 am
They're semantically equivalent and the optimizer will likely convert the second form to the first form.
There is no performance benefit in choosing one.
I suggest that you write your queries the way you feel more comfortable with and then start investigating smarter rewrites once it's proven that performance is a concern.
-- Gianluca Sartori
November 21, 2014 at 1:27 am
It's possible that both queries have the exact some execution plan.
In theory query 2 could be faster because you join against a smaller table. However, since it is a resultset from a query it is not indexed.
However, the query optimizer will most of the time be smart enough to realize what is going on and optimize query 1 where it filters table B when it is reading data.
Best method is to just execute the queries and compare execution plans.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 22, 2014 at 9:12 am
And compare execution times and reads.
But I would suspect they'll resolve out to the same plan during the simplification phase.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply