locked
SQL doubt RRS feed

  • Question

  • 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

    Wednesday, February 22, 2012 10:26 AM

Answers

All replies