当没有proc时，相同的查询运行得更快。

Question

我们有一个特定的查询，当在proc中运行时，运行速度要慢得多。我必须在这里补充一下，它是封闭在一个两级光标内的。但是，这两个游标都有一个由一行组成的迭代结果集。

首先让我陈述一下我们尝试过的和失败的事情：

• 使用选项(重新编译)和选项(optiimize for (@var未知))避免参数嗅探
• 这个帖子。似乎是问题所在的变量实际上是本地变量，而不是proc参数。

下面是从proc/游标中获取的查询。

 select @tpdim1 = dim1, @tpdim2 = dim2, @typecalc = typecalc
    from loyalty_policy where code=@loop2_loyalty_policy

注意：@loop2_loyalty_policy是从内部游标的结果中提取的变量，有一个值。code是loyalty_policy表的PK。因此，@tpdim1 1和@tpdim2各有一个值。

SET STATISTICS PROFILE ON 
SET STATISTICS    xml on           
                  insert into @tbl_loyal_loop2 (cnt, store, map, pda, insdate, line, item, loyalty_policy_data, loyal_calc, loyalty_policy)
                  select @cnt, t.store, t.map, t.pda, t.insdate, t.line, t.item, ld.tab_id,  
                  case @typecalc
                        when 1 then convert(bigint,round(isnull(t.valueFromTran,0.00) * ld.value , 0 ) )
                        when 2 then convert(bigint,round(isnull(t.netvalue,0.00) * ld.value , 0 ) )
                        when 3 then convert(bigint,isnull(t.qty,0) * ld.value )
                        when 4 then convert(bigint,round(isnull(t.valueFromPrice2,0.00) * ld.value , 0 ) )
                        when 5 then convert(bigint,round(isnull(t.valueFromPrice3,0.00) * ld.value , 0 ) )
                        when 6 then convert(bigint,round(isnull(t.valueFromPrice4,0.00) * ld.value , 0 ) )
                  else 0 end
                  ,@loop2_loyalty_policy
                  from loyalty_policy_data ld-- with (index=ind_loyalty_policy_02)
                              inner join #tbl_data t on t.insdate >= ld.fdateactive and t.insdate <= ld.tdateactive
                  where ld.loyalty_policy = @loop2_loyalty_policy 
                  and ld.tdateactive >= @from_rundate and ld.fdateactive <= @to_rundate
                  and t.dbupddate > @loop1_dbupddate  
                  and
                        case when @tpdim1 is null then '' 
                        else  
                              case  @tpdim1 
                                    when 'STORE'            then t.store when 'BRAND' then t.brand  when 'CAT1' then t.cat1   when 'CAT2' then t.cat2   when 'CAT3' then t.cat3   when 'ITEM' then t.item    
                                    when 'CUSTGROUP'  then t.custgroup when 'CUSTGROUP2' then t.custgroup2 when 'CUSTGROUP3' then t.custgroup3
                                    when 'CUSTOMER'         then @customer
                              else '' end
                        end
                        = case when @tpdim1 is null then '' else ld.dim1 end
                  and 
                        case when @tpdim2 is null then '' 
                        else  
                              case  @tpdim2 
                                    when 'STORE'            then t.store when 'BRAND' then t.brand  when 'CAT1' then t.cat1   when 'CAT2' then t.cat2   when 'CAT3' then t.cat3   when 'ITEM' then t.item    
                                    when 'CUSTGROUP'  then t.custgroup when 'CUSTGROUP2' then t.custgroup2 when 'CUSTGROUP3' then t.custgroup3
                                    when 'CUSTOMER'         then @customer                     
                              else '' end
                        end
                        = case when @tpdim2 is null then '' else ld.dim2 end
SET STATISTICS    xml off    

上面的SET STATISTICS XML返回这个计划。

