Hi All,
I have a doubt in my mind and i don't know exactly how much details should I post here.
I have a temp table with two columns and 100 rows
#t : fsnumber varchar(32) and PrimaryAirport char(3),clustered index on fsnumber
Here is my query
select t.fsnumber,t.PrimaryAirport,pe.emailaddress
from #t t
join <linkedserver>.<dbname>.dbo.Personcustomerprogram pcp on t.fsnumber=pcp.programnumber
join <linkedserver>.<dbname>.dbo.Personemail pe on pcp.personID=pe.personid
No of rows in PersonEmail is 5051852 with PersonID as clustered index
No of rows in Personcustomerprogram is 4497608 with PersonID and programnumber as clustered index order (personID,ProgramNumber)
What I see is that the above plan first joined temp table with Personcustomerprogram(merger join) and then join the obtained result set with PersonEmail(again merge join many-to-many).I am somewhat satisfied with this join order but when i run the linked
query join individually on its local server I get a hash join on PersonEmail and Personcustomerprogram so changing the query to below , I get an improved plan
select t.fsnumber,t.PrimaryAirport,x.emailaddress
from #t t
join
(select pe.emailAddress,pcp.programnumber as fsnumber from
<linkedserver>.<dbname>.dbo.Personcustomerprogram pcp
inner hash JOIN <linkedserver>.<dbname>.dbo.Personemail pe on pcp.personID=pe.personid
)x on t.fsnumber=x.fsnumber
So my question is whether it is good to specify a join hint here or should I rely on query Optimizer keeping in mind that I might be wrong somewhere??
Or how query optimizer optimizes the linked server queries??
Thanks and regards, Rishabh , Microsoft Community Contributor
