如何让聚合执行的更快???

贾桂权 贾桂权 墨衍会员 · AI 创作全网分发 墨衍智能分发 通过墨衍一键同步至各平台 可同步平台 CSDN微信公众号微博知乎掘金百家号博客园抖音小红书 你的文章也可以这样分发 墨衍会员 ¥399起/年,写一次发全网 我也要 + 加入社区

;with tb as(--这个括号中的内容执行很快,5ms左右
select
a.salesorderid,.... --20个左右的字段
from a
inner join b WITH ( NOLOCK ) on b.salesorderid = a.salesorderid
inner join c WITH ( NOLOCK ) on c.salesorderid = a.salesorderid
inner join d WITH ( NOLOCK ) on d.airbookingitemid = c.airbookingitemid
inner join ....还有7,8个连接表 )

select distinct top 10000
tb.*,
--以下三个字段执行非常慢,大概需要2000ms左右
( select sum(pl1.salesprice)
from pl1 WITH ( NOLOCK )
inner join it1 WITH ( NOLOCK ) on it1.InsuranceTicketID = pl1.itemidentityid
where pl1.salesorderid = tb.salesorderid
and pl1.itemtypeid = 8
and it1.Status in ( 0, 1 )
) as NormalInsurancePrice,

( select isnull(sum(pl2.salesprice), 0)
from pl2 WITH ( NOLOCK )
inner join it2 WITH ( NOLOCK ) on it2.InsuranceTicketID = pl2.itemidentityid
inner join x WITH ( NOLOCK ) on x.InsuranceTicketID = it2.InsuranceTicketID
inner join at WITH ( NOLOCK ) on at.AirTicketID = x.AirTicketID
where at.AirTicketID = tb.AirTicketID
and pl2.itemtypeid = 8
and it2.Status in ( 0, 1 )
) as UniversalInsurancePrice,

( select isnull(count(*), 1)
from ait2 WITH ( NOLOCK )
inner join at2 WITH ( NOLOCK ) on at2.AirTicketID = ait2.AirTicketID
inner join abi2 WITH ( NOLOCK ) on abi2.airbookingitemid = at2.airbookingitemid
and abi2.salesorderid = tb.salesorderid
) as UniversalInsuranceCount
from tb
order by createtime desc,
salesorderid,
airpnrid,
travelername,
airticketid,
startingtime




该加的索引已加。

如何改造才能使那些聚合内容执行的更快?
...全文
评论 30
收藏 已收藏
分享
返回顶部
贾桂权
疑难问题 22,299 成员 121,727 内容