SqlServer的语句优化

[删库到跑路]

          select 
                  p9.ID,
                  p9.ProductNum,
		  水泥=sum(case when p8.ID = 1 then p1.UPNum else 0 end)-0.3,
		  砂=sum(case when p8.ID = 10 then p1.UPNum else 0 end)-0.3,
		  石=sum(case when p8.ID = 20 then p1.UPNum else 0 end),
		  粉煤灰=sum(case when p8.ID = 30 then p1.UPNum else 0 end),
		  矿粉=sum(case when p8.ID = 40 then p1.UPNum else 0 end)-0.1,
		  外加剂=sum(case when p8.ID = 50 and p7.StyleName not like '%膨胀剂%' then p1.UPNum else 0 end),
		  膨胀剂=sum(case when p8.ID = 50 and p7.StyleName like '%膨胀剂%' then p1.UPNum else 0 end),
		  水=sum(case when p8.ID = 60  then p1.UPNum else 0 end)
		  from A as p1
		  inner join B as p2 on p1.TestYieldAppraiseProduct_ID = p2.id
		  inner join C as p3 on p2.TestMixSelectProduct_ID = p3.ID
		  inner join D as m3 on m3.ID=p3.TestMixProduct_ID
		  inner join E as p6 on m3.StuffFactoryProduct_ID = p6.ID
		  inner join F as p7 on p6.ProductStyle_ID = p7.ID
		  inner join G as p8 on p7.ProductType_ID = p8.ID
		  inner join H as p9 on p1.QueryMS_ID = p9.ID
		  group by  p9.ID,p9.ProductNum

A表数据100w,B,C表数据40w左右，H表为15w,其余为小表，执行语句为6s,得出15w数据，现在看到执行计划找到数据慢的地方为group by p9.ID,去掉这个分组就只需要2s,求大神看看有没有优化的方子

[吉普赛的歌]

1. 注释如下的两块， 需要多少秒？ 2. 每个表的连接字段都有索引吗？

SELECT p9.ID,
       p9.ProductNum,
       水泥 = SUM(CASE WHEN p8.ID = 1 THEN p1.UPNum ELSE 0 END) -0.3,
       砂 = SUM(CASE WHEN p8.ID = 10 THEN p1.UPNum ELSE 0 END) -0.3,
       石 = SUM(CASE WHEN p8.ID = 20 THEN p1.UPNum ELSE 0 END),
       粉煤灰 = SUM(CASE WHEN p8.ID = 30 THEN p1.UPNum ELSE 0 END),
       矿粉 = SUM(CASE WHEN p8.ID = 40 THEN p1.UPNum ELSE 0 END) -0.1,
       --外加剂 = SUM(
       --    CASE 
       --         WHEN p8.ID = 50
       --    AND p7.StyleName NOT LIKE '%膨胀剂%' THEN p1.UPNum ELSE 0 END
       --),
       --膨胀剂 = SUM(
       --    CASE 
       --         WHEN p8.ID = 50
       --    AND p7.StyleName LIKE '%膨胀剂%' THEN p1.UPNum ELSE 0 END
       --),
       水 = SUM(CASE WHEN p8.ID = 60 THEN p1.UPNum ELSE 0 END)
FROM   A             AS p1
       INNER JOIN B  AS p2
            ON  p1.TestYieldAppraiseProduct_ID = p2.id
       INNER JOIN C  AS p3
            ON  p2.TestMixSelectProduct_ID = p3.ID
       INNER JOIN D  AS m3
            ON  m3.ID = p3.TestMixProduct_ID
       INNER JOIN E  AS p6
            ON  m3.StuffFactoryProduct_ID = p6.ID
       INNER JOIN F  AS p7
            ON  p6.ProductStyle_ID = p7.ID
       INNER JOIN G  AS p8
            ON  p7.ProductType_ID = p8.ID
       INNER JOIN H  AS p9
            ON  p1.QueryMS_ID = p9.ID
GROUP BY
       p9.ID,
       p9.ProductNum

[删库到跑路]

[quote=引用 1 楼 yenange 的回复:] 1. 注释如下的两块， 需要多少秒？ 2. 每个表的连接字段都有索引吗？ [quote] 注释之后还是5s，这两个也是不能少的 每个表的索引都很正常

[xuson]

SELECT ID,
       ProductNum,
       水泥 = Sum(水泥),
       砂 = Sum(砂),
       石 = Sum(石),
       粉煤灰 = Sum(粉煤灰),
       矿粉 = Sum(矿粉),
       外加剂 =Sum(外加剂),
       膨胀剂 = Sum(膨胀剂),
       水=Sum(水)
From(
Select p9.ID,
       p9.ProductNum,
       水泥 = (CASE WHEN p8.ID = 1 THEN p1.UPNum ELSE 0 END) -0.3,
       砂 = (CASE WHEN p8.ID = 10 THEN p1.UPNum ELSE 0 END) -0.3,
       石 = (CASE WHEN p8.ID = 20 THEN p1.UPNum ELSE 0 END),
       粉煤灰 = (CASE WHEN p8.ID = 30 THEN p1.UPNum ELSE 0 END),
       矿粉 = (CASE WHEN p8.ID = 40 THEN p1.UPNum ELSE 0 END) -0.1,
       外加剂 = (
           CASE 
                WHEN p8.ID = 50
           AND p7.StyleName NOT LIKE '%膨胀剂%' THEN p1.UPNum ELSE 0 END
       ),
       膨胀剂 = (
           CASE 
                WHEN p8.ID = 50
           AND p7.StyleName LIKE '%膨胀剂%' THEN p1.UPNum ELSE 0 END
       ),
       水 = (CASE WHEN p8.ID = 60 THEN p1.UPNum ELSE 0 END)
FROM   A             AS p1
       INNER JOIN B  AS p2
            ON  p1.TestYieldAppraiseProduct_ID = p2.id
       INNER JOIN C  AS p3
            ON  p2.TestMixSelectProduct_ID = p3.ID
       INNER JOIN D  AS m3
            ON  m3.ID = p3.TestMixProduct_ID
       INNER JOIN E  AS p6
            ON  m3.StuffFactoryProduct_ID = p6.ID
       INNER JOIN F  AS p7
            ON  p6.ProductStyle_ID = p7.ID
       INNER JOIN G  AS p8
            ON  p7.ProductType_ID = p8.ID
       INNER JOIN H  AS p9
            ON  p1.QueryMS_ID = p9.ID
)Data
GROUP BY ID,ProductNum

[吉普赛的歌]

你可以拆临时表试试。 不过， 你这个优化空间有限， 因为你这是聚合， 而且一点条件都没有， 相当于每个表都得全表扫描。

[删库到跑路]

引用 3 楼 xuson 的回复:

兄弟，你这还是满了两秒

[吉普赛的歌]

建议你把这一块做成报表， 简单点来说建立一个结果表， 每天用 SQL作业 定时往里面插入数据。 用户需要查询的时候， 直接查结果表就可以了， 这个速度肯定是杠杠的。

[吉普赛的歌]

我以前在很多贴子说过， 什么叫优化？ 万里挑一才是。如果做不到， 百里挑一也行。 但你这个没得挑， 全部都要连接、计算。 只能通过报表， 提前计算来达到快速查询的效果。 实际上数据量大了， 很多时候只能这样， 实时报表根本做不到。

[二月十六]

语句比较简单了，就是关联表聚合读取。
另外不需要一下读取所有数据吧？