当前位置：文江博客话题详情

查询计划显示，当实际不涉及行时，插入成本为 54%

发布于 2024-09-18 16:28:48 字数 126 浏览 9 评论 0原文

在我的一个查询中，有一个数据插入到临时表中。查看查询计划，它显示实际插入到临时表中的时间为 54%（仅将数据插入到临时表中）。但是，没有行被插入到临时表中。

当没有插入行时，为什么计划显示非零值？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

铁憨憨 2024-09-25 16:28:48

即使在实际查询计划中，显示的子树成本也是基于估计以及基于成本的优化器使用的各种启发式方法和幻数。它们可能严重错误，应该大量使用盐。

重现的示例

create table #t
(
i int
)

insert into #t
select number
from master.dbo.spt_values
where number = 99999999

Plan

实际插入为零行，但估计为 1 行，这就是子树成本的来源。

编辑：我刚刚尝试了以下

insert into #t
select top 0 number
from master.dbo.spt_values
where number = 99999999

Plan

即使它获得了正确的估计行数仍然为插入分配一个小的非零成本。我猜想它使用的启发式总是分配固定成本的一些小元素。

Even in the actual query plan the subtree costs shown are based on estimates as well as various heuristics and magic numbers used by the cost based optimiser. They can be woefully wrong and should be taken with a big pinch of salt.

Example to reproduce

create table #t
(
i int
)

insert into #t
select number
from master.dbo.spt_values
where number = 99999999

Plan

The actual insert was zero rows but the estimate was 1 row which is where the subtree cost comes from.

Edit: I just tried the following

insert into #t
select top 0 number
from master.dbo.spt_values
where number = 99999999

Plan

Even when it gets the estimated number of rows right it still assigns a small non zero cost to the insert. I guess the heuristic it uses always assigns some small element of fixed cost.

回复收藏 0 原文

星星的轨迹 2024-09-25 16:28:48

看看这个

insert into #temp
select * from sometable 
where left(Somecol,3) = 'BLA'

不是sargable，所以它会导致扫描，但如果没有找到行，则插入不会发生......扫描仍然会发生，

但如果你这样做了，那么成本应该会急剧下降，因为现在可以使用索引

insert into #temp
select * from sometable 
where Somecol like 'BLA%'

BTW 我会使用 STATISTICS TIME 和 STATISTICS IO 来衡量性能，这两个是更好的指标..当你看到 3读取次数与 10000 次读取次数相比，您知道发生了什么。45% 准确地告诉您整个过程何时可以运行 3 分钟或 3 秒

take a look at this

insert into #temp
select * from sometable 
where left(Somecol,3) = 'BLA'

that is not sargable so it will cause a scan, but if no rows are found the insert doesn't happen...the scan still happens

but if you did this then the cost should drop dramatically because now the index can be used

insert into #temp
select * from sometable 
where Somecol like 'BLA%'

BTW I would use STATISTICS TIME and STATISTICS IO instead to measure performance, those two are much better indicators..when you see 3 reads vs 10000 reads you know what is happening..what does 45% tell you exactly when the whole process could run 3 minutes or 3 seconds

回复收藏 0 原文