sql - 超出 proc 时，相同的查询运行得更快
问题描述
我们有一个特定的查询，在 proc 中运行得慢很多。我必须在这里补充一点，它包含在一个两级光标中。但是，两个游标都有一行的迭代结果集。
让我首先说明我们尝试和失败的事情：
- 通过使用 option (recompile) 和 option (optimize for (@var UNKNOWN) 避免参数嗅探
- 这个线程。似乎是问题的变量实际上是本地变量，而不是 proc 参数。
这是从 proc/cursors 内部获取的查询。
select @tpdim1 = dim1, @tpdim2 = dim2, @typecalc = typecalc
from loyalty_policy where code=@loop2_loyalty_policy
注意：@loop2_loyalty_policy 是取自内部游标结果的 var，只有一个值。code是PK到loyalty_policy表。因此，@tpdim1 和@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
这是执行计划。这运行得更快。
为什么会有这种巨大的差异？根据我对执行分析的有限知识，我注意到
- 该
index spool操作的第一个（慢速）查询估计行数约为 9700 行，但实际行数为 300 万行。 - 第二个查询使用了许多具有并行性的操作
- 我在第二个查询中看到的唯一“真正”区别是 @tpdim1 和 @tpdim2 值的手动替换值。果然，当我们进入第一个查询的 proc 代码，并用他们应该得到的单个值替换 @tpdim1 和 @tpdim2 时，它的运行速度与第二个查询一样快。
您能否解释一下这种差异并提出一些建议来解决该程序？
编辑：正如 Laughing Vergil 建议的那样，我用先前声明的变量替换了第二个查询中的文字，它再次运行缓慢！
编辑 2：我有一些额外的信息来自做一些进一步的研究。
首先，我将问题隔离到这一行：
case when @tpdim1 is null then ''<-- 这使用了慢速计划
case when 'CUSTOMER' is null then ''<-- 这里使用fast plan
这在即席查询中是正确的，无需为 spcs 和/或游标烦恼。
即使我将代码更改为推荐的动态 where 结构，这种情况也会继续发生。
我还没有创建任何样本数据，但重要信息（如计划中所见）是，loyalty_policy_data如果我们只过滤loyalty_policy = @loop2_loyalty_policy. 但是，如果我们评估 @tpdim1 条件，本质上是 dim1=N'EL0134366'，则返回的行数仅为 4。
因此，计划中的不同之处在于何时根据日期检查条件评估此条件。
在快速计划中，它首先被评估 - 在寻找忠诚度策略值的索引时，它添加了一个（非寻找）谓词。虽然这个谓词不在索引内，但返回的行是 4 并且所有其他运算符都有“逻辑”大小。
相反，缓慢的计划痛苦地忽略了这个谓词，直到为时已晚。如果我没记错的话，它会在loyal_policy_data 上创建一个嵌套循环作为外部表（这很疯狂）。它将所需的列作为外部引用传递。对于每个这样的元组，索引假脱机扫描#table（~1k 行）并找到大约 250 个结果，并将其传递给最终执行 tpdim1 过滤的过滤器。因此，250*700k 行被传递给过滤器操作符。
所以现在我想我知道会发生什么。但我想不通为什么。
解决方案
出于可读性目的清理查询后，我有以下内容。
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 )
此外，我会确保您的loyal_policy_data 表上有一个复合索引...索引（忠诚度策略，tdateactive，fdateactive，dbupddate，dim1，dim2）
这样，您就可以限定 WHERE 过滤条件中使用的所有字段。不要只依赖键的索引...但是键 PLUS 日期将有助于优化特定日期范围而无需返回原始数据页面，但可以根据 INDEX 中的值优化查询 JOIN 条件.
至于您的临时表 #tbl_data，请确保您在 ( insdate ) 上有一个索引，因为这是唯一的 JOIN 基础条件（如果您还没有该表上的索引）。
评论 -
根据您对基于 null 的慢速与快速查询的评论
@tpdim1 = NULL vs '客户' = 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”作为@tpdim1（和@tpdim2）测试的第一个测试值。
推荐阅读
- r - 有没有办法让 R 计算我的 csv 文件以显示百分位数？
- pandas - 按字母顺序对字符串列的每个值进行排序
- python-3.x - 我得到了正确的输出，但我只想切片到前 5 个值请告诉我如何对相同的操作进行切片
- xml - 如何在经典 ASP 中将 XML 转换为 html？
- php - 在php中转换每个子数组的一项
- scala - Scala + Spark - 如何从“scala.collection.mutable.WrappedArray$ofRef”转换为我们的自定义对象？
- reactjs - 为什么 CSS 模块样式未应用于反应子组件列表中的第一个组件？
- css - 如何将背景图像 CSS 属性转换为
元素？
- r - 特定数据集子集
- python - 使用 Python 检查 BTC 种子是否存在