SQL:NOLOCK 导致查询速度变慢 [英] SQL: NOLOCK causes query slow down

问题描述

向查询添加 nolock 会导致执行时间增加是否有任何原因?

Is there any reason why adding a nolock to a query would cause it to increase execution time?

UPDATE TargetTable
      SET col1 = c1.RowCnt,
      col2 = c2.RowCnt
    from TargetTable tt
    join 
    (
      select col3, RowCnt = NULLIF(COUNT(*),0) from Table2 (nolock)
      group by col3
    ) c1 on c1.col3 = tt.ID 
    join
    (
      select col4, RowCnt = NULLIF(COUNT(*),0) from Table2 (nolock) 
      group by col4
    ) c2 on c2.col4 = tt.ID 

      WHERE timestamp BETWEEN @FromDate AND @ToDate
      AND (tt.Client_ID = @Client_ID)

推荐答案

NOLOCK 提示允许 分配顺序扫描.因此，他们可能会创建一个完全不同的执行计划，一个预期更快但结果更慢的执行计划(例如，由于陈旧的统计数据而导致错误的基数估计).与任何性能问题一样，使用调查方法找出问题的原因.Waits and Queues 是一种出色的方法.

NOLOCK hint allows Allocation Order Scans. As such, they may create a completely different execution plan, one expected to be faster but that it turns out to be slower (eg. wrong cardinality estimates due to stale stats). As with any performance pro0blem, use an investigation methodology to find the cause of the problem. Waits and Queues is an excellent such methodology.

这篇关于SQL:NOLOCK 导致查询速度变慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！