January 13, 2012 at 7:00 am
Sergiy (1/13/2012)
SQL Kiwi (1/13/2012)
You claim that opening handlers IS execution. OK, let's pretend for a minute we've agreed on that.
Yes, code execution (as shown in the stack trace from the debugger) is indeed execution. The code that first executes is the code that executes first. Consider that an earlier stack trace shows this code is executing:
sqlservr!CQScanStreamAggregateNew::Opensqlservr!CXStmtQuery::SetupQueryScanAndExpression
sqlservr!CXStmtQuery::InitForExecute
There's really nothing clearer than that to show the stream aggregate code executes first.
It says "InitForExecute", I cannot see any execution happening here.
A call stack is read bottom to top. The routine sqlservr!CQScanStreamAggregateNew::Open is currently executing on the processor. CQScanStreamAggregateNew::Open is the Stream Aggregate initializing, the next step will be to call its child operator so i can initialize itself and so on. Eventually (after many many intermediate steps), we reach the state I showed earlier:
sqlservr!CQScanTableScanNew::GetRow
sqlservr!CQScanNLJoinTrivialNew::GetRow
sqlservr!CQScanCountStarNew::GetRowHelper
sqlservr!CQScanStreamAggregateNew::GetCalculatedRow
sqlservr!CXStmtQuery::SetupQueryScanAndExpression
sqlservr!CXStmtQuery::InitForExecute
This shows the Stream Aggregate calculating COUNT(*), calling the Nested Loops Join iterator, which is calling the Table Scan, which is about to retrieve a row via the Storage Engine.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 13, 2012 at 7:01 am
Sergiy (1/13/2012)
You say - pouring is coming 1st, because before counter can start counting you must open the cap and put the nozzle into the hole. All right, but it's just a preparation for pouring, no pouring is happening before the pump starts pumping and counter actually starts counting.
I say that an executable plan is exactly that: executable code. In the executable code for this query, code that is part of the Stream Aggregate class executes first. This is my reasoning for saying the Stream Aggregate executes first. Enjoy your weekend.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 13, 2012 at 4:02 pm
SQL Kiwi (1/13/2012)
Sergiy (1/13/2012)
You say - pouring is coming 1st, because before counter can start counting you must open the cap and put the nozzle into the hole. All right, but it's just a preparation for pouring, no pouring is happening before the pump starts pumping and counter actually starts counting.I say that an executable plan is exactly that: executable code. In the executable code for this query, code that is part of the Stream Aggregate class executes first. This is my reasoning for saying the Stream Aggregate executes first. Enjoy your weekend.
And I say that pumping fuel is exactly the same thing as "Get Row", and counting liters (sorry, American fellows, we count liters here) is running a stream aggregate. Aggregation goes to closing mode when the "Get Row" method is closed (sorry, when the pump is stopped).
Analogy is perfect because the counter is initiated BEFORE pumping started, and then aggregates amounts streamed by the pump to the counter.
Now, your answer on the QoD states:
Measuring of pumped fuel amount is executed before pumping fuel.
How stupid does it sound?
Best regards to all MVPs and authors of BOL.
_____________
Code for TallyGenerator
January 13, 2012 at 7:55 pm
Sergiy (1/13/2012)
How stupid does it sound?
I don't find the analogy useful at all, to be honest. Instead, imagine four people. One reads rows from table A, one reads rows from table B, one attempts to match rows provided by persons A & B, and the fourth person (D) counts the rows received from person C.
Now you would argue that person A (or possibly person B, in your seek-comes-first example :laugh:) starts work first. But, how does person A know that he or she should start work? Well, person C (the join) asked person A for a row. And person C? He or she was asked for a row by person D (the counter). To get my result, who should I ask? Clearly, the answer is person D.
So, person D (the stream aggregate) starts work first.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 15, 2012 at 3:33 pm
SQL Kiwi (1/13/2012)
Sergiy (1/13/2012)
How stupid does it sound?I don't find the analogy useful at all, to be honest.
Really?
What is off in this analogy?
Now you would argue that person A starts work first.
That was not the question.
I could not see and mention of "starts" in your QoD.
So, you are now are answering a different question.
BTW, if we are talking about "starts" then the one which starts first would be that operator you cut-off from the execution plan.
Where the outcome of the stream aggregation goes to?
_____________
Code for TallyGenerator
January 15, 2012 at 3:38 pm
SQL Kiwi (1/13/2012)
So, person D (the stream aggregate) starts work first.
"Executes" has more in it then "starts execution".
Person D is still in process of execution when all others are done.
So, person D does the work last as well.
So, the same process is the first and the last amongst all the others.
Does it make much sence?
_____________
Code for TallyGenerator
January 15, 2012 at 3:53 pm
SQL Kiwi (1/13/2012)
I say that an executable plan is exactly that: executable code.
Staying within the terms of executable codes:
- Cursor loop executes before its first row is fetched.
- Main routine executes before any of its subroutines.
The fact is - the question should be not about what is executed first, but what is dependency relation between the routines.
SELECT is a main routine, which initiates subroutine Stream Aggregate, which initiates subroutine "Loop", and so on.
And as everywhere in executable codes, main routine is executed before, while and after execution of any of subroutines.
_____________
Code for TallyGenerator
January 15, 2012 at 5:06 pm
Another loose point I found in this article, which possibly leads to a wrong understanding:
http://bradsruminations.blogspot.com/2010/11/second-in-life-of-query-operator.html
Here is my test query:
DECLARE @a TABLE (a varchar(20) NOT NULL)
DECLARE @b-2 TABLE (b varchar(20) NOT NULL UNIQUE)
INSERT @a VALUES ('a')
INSERT @a VALUES (1)
INSERT @b-2 VALUES (1)
INSERT @a VALUES (3)
INSERT @b-2 VALUES (3)
INSERT @a VALUES (5)
INSERT @b-2 VALUES (5)
INSERT @a VALUES (7)
INSERT @b-2 VALUES (7)
SELECT TOP 2 *
FROM @a A
JOIN @b-2 B ON B.b = A.a
Now, we get back 2 rows, but how many times insex seek on @b-2 is executed?
Answer is 3.
But there are 4 matches, and only 2 rows are required.
So, why 3?
Because 1st seek for 'a' does not bring any result and is eliminated by JOIN (Nested Loop).
So, TOP contunues to request rows until it's fed up.
As soon as 2 rows have successfully passed JOIN and reached the TOP it stops the execution.
But it cannot happen until the rows are actually submitted upstairs by Scan, Seek and Loop.
This example clearly shows that execution of TOP is dependant of the outcome of Loop, which is dependant on the outcome of Scan and Seek.
It just cannot do its job until Scan and Seek have completed their tasks and physically submitted the sufficient number of rows to the buffer.
That's why it's totally wrong to say that operator TOP executes first.
If you wish, parts of it, some "Open" and "Init" statements.
But the operator as a whole executes last, as it may do what it's told to do only after other operators supplied some data to it (which means been executed, if I'm not mistaken).
🙂
I think the question as it was asked is totally misleading.
It should be about priority of control, not about order of execution.
_____________
Code for TallyGenerator
January 15, 2012 at 7:41 pm
"It should be about priority of control, not about order of execution."
Here you have the slickiest of words, "should."
Apparently, it is what it is; for the time being, please try to live with that.
If you can make a case this should be changed, make it.
January 15, 2012 at 10:12 pm
Sergiy (1/15/2012)
Really? What is off in this analogy?
Really, yes. Does the fuel counter reset before any fuel starts being delivered? Of course. The analogy doesn't add much in my opinion. You are welcome to disagree and hold a different opinion naturally.
That was not the question.
Neither was it about dispensing fuel. I was simply trying to help you by providing a better analogy.
BTW, if we are talking about "starts" then the one which starts first would be that operator you cut-off from the execution plan. Where the outcome of the stream aggregation goes to?
Well the full query was provided, so you can see for yourself. I simply cut the SELECT icon off for display purposes. The result of the aggregation is returned to the caller as the result, of course.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 15, 2012 at 10:16 pm
Sergiy (1/15/2012)
"Executes" has more in it then "starts execution". Person D is still in process of execution when all others are done. So, person D does the work last as well.
This semantic point has been discussed several times already in this thread, and you admitted earlier that the semantic was not your problem; now it seems it is. The vast majority of people are happy with the phrasing used. I don't care much that you read 'executes' as 'finishes executing' where most people read it as 'starts executing'. It's only a question of the day: it's your choice whether to learn from it or not.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 15, 2012 at 10:19 pm
Sergiy (1/15/2012)
The fact is - the question should be not about what is executed first, but what is dependency relation between the routines.
So write your own question about that. My question is about the order of code execution. Simple.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 15, 2012 at 10:23 pm
SQL Kiwi (1/15/2012)
Sergiy (1/15/2012)
Really? What is off in this analogy?Really, yes. Does the fuel counter reset before any fuel starts being delivered? Of course.
So?
Both fuel counter and row counter are reset before the counted substance is being delivered.
Just proves the analogy is correct.
The analogy doesn't add much in my opinion.
If a fact does not fit your theory ditch the fact.
🙂
_____________
Code for TallyGenerator
January 15, 2012 at 10:28 pm
Sergiy (1/15/2012)
So, TOP contunues to request rows until it's fed up.
I know how it feels 😉
That's why it's totally wrong to say that operator TOP executes first. If you wish, parts of it, some "Open" and "Init" statements. But the operator as a whole executes last, as it may do what it's told to do only after other operators supplied some data to it (which means been executed, if I'm not mistaken).think the question as it was asked is totally misleading.
It should be about priority of control, not about order of execution.
This semantic point has been discussed several times already in this thread, and you admitted earlier that the semantic was not your problem; now it seems it is. The vast majority of people are happy with the phrasing used. I don't care much that you read 'executes' as 'finishes executing' where most people read it as 'starts executing'. It's only a question of the day: it's your choice whether to learn from it or not.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 15, 2012 at 10:30 pm
SQL Kiwi (1/15/2012)
The vast majority of people are happy with the phrasing used.
How happy are those people about this one:
Cursor loop executes before the first row of the cursor is fetched?
Or
Main routine executes before any of its subroutines?
Hope these analogies works for you.
😉
_____________
Code for TallyGenerator
Viewing 15 posts - 76 through 90 (of 103 total)
You must be logged in to reply to this topic. Login to reply