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

[贾桂权]

;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

该加的索引已加。

如何改造才能使那些聚合内容执行的更快？

[Garnett_KG]

--方案二
select 
        a.salesorderid,....  --20个左右的字段
into #tb        
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个连接表        )

CREATE CLUSTERED INDEX CI_salesorderid ON #tb(salesorderid,AirTicketID)


SELECT DISTINCT TOP  10000
    tb.*,
         --以下三个字段执行非常慢，大概需要2000ms左右
    Normal.NormalInsurancePrice,
    Universal.UniversalInsurancePrice,
	UniversalInsurance.UniversalInsuranceCount
from #tb tb
	CROSS APPLY
		 ( select   NormalInsurancePrice=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 Normal
   CROSS APPLY
        ( select    UniversalInsurancePrice=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 Universal
   CROSS APPLY
        ( select    UniversalInsuranceCount=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 UniversalInsurance
order by createtime desc,
        salesorderid,
        airpnrid,
        travelername,
        airticketid,
        startingtime

[贾桂权]

[Quote=引用 10 楼 garnett_kg 的回复:]
try it:

。。。。。
[/Quote]


效率未提高。

[Garnett_KG]

改用#方案呢?

[贾桂权]

一样的，主要是那些聚合时消耗较大，tb内容基本不占资源。

[SQL77]

[Quote=引用 14 楼 jia_guijun 的回复:]
一样的，主要是那些聚合时消耗较大，tb内容基本不占资源。
[/Quote]
参数替换呢,具体看下执行计划看看哪个比较多

[Garnett_KG]

那换另外一种方案

先做聚合，然後再跟 tb 做 join .
.....
from tb
INNER JOIN
(
select p11.salesorderid, NormalInsurancePrice=sum(pl1.salesprice)
from pl1 WITH ( NOLOCK )
inner join it1 WITH ( NOLOCK ) on it1.InsuranceTicketID = pl1.itemidentityid
where
and pl1.itemtypeid = 8
and it1.Status in ( 0, 1 )
GROUP BY p11.salesorderid
) normal
ON normal.salesorderid = tb.salesorderid
.....

[贾桂权]

[Quote=引用 15 楼 sql77 的回复:]
引用 14 楼 jia_guijun 的回复:
一样的，主要是那些聚合时消耗较大，tb内容基本不占资源。

参数替换呢,具体看下执行计划看看哪个比较多
[/Quote]

你那个参数替换只能应用到获取1条记录的语句中。

[贾桂权]

[Quote=引用 16 楼 garnett_kg 的回复:]
那换另外一种方案

先做聚合，然後再跟 tb 做 join .
.....
from tb
    INNER JOIN
        (
        select  p11.salesorderid, NormalInsurancePrice=sum(pl1.salesprice)
          from      pl1 WITH ( NOLOCK )
                    inner join it1 WITH ( NOLOCK ) on it1.InsuranceTicketID = pl1.itemidentityid
          where   
                    and pl1.itemtypeid = 8
                    and it1.Status in ( 0, 1 )
          GROUP BY p11.salesorderid
        ) normal
      ON normal.salesorderid = tb.salesorderid
.....

[/Quote]


这个我自己试过，效率同样。

[Garnett_KG]

ok.没折了。
不帖执行计划，仅从语句上看能优化的地方只能到此为止了。

帖执行计划 & 索引情况

[SQL77]

[Quote=引用 17 楼 jia_guijun 的回复:]
引用 15 楼 sql77 的回复:
引用 14 楼 jia_guijun 的回复:
一样的，主要是那些聚合时消耗较大，tb内容基本不占资源。

参数替换呢,具体看下执行计划看看哪个比较多


你那个参数替换只能应用到获取1条记录的语句中。
[/Quote]
哦,弄错,呵呵,有跟TB连接

[SQL77]

[Quote=引用 19 楼 garnett_kg 的回复:]
ok.没折了。
不帖执行计划，仅从语句上看能优化的地方只能到此为止了。

帖执行计划 & 索引情况
[/Quote]
继续跟着学习,顺便实践一下学的索引知识

[贾桂权]

select    at.AirTicketID,isnull(sum(pl2.salesprice), 0) as UniversalInsurancePrice
          from      pricelist pl2 WITH ( NOLOCK )
                    inner join insuranceticket it2 WITH ( NOLOCK ) on it2.InsuranceTicketID = pl2.itemidentityid
                    inner join AirTicketInsuranceTicket WITH ( NOLOCK ) on AirTicketInsuranceTicket.InsuranceTicketID = it2.InsuranceTicketID
                    inner join AirTicket at WITH ( NOLOCK ) on at.AirTicketID = AirTicketInsuranceTicket.AirTicketID
          where     pl2.itemtypeid = 8
                    and it2.Status in ( 0, 1 )
          group by at.AirTicketID

[img=http://hi.csdn.net/attachment/201001/19/19210_1263887888kJ8S.jpg]怎么帖图？我的博客照片不能上传。[/img]

[Garnett_KG]

1).


select at.AirTicketID,isnull(sum(pl2.salesprice), 0) as UniversalInsurancePrice
from pricelist pl2 WITH ( NOLOCK )
inner join insuranceticket it2 WITH ( NOLOCK ) on it2.InsuranceTicketID = pl2.itemidentityid
inner join AirTicketInsuranceTicket WITH ( NOLOCK ) on AirTicketInsuranceTicket.InsuranceTicketID = it2.InsuranceTicketID
inner HASH join AirTicket at WITH ( NOLOCK ) on at.AirTicketID = AirTicketInsuranceTicket.AirTicketID
where pl2.itemtypeid = 8
and it2.Status in ( 0, 1 )
group by at.AirTicketID

2).
如果 1方法效果不好， DROP 掉 AirTicket.IX_AirTicket 索引.

[SQL77]

[Quote=引用 23 楼 garnett_kg 的回复:]
1).


select    at.AirTicketID,isnull(sum(pl2.salesprice), 0) as UniversalInsurancePrice
          from      pricelist pl2 WITH ( NOLOCK )
                    inner join insuranceticket it2 WITH ( NOLOCK ) on it2.InsuranceTicketID = pl2.itemidentityid
                    inner join AirTicketInsuranceTicket WITH ( NOLOCK ) on AirTicketInsuranceTicket.InsuranceTicketID = it2.InsuranceTicketID
                    inner HASH join AirTicket at WITH ( NOLOCK ) on at.AirTicketID = AirTicketInsuranceTicket.AirTicketID
          where    pl2.itemtypeid = 8
                    and it2.Status in ( 0, 1 )
          group by at.AirTicketID

2).
如果 1方法效果不好， DROP 掉 AirTicket.IX_AirTicket 索引.

[/Quote]
一个索引查找要花费这么多?

[clxsl_huang]

纯学习

[lsd123]

.

[贾桂权]

.

[黄_瓜]

[Quote=引用 22 楼 jia_guijun 的回复:]
SQL codeselect at.AirTicketID,isnull(sum(pl2.salesprice),0)as UniversalInsurancePricefrom pricelist pl2WITH ( NOLOCK )innerjoin insuranceticket it2WITH ( NOLOCK )on it2.InsuranceTicketID= pl2.itemidentityidinnerjoin AirTicketInsuranceTicketWITH ( NOLOCK )on AirTicketInsuranceTicket.InsuranceTicketID= it2.InsuranceTicketIDinnerjoin AirTicket atWITH ( NOLOCK )on at.AirTicketID= AirTicketInsuranceTicket.AirTicketIDwhere pl2.itemtypeid=8and it2.Statusin (0,1 )groupby at.AirTicketID



[/Quote]

索引查找开销好大呀

[Mr_Nice]

路过，学习...

[贾桂权]

还有没有其他的办法？还是老样子。