首页新闻找找看学习计划

sql查询语句查询很慢

1
悬赏园豆:30 [已解决问题] 解决于 2018-11-23 14:49

//主查询语句
select isnull(SUM(ZJSL),0) as zjsl,
b.b_code,b.b_name,b.b_munit,b.b_remark,b.b_cprice,
sum(sib_cnum) as sib_cnum,
sum(sib_afterrevnum) as sib_afterrevnum,
sum(sib_afterchgnum) as sib_afterchgnum,
round(b_cpricesum(sib_afterchgnum),0) as amount
from Meas_SubItemBill sib
join Meas_Bill b on sib.sib_bid = b.row_id
left join
(
select v.sibSiid ,v.b_code, v.b_name, v.b_munit, v.b_price, v.b_remark, SUM(v.ZJSL) as ZJSL
from view_chgBills v
group by v.sibSiid, v.b_code, v.b_name, v.b_munit, v.b_price, v.b_remark
) r on r.sibSiid = sib.sib_siid and isnull(r.b_code,'') = isnull(b.b_code,'') and isnull(r.b_name,'') = isnull(b.b_name,'')
and isnull(r.b_munit,'') = isnull(b.b_munit,'')
and isnull(r.b_price,0) = isnull(b.b_cprice,0) and isnull(r.b_remark,'') = isnull(b.b_remark,'')
where sib_siid in (select id from c_tree('62b7d3e5-1f37-4fa4-a390-2d746419d58b'))
group by b.b_code, b.b_name, b.b_munit, b.b_cprice, b.b_remark
//其中的用到的表值函数
ALTER function [dbo].c_tree/
定义函数c_tree,输入参数为初始节点id 创建函数 根据节点id 找出所有子节点/
returns @t table(id varchar(50),parentid varchar(50),lev INT,byid varchar(50))/
定义表t用来存放取出的数据/
begin
declare @i int/
标志递归级别*/
set @i=1
insert @t select row_id,ssi_pid,@i ,byid=@initid from Base_SectionSubItem where row_id=@initid
while @@rowcount<>0
begin
set @i=@i+1
insert @t select row_id,ssi_pid,@i,@initid from Base_SectionSubItem as a,@t as b
where b.id=ssi_pid and b.lev=@i-1
end
return
END
如果将where sib_siid in (select id from c_tree('62b7d3e5-1f37-4fa4-a390-2d746419d58b')) 这个where语句去掉,查询速度就会提升或者将left join去掉也可以,是什么原因造成的查询速度降了下来,有什么好的方法在保证查询结果的情况下,提升查询速度?

问题补充:

SELECT /v_ssi.allcode,/ bill.b_code, bill.b_name, bill.b_munit, bill.b_cprice AS b_price, bill.b_remark, bill.b_secid, si.revchgsi_siid AS sibSiid, revchgsib.revchgsib_sibid,
revchgsib.revchgsib_bapplynum AS BGQSL, revchgsib_bapplyuprice * revchgsib_bapplynum AS BGQJE, revchgsib_approvenum AS BGHSL,
revchgsib_applyuprice * revchgsib_approvenum AS BGHJE, revchgsib_approvenum - revchgsib_bapplynum AS ZJSL,
revchgsib_approvenum * revchgsib_approveuprice - revchgsib_bapplynum * revchgsib_bapplyuprice AS ZJJE
FROM Cont_RevChgSecsubitemBill revchgsib JOIN
Cont_ChangingMng chg ON revchgsib.revchgsib_revchgid = chg.row_id JOIN
Cont_ChangingMngs chgs ON chg.chg_issueid = chgs.row_id JOIN
Cont_RevChgSecsubitem si ON revchgsib.revchgsib_siid = si.revchgsi_ssiid JOIN
Meas_Bill bill ON revchgsib.revchgsib_bid = bill.row_id
/join V_SecChangeSubitemAllCodePath v_ssi on v_ssi.row_id = si.revchgsi_ssiid/ WHERE revchgsib.revchgsib_revchgtype = 2 AND ISNULL(chgs.flow_status, 0) = 2 AND si.row_id NOT IN
(SELECT revchgsi_ssiid
FROM Cont_RevChgSecsubitem)
这个为view_chgBills中的部分代码,根据dudu提供的方法,我查到Cont_RevChgSecsubitem这张表逻辑读取了20多万次,从而影响了性能,但要如何处理呢

