May 7, 2010 at 4:34 am
Tom Brown (5/7/2010)
Thanks Paul for the tip on Itzik's book - I'll be visiting Amazon soon I think (or should I wait for the R2 edition?)
AFAIK he's not releasing an R2 update. It is an excellent book - very dense, very technical in places, but if you love that sort of thing...you'll love it 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 7, 2010 at 4:38 am
Hugo Kornelis (5/7/2010)
I would have liked the question (a lot!) if the question had asked for the logical processing order. But the question specificallly asked aboout the sequence internally used, and that threw me off completely.
Hmmm it seems my brain substituted the word 'internally' for 'logically' without conscious intervention - I had to go back and re-read the question to see that you are right about this. You have a good point.
I still think it's a great question though 😛
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 7, 2010 at 4:50 am
Tom Brown (5/7/2010)
I attach my test script, where I was attempting to confirm the validity of my 10-year-old slip of paper, by looking at actual execution plans, and matching up the parts of T-SQL to the plan.I avoided using 'logical' because the execution plan seemed to back up the sequence of evaluation. The plans show data being moved from right to left, joined, filtered, sorted, etc. This is my explanation for the use of internal sequence of evaluation.
Perhaps these plans only represent the logical way a query is handled, and don't really reflect the true sequence of processing? If so then can we ever truely know whats going on.
Hi Tom,
The execution plan represents the actual processing order, you looked at the right thing. But you made two mistakes. One is not looking good enough, the other one is assuming that, if it holds for this example, it always holds.
Run the example again. Check the execution plans. This time, hover your mouse over any of the two table scan operators in the first query, or any of the two clustered index scan operators in the second query. Check what is listed as "Predicate". You will see that the WHERE clause has been pushed up; the Query Optimizer has decided to filter rows based on the WHERE clause because that reduces the query cost without affecting the results.
Of course, there might well be situations where the actual internally used order matches the logical processing order exactly. If there are no cheaper alternatives, that is what SQL Server will do. My point is that this will not always happen (and, in fact, almost never happens).
May 7, 2010 at 4:56 am
Thanks Hugo.
I'd never noticed those 'Predicate' parts of the plan before. Just goes to show, no matter how closely you investigate something, there is always more to learn.
May 7, 2010 at 5:30 am
The key for me getting this right was starting at the end. I knew it had to end DISTINCT, ORDER BY, COUNT -- fortunately there was only one answer with that ending! 🙂
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
May 7, 2010 at 5:41 am
So, now knowing how a query is processed internally, how can we use this knowledge to improve query performance or perhaps troubleshooting unexpected results?
May 7, 2010 at 7:21 am
Here's a link to similar info:
The difference between a logical vs internal implementation of code is moot because we have little control over how the engine determines to run it. It's an interesting academic discussion but does it have any practical use when writing code?
I like the question, though.
May 7, 2010 at 7:51 am
This was a great question. I just added "The Logical Query Processing Diagram" to my toolkit. Thanks for the link.
May 7, 2010 at 8:02 am
Even when I miss the answer I still love learning what the answer is.
Thanks for this gem!
I'll see if I can parley this into better queries in future.
May 7, 2010 at 8:33 am
skjoldtc (5/7/2010)
Here's a link to similar info:The difference between a logical vs internal implementation of code is moot because we have little control over how the engine determines to run it. It's an interesting academic discussion but does it have any practical use when writing code?
I agree with the Bob's comment on the link above and Hugo's explanation here. I would guess the practical thing would be to know these Phases and their order but not overly rely on it for query execution. It would be good for a beginner to know how late in the query execution is DISTINCT, for example, is taken into account and how many rows have already been parsed/processed. But the beginner should also learn to look at execution plans and read the predicates to see the efficiency of the query.
All questions are good questions for me (b'cos I've never submitted one myself) 🙂
May 7, 2010 at 9:50 am
Paul White NZ (5/7/2010)
CirquedeSQLeil (5/6/2010)
Though I got this correct, it appears there is a typo in the correct answer. My version had the last two flip-flopped based on the source document provided in the answer.What are you referring to here Jason?
It must be hard to spot - I can't see anything wrong...:unsure:
Your list has it correct, Itzik's list has it correct. The correct Answer (and explanation) has inverted the order by and top (order by then top as opposed to the document which says top then order by).
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
May 7, 2010 at 9:51 am
Tom Brown (5/7/2010)
Thank-you all.I've had this data for 10 years now, maybe I copied it from someone's SQL 2000 book - or possibly it came from an SQL 7 course I did (in 1999!), and I found it really useful, But I was slightly unsure that it was still valid, particularly as I had some difficulty in finding a concrete reference.
Thanks Paul for the tip on Itzik's book - I'll be visiting Amazon soon I think (or should I wait for the R2 edition?)
Your link to Pinal Daves website is an excerpt taken from Itziks book for 2008.
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
May 7, 2010 at 9:58 am
skjoldtc (5/7/2010)
Here's a link to similar info:
This is the alternate blog site for Pinal Dave. Both link to the same poster for download.
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
May 7, 2010 at 10:06 am
I actually thought this was a great question, because it made me think. I looked at my choices and said to myself, "okay, let's say I'm processing this. What order would make the most sense?"
I looked at the choices, thought about what seemed most logically correct, and made a somewhat educated guess. Lo and behold, I got it right!
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
May 7, 2010 at 11:51 am
CirquedeSQLeil (5/7/2010)
Paul White NZ (5/7/2010)
CirquedeSQLeil (5/6/2010)
Though I got this correct, it appears there is a typo in the correct answer. My version had the last two flip-flopped based on the source document provided in the answer.What are you referring to here Jason?
It must be hard to spot - I can't see anything wrong...:unsure:
Your list has it correct, Itzik's list has it correct. The correct Answer (and explanation) has inverted the order by and top (order by then top as opposed to the document which says top then order by).
Hi Jason,
It's not my habit to disagree with Itzik, but in this case I have to make an exception.
Lemme ask you a simple question - in a query that has a TOP and an ORDER BY clause, how exactly should SQL Server process the TOP without sorting the rows first? *IF* Microsoft had decided to use a seperate clause to define the order for the TOP instead of overloading the ORDER BY clause (for instance TOP ... OVER (ORDER BY column)), then I would agree with TOP first (which includes ordering by the OVER (ORDER BY) specificion of the TOP), ORDER BY (for presentation) last. But as it stands, it simply does not make sense to do an ORDER BY after a TOP that has already included the same sort operation.
I disagree with the list and Itzik, and I agree with the order as presented in the question.
Note that, for most clauses, the logical processing order is defined by the ANSI standards. However, the TOP clause is proprietary; ANSI does not support it. And Microsoft never officially announced the logical order, so this is all speculation. Both on Itzik's and on my part.
Viewing 15 posts - 16 through 30 (of 42 total)
You must be logged in to reply to this topic. Login to reply