在尝试调试它时，我们以以下形式隔离了查询(在这里，您还可以看到表#a是如何生成的，它的数据与前一个#tbl_data完全相同)：

drop table #a;
select dt.dbupddate, dt.insdate, dt.map, dt.pda, pt.line, pt.item, 
( pt.exp_qty - pt.imp_qty)  as qty,  
( pt.exp_value + pt.imp_value )  as netvalue, 
( (document.exp_val - document.imp_val) * (pt.netvalue - pt.vat_value) )  as valueFromTran,  
( (document.exp_val - document.imp_val) * ( ( (pt.qty - pt.qty_gift) * isnull(pt.price2,0.00) ) * (1.00-( pt.disc_perc / 100)) ) ) as valueFromPrice2, 
( (document.exp_val - document.imp_val) * ( ( (pt.qty - pt.qty_gift) * isnull(pt.price3,0.00) ) * (1.00-( pt.disc_perc / 100)) ) ) as valueFromPrice3, 
( (document.exp_val - document.imp_val) * ( ( (pt.qty - pt.qty_gift) * isnull(pt.price4,0.00) ) * (1.00-( pt.disc_perc / 100)) ) ) as valueFromPrice4, 
dt.store, item.brand, item.cat1, item.cat2, item.cat3, customer.custgroup, customer.custgroup2, customer.custgroup3 
into #a
from document with (nolock) 
      inner join dt with (nolock) on dt.doccode = document.code 
      inner join store with (nolock) on store.code = dt.store and store.calc_loyal = 1 
      inner join customer with (nolock) on customer.code = dt.customer  
      inner join pt with (nolock) on dt.map = pt.map and dt.pda=pt.pda 
      inner join item with (nolock) on item.code = pt.item and item.itemtype in (select code from itemtype with (nolock) where vsales = 1)
where dt.canceled = 0 and document.is_opposite = 0 and document.type = 3 and dt.customer=N'EL4444444'
and dt.insdate >= '20180109' and dt.insdate <= '20190108' ;



SET STATISTICS PROFILE ON 
                  select t.store, t.map, t.pda, t.insdate, t.line, t.item, ld.tab_id,  
                  case 4
                        when 1 then convert(bigint,round(isnull(t.valueFromTran,0.00) * ld.value , 0 ) )
                        when 2 then convert(bigint,round(isnull(t.netvalue,0.00) * ld.value , 0 ) )
                        when 3 then convert(bigint,isnull(t.qty,0) * ld.value )
                        when 4 then convert(bigint,round(isnull(t.valueFromPrice2,0.00) * ld.value , 0 ) )
                        when 5 then convert(bigint,round(isnull(t.valueFromPrice3,0.00) * ld.value , 0 ) )
                        when 6 then convert(bigint,round(isnull(t.valueFromPrice4,0.00) * ld.value , 0 ) )
                  else 0 end
                  ,'003'
                  --select count(*)
                  from loyalty_policy_data ld with (index=ind_loyalty_policy_02)
                              inner join #a t on t.insdate >= ld.fdateactive and t.insdate <= ld.tdateactive
                  where ld.loyalty_policy = '003' 
                  --and ld.tdateactive >= '20180109' and ld.fdateactive <= '20190108'
                  and t.dbupddate > '20000101'
      and 
                        case when 'CUSTOMER' is null then '' 
                        else  
                              case  'CUSTOMER' 
                                    when 'STORE'            then t.store when 'BRAND' then t.brand  when 'CAT1' then t.cat1   when 'CAT2' then t.cat2   when 'CAT3' then t.cat3   when 'ITEM' then t.item    
                                    when 'CUSTGROUP'  then t.custgroup when 'CUSTGROUP2' then t.custgroup2 when 'CUSTGROUP3' then t.custgroup3
                                    when 'CUSTOMER'         then 'EL0134366'
                              else '' end
                        end
                        = case when 'CUSTOMER' is null then '' else ld.dim1 end
                  and 
                        case when 'BRAND' is null then '' 
                        else  
                              case  'BRAND' 
                                    when 'STORE'            then t.store when 'BRAND' then t.brand  when 'CAT1' then t.cat1   when 'CAT2' then t.cat2   when 'CAT3' then t.cat3   when 'ITEM' then t.item    
                                    when 'CUSTGROUP'  then t.custgroup when 'CUSTGROUP2' then t.custgroup2 when 'CUSTGROUP3' then t.custgroup3
                                    when 'CUSTOMER'         then 'EL0134366'

                              else '' end
                        end
                        = case when 'BRAND' is null then '' else ld.dim2 end
SET STATISTICS PROFILE off    

而这里是执行计划。这个跑得快多了。

为什么会有如此巨大的差异？从我有限的执行分析知识来看，我注意到

1. 对index spool操作的第一个(慢速)查询估计行数为9700行，但实际行数为300万行。
2. 第二个查询使用了许多并行操作。
3. 我在第二个查询中唯一能看到的“真实”区别是@tpdim1 1和@tpdim2值的手动替换值。当然，当我们进入第一个查询的proc代码，用它们应该得到的单个值替换@tpdim1 1& @tpdim2时，它的运行速度和第二个查询一样快。

你能不能解释一下这个差异，并提出一些建议来解决这个问题？

编辑:正如Vergil所推荐的，我用先前声明的变量替换了第二个查询中的文本，并且再次缓慢运行！

编辑2:我有一些额外的信息做一些进一步的研究。

首先，我把问题隔离到这一行：

case when @tpdim1 is null then '' <--这使用慢速计划

case when 'CUSTOMER' is null then '' <--这使用快速计划

这在即席查询中是正确的，不需要用spc和/或游标来麻烦自己。

即使我将代码更改为推荐的动态where结构，这种情况也会持续下去。

我还没有创建任何sampla数据，但重要的信息(可以在计划中看到)是，如果我们只通过loyalty_policy_data进行筛选，loyalty_policy = @loop2_loyalty_policy大约有720 k行。但是，如果计算@tpdim1 1条件(实质上是if 1=N‘EL0134366’)，则返回的行仅为4。

因此，计划的不同之处在于，当对日期检查条件进行评估时，就会对此条件进行评估。

在快速计划中，它首先得到评估--在为忠诚度策略值寻找索引时，它添加了一个(非查找)谓词。虽然此谓词不在索引中，但返回的行为4，所有其他运算符都具有“逻辑”大小。

相反，缓慢的计划痛苦地忽视了这个谓词，直到太晚。如果我计算正确的话，它会将loyalty_policy_data上的嵌套循环作为外层表(这太疯狂了)。它以外部引用的形式传递所需的列。对于每个这样的元组，索引假脱机扫描#表(~1k行)并查找大约250个结果，并将其传递给最终执行tpdim1筛选的过滤器。因此，250*700 k行传递给过滤器运算符。

所以现在我想我知道发生了什么。但我不明白为什么。

Answer 1

回答你的问题：

对查询分析器在这些情况下如何和为什么有不同行为的清晰和可重复的解释。

在这些情况下，查询优化器的行为不同，因为带有变量的计划必须对任何可能的未来参数值有效，因此优化器生成一个复杂的泛型计划，即使参数为NULL，也会产生正确的结果。

包含文字(而不是变量)的计划通常更有效，因为优化器可以大大简化计划编译阶段的CASE逻辑。优化器有更好的选择最优计划形状的机会，因为当查询更简单且过滤器具有已知值时，优化器更容易考虑有关索引和基数估计的可用信息。

马丁·史密斯在评论中指出，您使用的是服务器版本10.0.2531.0，它是2008年的SP1，没有启用参数嵌入优化。您需要在该分支的最小SP1 CU5上使OPTION (RECOMPILE)正常工作(正如我在下面的解释中所预期的那样)。

Erland在下面提到的文章中也谈到了这一点。他说你至少要上SP2。

如果无法更新服务器，请查看Erland文章SQL 2005及更高版本的T版本的动态搜索条件的旧版本，以了解如何在没有合适的OPTION (RECOMPILE)时处理这种情况。

这是我最初的答案。

我知道你说你试过了，但我还是要你再查一遍。观察你的症状，OPTION (RECOMPILE)应该有帮助。

您需要将此选项添加到主查询中。而不是整个存储过程。如下所示：

insert into @tbl_loyal_loop2 (cnt, store, map, pda, insdate, line, item, loyalty_policy_data, loyal_calc, loyalty_policy)
select @cnt, t.store, t.map, t.pda, t.insdate, t.line, t.item, ld.tab_id,  
case @typecalc
    when 1 then convert(bigint,round(isnull(t.valueFromTran,0.00) * ld.value , 0 ) )
    when 2 then convert(bigint,round(isnull(t.netvalue,0.00) * ld.value , 0 ) )
    when 3 then convert(bigint,isnull(t.qty,0) * ld.value )
    when 4 then convert(bigint,round(isnull(t.valueFromPrice2,0.00) * ld.value , 0 ) )
    when 5 then convert(bigint,round(isnull(t.valueFromPrice3,0.00) * ld.value , 0 ) )
    when 6 then convert(bigint,round(isnull(t.valueFromPrice4,0.00) * ld.value , 0 ) )
else 0 end
,@loop2_loyalty_policy
from loyalty_policy_data ld -- with (index=ind_loyalty_policy_02)
            inner join #tbl_data t on t.insdate >= ld.fdateactive and t.insdate <= ld.tdateactive
where ld.loyalty_policy = @loop2_loyalty_policy 
and ld.tdateactive >= @from_rundate and ld.fdateactive <= @to_rundate
and t.dbupddate > @loop1_dbupddate  
and
    case when @tpdim1 is null then '' 
    else  
            case  @tpdim1 
                when 'STORE'            then t.store when 'BRAND' then t.brand  when 'CAT1' then t.cat1   when 'CAT2' then t.cat2   when 'CAT3' then t.cat3   when 'ITEM' then t.item    
                when 'CUSTGROUP'  then t.custgroup when 'CUSTGROUP2' then t.custgroup2 when 'CUSTGROUP3' then t.custgroup3
                when 'CUSTOMER'         then @customer
            else '' end
    end
    = case when @tpdim1 is null then '' else ld.dim1 end
and 
    case when @tpdim2 is null then '' 
    else  
            case  @tpdim2 
                when 'STORE'            then t.store when 'BRAND' then t.brand  when 'CAT1' then t.cat1   when 'CAT2' then t.cat2   when 'CAT3' then t.cat3   when 'ITEM' then t.item    
                when 'CUSTGROUP'  then t.custgroup when 'CUSTGROUP2' then t.custgroup2 when 'CUSTGROUP3' then t.custgroup3
                when 'CUSTOMER'         then @customer                     
            else '' end
    end
    = case when @tpdim2 is null then '' else ld.dim2 end
OPTION(RECOMPILE);

OPTION (RECOMPILE)不是为了减轻参数嗅探，而是为了允许优化器将参数的实际值内联到查询中。这给了优化器简化查询逻辑的自由。

您的查询类型类似于动态搜索条件，我强烈建议您阅读Erland的那篇文章。

而且，而不是

and
    case when @tpdim1 is null then '' 
    else  
            case  @tpdim1 
                when 'STORE'            then t.store when 'BRAND' then t.brand  when 'CAT1' then t.cat1   when 'CAT2' then t.cat2   when 'CAT3' then t.cat3   when 'ITEM' then t.item    
                when 'CUSTGROUP'  then t.custgroup when 'CUSTGROUP2' then t.custgroup2 when 'CUSTGROUP3' then t.custgroup3
                when 'CUSTOMER'         then @customer
            else '' end
    end
    = case when @tpdim1 is null then '' else ld.dim1 end

我会把它写得有点不同：

and
(
    @tpdim1 is null
    OR
    (
            ld.dim1 =
            case @tpdim1
                when 'STORE'      then t.store 
                when 'BRAND'      then t.brand  
                when 'CAT1'       then t.cat1   
                when 'CAT2'       then t.cat2   
                when 'CAT3'       then t.cat3   
                when 'ITEM'       then t.item    
                when 'CUSTGROUP'  then t.custgroup 
                when 'CUSTGROUP2' then t.custgroup2 
                when 'CUSTGROUP3' then t.custgroup3
                when 'CUSTOMER'   then @customer
                else ''
            end
    )
)

对于OPTION (RECOMPILE)，当@tpdim1的值为CUSTOMER，而@customer的值为EL0134366时，优化器应该将该语句转换为一个简单的

and
(
    ld.dim1 = `EL0134366`
)

然后，它将能够使用合适的索引或更准确地估计行数，并对计划形状做出更好的决定。使用此选项，计划将仅对参数的此特定值有效。

请注意，option (optimize for UNKNOWN)在这里无能为力。optimize for UNKNOWN必须生成一个对参数的任何可能值都有效的通用计划。

Answer 2

在为可读性目的清理查询之后，我有以下内容。

insert into @tbl_loyal_loop2 
( cnt, 
  store, 
  map, 
  pda, 
  insdate, 
  line, 
  item, 
  loyalty_policy_data, 
  loyal_calc, 
  loyalty_policy
)
select 
      @cnt, 
      t.store, 
      t.map, 
      t.pda, 
      t.insdate, 
      t.line, 
      t.item, 
      ld.tab_id,
      convert(bigint, round( coalesce(
         case @typecalc
               when 1 then t.valueFromTran
               when 2 then t.netvalue
               when 3 then t.qty
               when 4 then t.valueFromPrice2
               when 5 then t.valueFromPrice3
               when 6 then t.valueFromPrice4
               else 0 
            END,   0.00) * ld.value , 0 ) ),
      @loop2_loyalty_policy
   from 
      loyalty_policy_data ld  -- with (index=ind_loyalty_policy_02)
         inner join #tbl_data t 
            on t.insdate >= ld.fdateactive 
            and t.insdate <= ld.tdateactive
   where 
          ld.loyalty_policy = @loop2_loyalty_policy 
      and ld.tdateactive >= @from_rundate 
      and ld.fdateactive <= @to_rundate
      and t.dbupddate > @loop1_dbupddate  
      and (   @tpdim1 is null
           OR ld.dim1 = case @tpdim1
                           when 'STORE' then t.store 
                           when 'BRAND' then t.brand  
                           when 'CAT1' then t.cat1   
                           when 'CAT2' then t.cat2   
                           when 'CAT3' then t.cat3   
                           when 'ITEM' then t.item    
                           when 'CUSTGROUP' then t.custgroup 
                           when 'CUSTGROUP2' then t.custgroup2 
                           when 'CUSTGROUP3' then t.custgroup3
                           when 'CUSTOMER' then @customer
                           else ''
                          END )
      and (   @tpdim2 is null
           OR ld.dim2 = case when @tpdim1
                         when 'STORE' then t.store 
                         when 'BRAND' then t.brand  
                         when 'CAT1' then t.cat1
                         when 'CAT2' then t.cat2
                         when 'CAT3' then t.cat3
                         when 'ITEM' then t.item    
                         when 'CUSTGROUP' then t.custgroup 
                         when 'CUSTGROUP2' then t.custgroup2 
                         when 'CUSTGROUP3' then t.custgroup3
                         when 'CUSTOMER' then @customer
                         else '' 
                      END )

此外，我还要确保您的loyalty_policy_data表上有一个综合索引.索引on ( loyalty_policy、、dim1、dim2 )

这样，您将对WHERE筛选标准中使用的所有字段进行限定。不要只依靠关键的索引..。但是，键加上日期将有助于优化特定的日期范围，而不必返回原始数据页，但可以根据索引中的值优化查询连接条件。

至于临时表#tbl_data，请确保您有一个索引on ( insdate )，因为这是唯一的连接基条件(如果您还没有该表的索引)。

评论-

的慢查询和快速查询的注释

@tpdim1 1= NULL vs 'CUSTOMER‘= NULL

一个固定的字符串'CUSTOMER‘永远不是空的，所以它不必在空路径中考虑它。修正了字符串'CUSTOMER‘与@customer变量为null或在ld.dim1和ld.dim2的情况下被比较为null.也许需要测试的内容应该从

  and (   @tpdim1 is null
               OR ld.dim1 = case @tpdim1
                               when 'STORE' then t.store 
                               when 'BRAND' then t.brand  ... end
     )

至

  and ld.dim1 = case @tpdim1
                when NULL then ''
                when 'STORE' then t.store 
                when 'BRAND' then t.brand  ... end

ld.dim2案例/时间也是如此。将"NULL“作为@tpdim2 1(和@tpdim2)测试的第一个测试值。

Answer 3

一般来说，使用literal value查询比使用proc parameter或local variable查询快。

当使用文字值时，如果Optimizer未打开，Forced Parameterization将为该值制定特殊计划

优化器也可以使Trivial Plan或简单Parameterize Plan，但在您的情况下，这是不正确的。

使用参数时，优化器将为该参数创建一个称为Parameter Sniffing的计划，然后重用该计划。

Option Recompile是解决这个问题的一种方法:为每个不同的变量值创建计划，以保持“基数估计”。这很短

因此，具有文字值的查询总是更快。

首先让我陈述一下我们尝试过的和失败的事情： ·使用选项(重新编译)和选项(optiimize (@var UNKOWN) )避免参数嗅探 ·这条线。似乎是问题所在的变量实际上是本地变量，而不是proc参数。

您失败了，因为您的查询写得非常糟糕(恕我直言)。

不要使用游标。在您的情况下，光标似乎是可以避免的。

使用变量参数发布完整的proc查询，因为在@loop2_loyalty_policy等中获取值的逻辑不是clear.This，这将有助于给出正确的提示“以避免游标”。

case when @tpdim1 is null：可以创建完整的逻辑并将其插入临时表本身，这样新列就可以立即在join.Hope中使用--您可以理解我的想法和语言。

1.对索引假脱机操作的第一个(慢速)查询估计行数为9700行，但实际行数为300万行。

由于optmizer的基数估计很高，所以在出现连接错误时

我不确定，这是否肯定会改善您的查询和基数估计，因为我还没有100%理解您的查询。

但改变加入条件往往会有帮助，

在这里仔细阅读，我不知道loyalty_policy和t.insdate.It列中有什么数据不需要像下面这样复杂的连接。

如果您真的需要，那么您可以像下面这样alter join condition一次。

from loyalty_policy_data ld with (nolock)
 inner join #tbl_data t on ld.loyalty_policy = @loop2_loyalty_policy
 and ld.tdateactive >= @from_rundate and ld.fdateactive <= @to_rundate 
and t.insdate >= ld.fdateactive and t.insdate <= ld.tdateactive
                  where t.dbupddate > @loop1_dbupddate 

主要目标是避免游标。