但乱红尘的主页 但乱红尘 | 菜鸟二级 | 园豆:392
提问于:2018-11-22 16:10
< >
分享
最佳答案
1

你这一个查询里,又是group by 又是关联表,还用in,还递归。。怎么不慢
尝试分开执行那几部分,查询出的数据可以先临时放一个表,然后逐个合并上去看看问题在哪部分

收获园豆:15
rqx | 菜鸟二级 |园豆:468 | 2018-11-22 16:55

对,我试着将一部分结果先放到虚拟表里,再查,就会快很多,但感觉这样写不太好,就想想看看还有没有其他方法

但乱红尘 | 园豆:392 (菜鸟二级) | 2018-11-22 16:58

@淘小涛: 临时表没什么不好的啊,数据库会对这些做优化的,而且你这sql语句较复杂,合在一起后面维护成本也很高,拆开逻辑清晰就行

rqx | 园豆:468 (菜鸟二级) | 2018-11-22 17:01

@rqx: 是的,但如果两个人同时访问查询的话,会不会产生冲突?

但乱红尘 | 园豆:392 (菜鸟二级) | 2018-11-22 17:29

@淘小涛: 。。不会。。sql那边对临时表处理了的

rqx | 园豆:468 (菜鸟二级) | 2018-11-22 17:45

@rqx: 嗯嗯,暂定是这种了,谢谢啦,不过最近不太忙,想研究研究看能不能通过语句优化,降低逻辑读取来提高查询速度

但乱红尘 | 园豆:392 (菜鸟二级) | 2018-11-23 09:42

Cont_RevChgSecsubitem 中同一个revchgsi_ssiid 下有很多子项,你是把主表和子表一起关联查询啊?
几种方式可以试试,因为不清楚你具体存储的逻辑
1.用子表做主表关联,其他表关联子表
2.先把主表的信息查完了,最后关联子表赋值内容
3.把子表和需要处理的表关联查询完了,再连接主表

rqx | 园豆:468 (菜鸟二级) | 2018-11-23 09:43

@rqx: 嗯,根据你的意见,已经解决了一个问题,将Cont_RevChgSecsubitem表的逻辑读取从20多万次降到100多了,不过还有worktable表的77万次呢[哭笑~]我看网上说这个工作表是用来存储group by,union,order by产生的中间数据的,问题发现了,怎么解决呢?没什么经验

但乱红尘 | 园豆:392 (菜鸟二级) | 2018-11-23 11:32

@淘小涛: 可以用left join的,不要用inner join,group by这种没办法,这种聚合查询本来就是全表,如果有where条件的,可以考虑where条件判断上的字段加索引

rqx | 园豆:468 (菜鸟二级) | 2018-11-23 11:36

@淘小涛: 对了,还有一些设置,比如中间信息是否显示,忘了是哪个关键字了,平时都是直接复制的。。。有一些控制语句,关闭那些,可以减少中间信息的输出,会加快一些

rqx | 园豆:468 (菜鸟二级) | 2018-11-23 11:38

@rqx: where条件判断字段是表值函数定义表中的Id,用的是varchar,还不能设置为主键;中间信息是什么??

但乱红尘 | 园豆:392 (菜鸟二级) | 2018-11-23 14:00

问题已经解决了,虽然不是通过优化精简sql语句,是通过修改业务,绕过了使用view_chgBills视图,从而加快了查询速度,不过通过这次,学到的蛮多的,衷心的感谢哦

但乱红尘 | 园豆:392 (菜鸟二级) | 2018-11-23 14:44
其他回答(5)
0

加索引了吗

徒然喜欢你 | 园豆:1599 (小虾三级) | 2018-11-22 16:14

加了,不好使,如果将数据放到虚拟表中再查询速度就可以了,但感觉不太好

支持(0) 反对(0) 但乱红尘 | 园豆:392 (菜鸟二级) | 2018-11-22 16:46

@淘小涛: 子查询里面没办法用索引,看看执行计划吧,看能不能不要子查询

支持(0) 反对(0) 会长 | 园豆:6042 (大侠五级) | 2018-11-22 17:25

@会长: 嗯,根据dudu提供的方法,发现是Cont_RevChgSecsubitem这张表被读取太多次了,从而增加了内耗,但要怎么解决呢,索引是用不了了

支持(0) 反对(0) 但乱红尘 | 园豆:392 (菜鸟二级) | 2018-11-23 09:46

@淘小涛: 改表结构来得及吗,数据库适当的冗余也能加快查询速度

