sql - SQL Server 中按Cube 分组的优化

Question

我想对一个包含 9 列和超过 1.07 亿行的表进行 GROUP BY CUBE。这是我的代码示例:

 select     id
            ,case when grouping(cod_01)    = 0 then cod_01    else 0   end cod_01
            ,case when grouping(cod_02)     = 0 then cod_02     else 0   end cod_02
            ,case when grouping(cod_03)        = 0 then cod_03        else 0   end cod_03
            ,case when grouping(cod_04)   = 0 then cod_04   else 0   end cod_04
            ,case when grouping(cod_05) = 0 then cod_05 else 0   end cod_05
            ,case when grouping(input)    = 0 then input    else '0' end cod_input
            ,date
            ,historical
            ,COUNT(distinct pp) value
     from tmp.test
     where final_state in ('A','B')
     group by id
              ,cod_01
              ,cod_02
              ,cod_03
              ,cod_04
              ,cod_05
              ,input
              ,date 
              ,historical
               with cube 
     having GROUPING(id) = 0
            and GROUPING(cod_02) = 0
            and GROUPING(cod_03) = 0
            and GROUPING(date) = 0
            and GROUPING(historical) = 0

这是在 SQL Server 中运行的。

对于 10K 行，它花费了 7 秒，但是当我将行数增加到总数 1.07 亿时，它花费了超过 24 小时。

我怎样才能改进我的句子？有更好的方法吗？

Answer 1

除了在 final_state 列上添加索引(如果它有足够的选择性)或什至创建覆盖索引(占用大量存储空间并会降低插入/更新性能)等显而易见的事情之外，您还可以使用 Grouping Sets而不是 Cube+Having。

它会按您实际需要的列组合聚合数据，而不是先用 Cube 计算所有可能的组合，然后用 Having 过滤它们。这可能会更快，但如果此查询的结果也有数千万行，则不要指望会出现任何问题。

我在我的服务器 (MSSQL 2012) 上对此进行了测试，结果表明使用 Cube+ 的查询执行了 6 次单独的索引扫描，然后连接流，而使用产生相同结果的分组集的查询只执行了一次扫描并且是快几倍。