如何让聚合执行的更快???
;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该加的索引已加。
如何改造才能使那些聚合内容执行的更快?
...全文
相关推荐
MySQL之聚合查询、分组和排序
分组后可以对每个组执行聚合操作。MySQL使用`GROUP BY`子句来实现这一功能。
bgp路由聚合
通过聚合,可以减少BGP邻居间的更新消息数量,从而降低带宽消耗和处理开销。此外,聚合还可以减少路由表的大小,使路由器能够更快地进行路由查找。
Mongodb中MapReduce实现数据聚合方法详解
MongoDB 还提供了聚合框架(aggregate)和聚合管道,它们通常比 MapReduce 更快,更适合实时或近实时的数据分析。在实际应用中,应根据数据规模和需求选择合适的聚合方法。
Mysql 聚合函数嵌套使用操作
**其他聚合函数和查询技巧:**- 使用`GROUP BY`语句可以将数据按指定列进行分组,然后对每个组应用聚合函数。
如何让SQL运行得更快
**硬件升级**:增加内存、使用更快的硬盘(如SSD)或优化I/O子系统也能提高SQL查询的运行速度。9.


疑难问题
