Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
 Posted - 2006-01-07 : 11:25:04
|
| Hi,Lets say I have an execution plan that goes something like this..- scan table A- join to table B- calculate scalar- selectMy question is what order this stuff is done in.Is the whole of the scan from table A returned, then the join is completed, then the scalars are calculated and then the select is done?I'd have thought not because that would mean several different iterations over result sets.Or is one row from A returned, joined to B, the resultant calculated and returned and then an attempt is made to join that same row from A to another row to B and so on?In other words, are rows from the root table passed completely through the execution plan one by one or are the individual operations allowed to complete for all inputs before the plan moves on to the next operation?I think the former would be faster but the second closer to the relational algebra.Cheers, XF |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-01-07 : 11:39:25
|
| If you look at the graphical query plan (Ctrl-L (estimated) or Ctrl-K (actual) in QA) you can hover over the arrows between the operators to see how many rows are returned after the operator.It will evaluate all the rows in each operator and pass them to the next, not pass one row after the other through the whole plan.An actual join is done row by row from the two input sets, and the result is an output set.The strategy that is used by the join can differ, see NESTED LOOPS, HASH JOIN, MERGE JOIN in BOL.rockmoose |
 |
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2006-01-07 : 11:49:31
|
OK thanks.quote:
If you look at the graphical query plan (Ctrl-L (estimated) or Ctrl-K (actual) in QA) you can hover over the arrows between the operators to see how many rows are returned after the operator.
The rows could still be processed one by one and these rows counts would just be running totals. |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-01-07 : 12:37:56
|
| >> The rows could still be processed one by one and these rows counts would just be running totals.I really don't think so, but then weirder things than that have happened in the world.For all I know sql server could be doing a lot of things simultaneously and where possible be pushing partial resultsets through the plan as they are ready.So why this sudden interest in how sql server processes the rows?rockmoose |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2006-01-07 : 14:45:08
|
| Hash aggregations and hash joins, Many-to-many merge joins, and Spool operations [edit]and Sort, of course (doh!)[/edit] will certainly require cause some buffering of rows within the execution of the plan. |
|
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2006-01-08 : 10:53:19
|
quote:
So why this sudden interest in how sql server processes the rows?
Well one has to pace one's learnings to avoid being overloaded. |
|
|
|
|
|
|
|
|
Reading execution plan