The Right Kind Of Join

  • Paul White

    SSC Guru

    Points: 150442

    Comments posted to this topic are about the item The Right Kind Of Join

  • Ron McCullough

    SSC Guru

    Points: 63877

    Very nice ...

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • SQLRNNR

    SSC Guru

    Points: 281252

    Nice question

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • kapfundestanley

    SSCertifiable

    Points: 5632

    Good question.

    “When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris

  • This was removed by the editor as SPAM

  • adb2303

    SSCertifiable

    Points: 7586

    Pure guess work from me...

  • Andrew Watson-478275

    SSCarpal Tunnel

    Points: 4652

    There would have been less guesswork involved if the full error message had been given instead of just an extract:

    Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Great question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • CoolCodeShare

    SSCrazy

    Points: 2584

    Nice question.

  • Paul White

    SSC Guru

    Points: 150442

    Andrew Watson-478275 (8/24/2011)


    There would have been less guesswork involved if the full error message had been given instead of just an extract

    You can get the full error text without running the query by checking sys.messages:

    SELECT

    m.[text]

    FROM sys.messages AS m

    WHERE

    m.message_id = 8622

    AND m.language_id = 1033

    I'm comfortable that the question and answers had the right level of detail to make it interesting (it is a 2 point question after all).

  • Kwex

    Default port

    Points: 1407

    Nice! Learnt something new today about hint limitations on RIGHT joins 🙂

  • Andrew Watson-478275

    SSCarpal Tunnel

    Points: 4652

    Paul - that's exactly what I did when BOL didn't quite give enough information to answer.

    I agree with your view on the 2 point question; sorry for doubting you.

    (In real life, they're all 2+ point questions - you never get it handed on a plate).

  • TomThomson

    SSC Guru

    Points: 104773

    Good question.

    Isn't the inability to apply the loop hint to a right join a bit bizarre though? Although it seems pretty obvious why this happens, there is in fact something odd about the exclusion of right join. After all

    A right join B on A.x=B.x

    is identical in meaning to

    B left join A on A.x=B.x

    and I can apply a loop hint to the second. So I ought to be able to apply it to the first, unless the optimiser interprets the loop hint as saying "use a nested loop join where the outinner loop is on the right hand component of the join, that is the table which occurs second in the text of the query" rather than just "use a nested loop join (choosing whichever component the optimiser thinks best for the outer loop)". The optimiser has great freedom generally, and making the loop hint specify more of a constraint on the optimiser than it really needs is out of tune with that usual freedom.

    edit: "outer" should be "inner", see text struck and replaced above

    Tom

  • Paul White

    SSC Guru

    Points: 150442

    Tom.Thomson (8/24/2011)


    Isn't the inability to apply the loop hint to a right join a bit bizarre though?

    I would normally answer this, but today's question is one of three... 😉

    Question 2 will appear next week (1 September), and question 3 a week later.

  • Paul White

    SSC Guru

    Points: 150442

    Andrew Watson-478275 (8/24/2011)


    Paul - that's exactly what I did when BOL didn't quite give enough information to answer. I agree with your view on the 2 point question...

    No worries 🙂

Viewing 15 posts - 1 through 15 (of 29 total)

You must be logged in to reply to this topic. Login to reply