讨论常用SQL语句执行效率问题

－－1、下面三个执行计划是一样的，效率也是一样的，常用第一种写法-----
select a.pid,a.pnm,a.smanno,b.snm,b.memo from pro a,user2 b where a.smanno=b.sid
select a.pid,a.pnm,a.smanno,b.snm,b.memo from pro a inner join user2 b on a.smanno=b.sid
select a.pid,a.pnm,a.smanno,b.snm,b.memo from pro a cross join user2 b where a.smanno=b.sid

－－2、下面二个执行计划一样，效率是前者快，固看情况应用：inner、left、right join－－－
select a.pid,a.pnm,a.smanno,b.snm from pro a inner join user2 b on a.smanno=b.sid
select a.pid,a.pnm,a.smanno,b.snm from pro a left join user2 b on a.smanno=b.sid

－－3、下面二个查询结果集一样，执行计划不一样，效率前者快－－
select a.pid,a.pnm,a.smanno,b.snm from pro a left join user2 b on a.smanno=b.sid
select pid,pnm,smanno,(select snm from user2 as b where b.sid=a.smanno) snm from pro a

－－4、下面二个查询结果集一样，执行计划不一样，效率前者快，后者相当于执行两个left join－－
select a.pid,a.pnm,a.smanno,b.snm,b.tel from pro a left join user2 b on a.smanno=b.sid
select pid,pnm,smanno,
  (select snm from user2 as b where b.sid=a.smanno) snm,
  (select tel from user2 as b where b.sid=a.smanno) tel from pro a

－－5、下面二个查询结果集一样的，执行计划不一样，效率后者快－－
select a.pid,a.pnm,a.smanno,sum(b.num) qty 
from pro a,stock b 
where a.pid=b.pid 
group by a.pid,a.pnm,a.smanno

select a.pid,a.pnm,a.smanno,b.qty 
from pro a,(select pid,sum(num) qty from stock group by pid) b 
where a.pid=b.pid

－－6、查询结果集一样，效率前者快－－
select a.pid,a.pnm,a.smanno,b.qty,b.ps 
from pro a left join (select pid,sum(num) qty,sum(ps) ps from stock group by pid) b 
on a.pid=b.pid

select pid,pnm,smanno,
  (select sum(num) from stock b where a.pid=b.pid) qty,
  (select sum(ps) from stock b where a.pid=b.pid) ps
from pro a  

---老SQL：---
declare @mSYS001 varchar(20),@mSYS002 varchar(20)
select @mSYS001 = SYA002 from XT_TPASYA where SYA001 = 'SYS001'
select @mSYS002 = SYA002 from XT_TPASYA where SYA001 = 'SYS002'

select BGA003 as 生产批号,BGA002 as 日期,
(select DAA002 from TPADAA where DAA001 = BGA012) as 班别,
BGA018 as 机号,(select DBA002 from TPADBA where DBA001 = BGA007) as 员工,
RAA008 as 规格,RAA007 as 材质, BGA006 as 生产数量PCS,
isnull((select sum(b.BGA024) from XT_SGMBGA b where b.BGA003 = a.BGA003 and b.BGA004 = @mSYS002 and b.BGA980 = a.BGA980 ),0) as 初检前重量KG,
isnull((select sum(b.BGA981) from XT_SGMBGA b where b.BGA003 = a.BGA003 and b.BGA004 = @mSYS002 and b.BGA980 = a.BGA980 ),0) as 初检数量KG,
isnull((select sum(b.BGA006) from XT_SGMBGA b where b.BGA003 = a.BGA003 and b.BGA004 = @mSYS002 and b.BGA980 = a.BGA980 ),0) as 初检数量PCS,
isnull((select sum(b.BGA023) from XT_SGMBGA b where b.BGA003 = a.BGA003 and b.BGA004 = @mSYS002 and b.BGA980 = a.BGA980 ),0) as 初检不良重量KG,
case isnull((select sum(b.BGA024) from XT_SGMBGA b where b.BGA003 = a.BGA003 and b.BGA004 = @mSYS002 and b.BGA980 = a.BGA980 ),0) when 0 then 0 
else isnull((select sum(b.BGA023) from XT_SGMBGA b where b.BGA003 = a.BGA003 and b.BGA004 = @mSYS002 and b.BGA980 = a.BGA980 ),0)
/isnull((select sum(b.BGA024) from XT_SGMBGA b where b.BGA003 = a.BGA003 and b.BGA004 = @mSYS002 and b.BGA980 = a.BGA980 ),0)*100 end as [不良率%]  
from XT_SGMBGA a,XT_SGMRAA where RAA001 = a.BGA003 and a.BGA004 = @mSYS001

---进行优化后的SQL---
declare @mSYS001 varchar(20),@mSYS002 varchar(20)
select @mSYS001 = SYA002 from XT_TPASYA where SYA001 = 'SYS001'
select @mSYS002 = SYA002 from XT_TPASYA where SYA001 = 'SYS002'

select a.BGA003 as 生产批号,a.BGA002 as 日期,
DAA002 as 班别,
BGA018 as 机号,DBA002 as 员工,
RAA008 as 规格,RAA007 as 材质, BGA006 as 生产数量PCS,
isnull(e.n1,0) as 初检前重量KG,
isnull(e.n2,0) as 初检数量KG,
isnull(e.n3,0) as 初检数量PCS,
isnull(e.n4,0) as 初检不良重量KG,
case isnull(e.n1,0) when 0 then 0 else e.n4/e.n1 end as [不良率%]  
from XT_SGMBGA a 
left join TPADAA c on DAA001 = BGA012 
left join TPADBA d on DBA001 = BGA007 
left join (select BGA003,BGA980,sum(BGA024) n1,sum(BGA981) n2,sum(BGA006) n3,sum(BGA023) n4 from XT_SGMBGA 
where BGA004=@mSYS002
  group by BGA003,BGA980) e on a.BGA003=e.BGA003 and a.BGA980=e.BGA980 
cross join XT_SGMRAA b 
where b.RAA001 = a.BGA003 and a.BGA004 = @mSYS001

-------------------------
这些是今天作的一个小结，
有不足地方，还请各大侠补充下啦

该回复于2011-12-06 15:23:50被管理员删除

fredrickhu (小F) 等　级： 3 2 更多勋章

#2楼 得分：0回复于：2011-12-06 15:27:39 晕 回复谢谢分享也能被删除？

－－2、下面二个执行计划一样，效率是前者快，固看情况应用：inner、left、right join－－－
select a.pid,a.pnm,a.smanno,b.snm from pro a inner join user2 b on a.smanno=b.sid
select a.pid,a.pnm,a.smanno,b.snm from pro a left join user2 b on a.smanno=b.sid

不等价

该回复于2011-12-06 16:01:38被管理员删除

fredrickhu (小F) 等　级： 3 2 更多勋章

#6楼 得分：0回复于：2011-12-06 15:49:23 下面二个查询结果集一样，执行计划不一样，效率前者快－－ select a.pid,a.pnm,a.smanno,b.snm from pro a left join user2 b on a.smanno=b.sid select pid,pnm,smanno,(select snm from user2 as b where b.sid=a.smanno) snm from pro a 如果是结果集一样的话 证明是inner join了

[Quote=引用 4 楼 watermm 的回复:]

是不等价啊，

前者是交叉联接的结果集，即共用部分；
后者是左向外联接的结果集，即左边所有结果集为基准。

前者就是left join了，
后者也相当于let join。