SQL语句效率

[nightmaple]

select * from NiMrRsvPoBatch a
left join (select a.MrNo,a.MatKey,SonMrRsvQty=
sum(Isnull(a.RsvQty,0)-isnull(a.RsvUsage,0)+isnull(a.NetDnQty,0)) 
from NiMrRsvPoBatch a
inner join TxNi_MrDet b on a.MrNo=b.SonMrNo
group by a.MrNo,a.MatKey) b on a.MrNo=b.MrNo and a.MatKey=b.MatKey
where a.MrNo='10/0129-JM-004'

select *,SonMrRsvQty=
(select sum(Isnull(g.RsvQty,0)-isnull(g.RsvUsage,0)+isnull(g.NetDnQty,0))    
from NiMrRsvPoBatch g(nolock) 
inner join TxNi_MrDet b(nolock) on g.MrNo=b.SonMrNo
where b.MrNo=a.MrNo and g.MatKey=a.MatKey)  
from NiMrRsvPoBatch a
where a.MrNo='10/0129-JM-004'

这两个语句那个效率高？

[xuam]

上面的吧

[billpu]

看执行计划就一目了然

[宇峰科技]

[Quote=引用 2 楼 billpu 的回复:]
看执行计划就一目了然
[/Quote]

[Mr_Nice]

[Quote=引用 2 楼 billpu 的回复:]

看执行计划就一目了然
[/Quote]

需要看执行计划...

[xiaoxiao8372]

我问一下，那个执行计划怎么看啊。！！

[nightmaple]

SET SHOWPLAN_TEXT on
go

select * from NiMrRsvPoBatch a
left join (select a.MrNo,a.MatKey,SonMrRsvQty=
sum(Isnull(a.RsvQty,0)-isnull(a.RsvUsage,0)+isnull(a.NetDnQty,0)) 
from NiMrRsvPoBatch a
inner join TxNi_MrDet b on a.MrNo=b.SonMrNo
group by a.MrNo,a.MatKey) b on a.MrNo=b.MrNo and a.MatKey=b.MatKey
where a.MrNo='10/0129-JM-004'
go

select *,SonMrRsvBal=
(select sum(Isnull(g.RsvQty,0)-isnull(g.RsvUsage,0)+isnull(g.NetDnQty,0))    
from NiMrRsvPoBatch g(nolock) 
inner join TxNi_MrDet b(nolock) on g.MrNo=b.SonMrNo
where b.MrNo=a.MrNo and g.MatKey=a.MatKey)  
from NiMrRsvPoBatch a
where a.MrNo='10/0129-JM-004'
go

SET SHOWPLAN_TEXT off
go

用SQL的话，这样可以看到，我之前还真没用过，不怎么会看~~~

[claro]

纯帮顶

[jaydom]

我是来拿分的

[jaydom]

友情帮顶

[nightmaple]

SET SHOWPLAN_TEXT on
go

select * from NiMrRsvPoBatch a
left join (select a.MrNo,a.MatKey,SonMrRsvQty=
sum(Isnull(a.RsvQty,0)-isnull(a.RsvUsage,0)+isnull(a.NetDnQty,0)) 
from NiMrRsvPoBatch a
inner join TxNi_MrDet b on a.MrNo=b.SonMrNo
group by a.MrNo,a.MatKey) b on a.MrNo=b.MrNo and a.MatKey=b.MatKey
where a.MrNo='10/0129-JM-004'
go

--结果：StmtText列的值如下
select * from NiMrRsvPoBatch a  left join (select a.MrNo,a.MatKey,SonMrRsvQty=  sum(Isnull(a.RsvQty,0)-isnull(a.RsvUsage,0)+isnull(a.NetDnQty,0))   from NiMrRsvPoBatch a  inner join TxNi_MrDet b on a.MrNo=b.SonMrNo  group by a.MrNo,a.MatKey) b on a.MrNo=b.MrNo and a.MatKey=b.MatKey  where a.MrNo='10/0129-JM-004'
  |--Hash Match(Right Outer Join, HASH:([a].[MatKey])=([a].[MatKey]))
       |--Compute Scalar(DEFINE:([a].[MrNo]=[DtradeSimpleGarment01].[dbo].[NiMrRsvPoBatch].[MrNo] as [a].[MrNo], [a].[MatKey]=[DtradeSimpleGarment01].[dbo].[NiMrRsvPoBatch].[MatKey] as [a].[MatKey], [Expr1007]=[Expr1007]))
       |    |--Stream Aggregate(GROUP BY:([a].[MatKey]) DEFINE:([Expr1007]=SUM([Expr1011]), [a].[MrNo]=ANY([DtradeSimpleGarment01].[dbo].[NiMrRsvPoBatch].[MrNo] as [a].[MrNo])))
       |         |--Nested Loops(Inner Join)
       |              |--Index Seek(OBJECT:([DtradeSimpleGarment01].[dbo].[TxNi_MrDet].[UXTxNi_MrDetSonMr] AS [b]), SEEK:([b].[SonMrNo]='10/0129-JM-004') ORDERED FORWARD)
       |              |--Compute Scalar(DEFINE:([Expr1011]=(isnull([DtradeSimpleGarment01].[dbo].[NiMrRsvPoBatch].[RsvQty] as [a].[RsvQty],(0.000))-isnull([DtradeSimpleGarment01].[dbo].[NiMrRsvPoBatch].[RsvUsage] as [a].[RsvUsage],(0.000)))+isnull([DtradeSimpleGarment01].[dbo].[NiMrRsvPoBatch].[NetDnQty] as [a].[NetDnQty],(0.000))))
       |                   |--Clustered Index Seek(OBJECT:([DtradeSimpleGarment01].[dbo].[NiMrRsvPoBatch].[PK_NiMrRsvPoBatch] AS [a]), SEEK:([a].[MrNo]='10/0129-JM-004') ORDERED FORWARD)
       |--Clustered Index Seek(OBJECT:([DtradeSimpleGarment01].[dbo].[NiMrRsvPoBatch].[PK_NiMrRsvPoBatch] AS [a]), SEEK:([a].[MrNo]='10/0129-JM-004') ORDERED FORWARD)

我不会看，我想知道是先执行left join的条件a.MrNo=b.MrNo and a.MatKey=b.MatKey进行筛选，
还是先执行select a.MrNo,a.MatKey,SonMrRsvQty=
sum(Isnull(a.RsvQty,0)-isnull(a.RsvUsage,0)+isnull(a.NetDnQty,0))
from NiMrRsvPoBatch a
inner join TxNi_MrDet b on a.MrNo=b.SonMrNo
group by a.MrNo,a.MatKey生成临时表，再进行left join的关联？