注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

中吴南顾惟一笑

成功法则就是那19个字

 
 
 

日志

 
 

Non-blocking vs. blocking iterators  

2014-09-28 14:43:01|  分类: dbms |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

Craig Freedman's SQL Server Blog

Most iterators can be classified into two categories:

  1. Iterators that consume input rows and produce output rows at the same time (in the GetRow method).  We often refer to these iterators as “non-blocking.”
  2. Iterators that consume all input rows (generally in the Open method) before producing any output rows.  We refer to these iterators as “blocking” or “stop-and-go.”

The compute scalar iterator is a simple example of a non-blocking iterator.  It read an input row, computes a new output value using the input values from the current row, immediately outputs the new value, and continues to the next input row.

The sort iterator is a good example of a blocking iterator.  The sort cannot determine the first output row until it has read and sorted all input rows.  (The last input row could be the first output row; there is no way to know until we have seen it.)

If an iterator has two children, the iterator may be blocking with respect to one and non-blocking with respect to the other.  Hash join (which I’ll cover in a future post) is a good example of such an iterator.

Non-blocking iterators are generally optimal for OLTP queries where response time is important.  They are especially desirable for TOP N queries or queries with a FAST N hint.  Since the goal is to return rows as quickly as possible, we’d like to avoid blocking operators which might process more data than necessary before returning the first rows.  Non-blocking iterators can also be useful when evaluating an EXISTS subquery where we again would like to avoid processing more data than necessary to conclude that at least one output row exists.

Hint: If a query requires ordered output, creating the right index can enable the optimizer to find a query plan that uses the index to produce the desired order instead of introducing a blocking sort.  This may dramatically improve response time.  Of course, there are other reasons to use indexes too.

Dynamic cursor support

The iterators used in a dynamic cursor query plan have special properties.  Among other things, a dynamic cursor must be able to return a subset of the result set at a time, must be able to scan forward or backward, and must be able to acquire scroll locks.  To support this functionality, an iterator must be able to save and restore its state, scan forward or backward, must process one input row for each output row it produces, and must be non-blocking.  Not all iterators have all of these properties.

For a query to be executed using a dynamic cursor, the optimizer must be able to find a query plan that uses only iterators that support dynamic cursors.  This is not always possible and this is why some queries cannot be executed using a dynamic cursor.

Hint: Just a creating the right index can enable the optimizer to eliminate a sort, for the same reason, it can sometimes (though not always) enable the optimizer also to find a dynamic cursor query plan.  Unfortunately, this is not always possible.  For example, there is no way to perform aggregation without violating the one input row for each output row property (though it is sometimes possible to work around this problem using an indexed view).

Eg. Different distinct implementation

Stream distinct (Sort distinct) : Removes duplicates presuming an input sorted on all columns.

Flow distinct: a hash-based duplicate removal operator that retains each unique input item in its hash table and also passes it through immediately as soon as a new distinct value is found.
Flow-distinct operators rely on a stable (unchanging) key and essentially remember which values of the stable key have been seen before so that each record is operated on only once.  An example of this type of operator is hash flow-distinct which uses a hash table to capture previously encountered keys, yet (differently than the usual implementation of hash distinct) passes unique input records directly to its consumers operator.

Hash distinct: Also known as eager hash. A hash distinct operator collects all unique input items in a hash table, and then produces output.

  评论这张
 
阅读(64)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017