高分 性能问题

[weiluo12]

方式一：
select sum(money) from t_report_fee_details
where indicators_id in (select id from t_base_indicators where pid = 1) and report_id = 211
and type = 1

方式二：
select sum(money) from t_report_fee_details as d
right join (select id from t_base_indicators where pid = 1)as m
on d.indicators_id=m.id and d.report_id = 211
and d.type = 1

请问：这两种方式，哪种方式性能高一些

[黄_瓜]

第二个会高那么一点点吧，呵呵

in效率很低的

[SQL77]

方式一：
select sum(money) from t_report_fee_details
where indicators_id in (select id from t_base_indicators where pid = 1) and report_id = 211
and type = 1

方式二：
select sum(D.money) from t_report_fee_details as d
right join (select id from t_base_indicators where pid = 1)as m
on d.indicators_id=m.id and d.report_id = 211
and d.type = 1


语义不同,RIGHT JOIN 改为INNER JOIN

如果只查一表数据IN和EXISTS比连接快

具体楼主测试一下吧

[黄_瓜]

引用 2 楼 sql77 的回复:

方式一：
select sum(money) from t_report_fee_details
where indicators_id in (select id from t_base_indicators where pid = 1) and report_id = 211
and type = 1

方式二：
select sum(D.money) from t_report_fee_details as d
right join (select id from t_base_indicators where pid = 1)as m
on d.indicators_id=m.id and  d.report_id = 211
and d.type = 1


语义不同,RIGHT JOIN 改为INNER JOIN

如果只查一表数据IN和EXISTS比连接快

具体楼主测试一下吧

学习，我错了，以后不参与讨论效率的问题了。。

[ks_reny]

看執行計畫，個人感覺第二種性能高些。 連接的效率高於子查詢。

[SQL77]

引用 3 楼 beirut 的回复:

引用 2 楼 sql77 的回复:方式一： select sum(money) from t_report_fee_details where indicators_id in (select id from t_base_indicators where pid = 1) and report_id = 211 and type = 1 方式二： select sum(D.money) from t_report_fee_details as d right join (select id from t_base_indicators where pid = 1)as m on d.indicators_id=m.id and  d.report_id = 211 and d.type = 1 语义不同,RIGHT JOIN 改为INNER JOIN 如果只查一表数据IN和EXISTS比连接快 具体楼主测试一下吧
学习，我错了，以后不参与讨论效率的问题了。。

这个也是上次小麦给的文件看到的,呵呵,测试了一下

[ws_hgo]

第二个...................

[weiluo12]

还有一种用逗号的方式：哪位大哥能写写

谢谢各位的回复！

[SQL77]

select 
sum(money) 
from t_report_fee_details as d 
, (select id from t_base_indicators where pid = 1)as m 
on d.indicators_id=m.id and  d.report_id = 211 and d.type = 1 

楼主的RIGHT JOIN 应该要为INNER JOIN 才能改成逗号,不过数据问题的话,RIGHT JOIN 与INNER JOIN等效

[xiaoliaoyun]

在查询分析器里 选上 "Include Actual Execution Plan"(快捷键Ctrl+M),再执行2语句,比较下Execution Plan.看看哪个语句效率高.
我感觉2个语句效率相差不大.IN因为没有关联的外层表,效率>=INNER JOIN

[feixianxxx]

引用 2 楼 sql77 的回复:

方式一：
select sum(money) from t_report_fee_details
where indicators_id in (select id from t_base_indicators where pid = 1) and report_id = 211
and type = 1

方式二：
select sum(D.money) from t_report_fee_details as d
right join (select id from t_base_indicators where pid = 1)as m
on d.indicators_id=m.id and  d.report_id = 211
and d.type = 1


语义不同,RIGHT JOIN 改为INNER JOIN

如果只查一表数据IN和EXISTS比连接快

具体楼主测试一下吧

你还记得啊。。。。
确实如此。。
查询一个表数据还是不要用链接快。。。

[SQL77]

引用 10 楼 feixianxxx 的回复:

引用 2 楼 sql77 的回复:方式一： select sum(money) from t_report_fee_details where indicators_id in (select id from t_base_indicators where pid = 1) and report_id = 211 and type = 1 方式二： select sum(D.money) from t_report_fee_details as d right join (select id from t_base_indicators where pid = 1)as m on d.indicators_id=m.id and  d.report_id = 211 and d.type = 1 语义不同,RIGHT JOIN 改为INNER JOIN 如果只查一表数据IN和EXISTS比连接快 具体楼主测试一下吧

你还记得啊。。。。
确实如此。。
查询一个表数据还是不要用链接快。。。

这个肯定要记得了,忘了两次了呢

[starseeker7]

学习了- -

[weiluo12]

引用 8 楼 sql77 的回复:

SQL codeselectsum(money)from t_report_fee_detailsas d
, (select idfrom t_base_indicatorswhere pid=1)as mon d.indicators_id=m.idand d.report_id=211and d.type=1
楼主的RIGHT JOIN 应该要为INNER JOIN 才能改成逗号,不过数据问题的话,RIGHT JOIN 与INNER JOIN等效

on --->where

[weiluo12]

--in的方式---查询成本21.05%
select sum(money) from t_report_fee_details
where indicators_id in (select id from t_base_indicators where pid = 1) and report_id = 211 
and type = 1

--连接的方式---查询成本36.84%
select sum(money) from t_report_fee_details as d 
right join (select id from t_base_indicators where pid = 1)as m
on d.indicators_id=m.id and  d.report_id = 211 
and d.type = 1

--where(不增加在连接条件上)---查询成本21.05%
select sum(money) from t_report_fee_details as d 
right join (select id from t_base_indicators where pid = 1)as m
on d.indicators_id=m.id where  d.report_id = 211 
and d.type = 1

--逗号的方式---查询成本21.05%
select 
sum(money) 
from t_report_fee_details as d 
, (select id from t_base_indicators where pid = 1)as m 
where d.indicators_id=m.id and  d.report_id = 211 and d.type = 1 

谢谢各位!

[dinghun8leech]

得全分的，不会又是sw80934吧。

[GSDN00A]

恭喜SQL77、ks_reny、xiaoliaoyun 三位回答正确。








恭喜：

weiluo12之马甲sw80934成为今日技术区社区之星。

[GSDN00A]

快来接分

[昵称被占用了]

不结合索引讨论效率往往意义不大

[勤杂工人]

select sum(d.money)
from t_report_fee_details as d
inner join t_base_indicators as m on m.id = d.indicators_id
where d.report_id = 211 and d.type = 1

个人觉得这种好，也一直使用这种方式

[勤杂工人]

楼上写漏了

select sum(d.money)
from t_report_fee_details as d
inner join t_base_indicators as m on m.id = d.indicators_id
where d.report_id = 211 and d.type = 1 and m.pid = 1