语句可以简化一下,不知道对速度有没有影响 update tb_analysis_buffer set location=isnull(tb_iptable_city_redapple.provcity_id,0) from tb_iptable_city_redapple where tb_analysis_buffer.ipvalue between tb_iptable_city_redapple.start_value and tb_iptable_city_redapple.end_value
试试下面的脚本update a set a.location = b.provcity_id from tb_analysis_buffer a where exists ( select 1 from tb_iptable_city_redapple b where b.start_value <= a.ipvalue and b.end_value >= a.ipvalue )在tb_iptable_city_redapple的start_value和end_value上建覆盖索引,再试试
请问大大们有什么好办法非聚集索引禁用试试,好像没什么办法了,等高手
tb_buffer上对ipvalue 加了个非聚居索引 >>>>改tb_buffer上的ipvalue 为 聚居索引
update tb_analysis_buffer set location=isnull(tb_iptable_city_redapple.provcity_id,0)
from tb_iptable_city_redapple
where tb_analysis_buffer.ipvalue between tb_iptable_city_redapple.start_value and tb_iptable_city_redapple.end_value
tb_buffer(索引查找)
idx_tb_buffer_ipvalue
开销31%tb_iptable(聚集索引扫描)
开销0%嵌套循环(inner join) 27%
哈希匹配(Partial Aggregate)42%应该是走索引的
谁能解释一下
试试下面的脚本update
a
set
a.location = b.provcity_id
from
tb_analysis_buffer a
where
exists
(
select
1
from
tb_iptable_city_redapple b
where
b.start_value <= a.ipvalue
and
b.end_value >= a.ipvalue
)在tb_iptable_city_redapple的start_value和end_value上建覆盖索引,再试试