;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
该加的索引已加。
如何改造才能使那些聚合内容执行的更快？
