写这个TSQL语句的更好的方式

Question

我正在寻找一种更好的方法来计算2个相关表的值，现在我加入了"根"有两个"松散相关的表">

select R.ID 
, IsNull(I.[count],0) as [IpWithIncidents]
, IsNull(x.[count],0) as [IpWithOutIncident]
from [dbo].[ISPRange] as r
left outer join (select R.[ID]
, count(distinct i.[CIDR]) AS [count] 
from [dbo].[Incidents] as i 
join [dbo].[ISPRange] as R on i.[CIDR] between R.[CIDR_FROM] and R.[CIDR_TILL] 
group by R.[ID]
) as I on i.[ID]=r.[ID]
left outer join (select R.[ID]
, count(distinct v.[CIDR]) as [count] 
from [dbo].[VisitStats] as v 
join [dbo].[ISPRange] as R on v.[CIDR] between R.[CIDR_FROM] and R.[CIDR_TILL] 
where v.[Incident] = 0 
group by R.[ID]
) as x on x.[ID] = R.[ID]
where (R.[ID]= @RangeId or @RangeId is null)

新增执行计划链接https://1drv.ms/u/s ! AqeBLgyZtSGag_ou1PCoBCS7z1iEJw ? e = zdMuQC

Answer 1

您应该能够在这里使用apply()，并且可能会看到一个更有效的计划。

没有一个最小的可重复的例子，我不能测试以下，但使用outer apply像这样应该是等效的:

select r.ID,
IsNull(i.cnt,0) as IpWithIncidents,
IsNull(x.cnt,0) as IpWithOutIncident
from dbo.ISPRange r
outer apply(
select Count(distinct i.cidr) cnt
from dbo.incidents i
where i.id = r.id 
and i.cidr between r.cidr_from and r.cidr_till
)i
outer apply(
select Count(distinct v.cidr) cnt
from dbo.VisitStats v
where v.id = r.id 
and v.Incidient = 0 
and v.cidr between r.cidr_from and r.cidr_till
)x
where R.Id = @RangeId or @RangeId is null