支持(0) 反对(0) 会长 | 园豆:6042 (大侠五级) | 2018-11-23 09:50

@会长: 表结构没改,业务关系改了一下,效果是相同的,绕过了使用view_chgBills视图,所以查询速度提升上来了

支持(0) 反对(0) 但乱红尘 | 园豆:392 (菜鸟二级) | 2018-11-23 14:46
0

尽可能少用子查询,在查询频繁的字段上加索引试试

收获园豆:5
会长 | 园豆:6042 (大侠五级) | 2018-11-22 16:14

添加了索引,但速度没有明显的提升

支持(0) 反对(0) 但乱红尘 | 园豆:392 (菜鸟二级) | 2018-11-22 16:56

嗯嗯,非常感谢你的建议哦,问题已经解决了

支持(0) 反对(0) 但乱红尘 | 园豆:392 (菜鸟二级) | 2018-11-23 14:45
1

建议看一下执行计划

收获园豆:5
dudu | 园豆:39065 (高人七级) | 2018-11-22 16:15

我根据执行计划添加了索引,Cont_RevChgSecsubitemBill的开销从28%降到14%,但查询时间依旧需要20多秒

支持(0) 反对(0) 但乱红尘 | 园豆:392 (菜鸟二级) | 2018-11-22 16:44

@淘小涛: 建议加上 set statistics io on 看一下 physical reads 比较大的查询进行优化

支持(0) 反对(0) dudu | 园豆:39065 (高人七级) | 2018-11-22 17:44

@dudu: 我去看一下,的确Cont_RevChgSecsubitem这张表被逻辑读取了20多万次从而影响了性能,应该是WHERE revchgsib.revchgsib_revchgtype = 2 AND ISNULL(chgs.flow_status, 0) = 2 AND si.row_id NOT IN
(SELECT revchgsi_ssiid
FROM Cont_RevChgSecsubitem),not in子查询这里影响了性能,但要怎么去优化它呢?

支持(0) 反对(0) 但乱红尘 | 园豆:392 (菜鸟二级) | 2018-11-23 09:40

问题已经解决啦,非常感谢dudu提供的建议。

支持(0) 反对(0) 但乱红尘 | 园豆:392 (菜鸟二级) | 2018-11-23 14:44
0

换个数据库行不行?比如MongoDB,,看的有些晕,

智人N | 园豆:601 (小虾三级) | 2018-11-22 20:59

。。。。这个好像不是我能决定的。。。我也没办法

支持(0) 反对(0) 但乱红尘 | 园豆:392 (菜鸟二级) | 2018-11-23 09:42

谢谢提供的建议,问题已经解决啦

支持(0) 反对(0) 但乱红尘 | 园豆:392 (菜鸟二级) | 2018-11-23 14:40
0

建个多字段索引

v.sibSiid, v.b_code, v.b_name, v.b_munit, v.b_price, v.b_remark

 

这些条件用exist 函数; left join view_chgBills   就可以除掉group by;  group by 非常的好性能,主要从这个着手优化,尽可能变换SQL语句,想办法除掉group by 

 r.sibSiid = sib.sib_siid and isnull(r.b_code,'') = isnull(b.b_code,'') and isnull(r.b_name,'') = isnull(b.b_name,'')
and isnull(r.b_munit,'') = isnull(b.b_munit,'')
and isnull(r.b_price,0) = isnull(b.b_cprice,0) and isnull(r.b_remark,'') = isnull(b.b_remark,'')

 

收获园豆:5
再见2019 | 园豆:11616 (专家六级) | 2018-11-23 10:08

exists函数这里怎么用??不过我将where sib_siid in (select id from c_tree('62b7d3e5-1f37-4fa4-a390-2d746419d58b'))这的in改为exists了,但性能并没有太大变化,但问题正如你所说的,group by太耗性能了,我set statistics io on看到worktable表逻辑读取了77万次

支持(0) 反对(0) 但乱红尘 | 园豆:392 (菜鸟二级) | 2018-11-23 11:27

@淘小涛: 没有数据,没有表,我只能提供思路了

支持(0) 反对(0) 再见2019 | 园豆:11616 (专家六级) | 2018-11-23 13:42

@水淘沙: 谢谢啦,问题已经解决了,从业务上绕开了使用view_chgBills视图,效果达成了一致

支持(0) 反对(0) 但乱红尘 | 园豆:392 (菜鸟二级) | 2018-11-23 14:40
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册