August 23, 2011 at 9:48 pm
Comments posted to this topic are about the item The Right Kind Of Join
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 23, 2011 at 9:48 pm
August 23, 2011 at 10:55 pm
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
August 24, 2011 at 12:36 am
Good question.
“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris
August 24, 2011 at 1:39 am
This was removed by the editor as SPAM
August 24, 2011 at 2:41 am
Pure guess work from me...
August 24, 2011 at 2:49 am
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.
August 24, 2011 at 3:10 am
Great question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 24, 2011 at 3:17 am
Nice question.
August 24, 2011 at 3:30 am
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).
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 24, 2011 at 3:44 am
Nice! Learnt something new today about hint limitations on RIGHT joins 🙂
August 24, 2011 at 3:45 am
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).
August 24, 2011 at 3:47 am
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
August 24, 2011 at 4:34 am
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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 24, 2011 at 4:36 am
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 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply