主页 / dba / 问题 / 3039
Accepted
garik
Asked: 2011-05-30 23:57:07 +0800 CST

在聚合上使用总计来提高性能

  • 772

我有两个表：这些详细信息的详细信息和总计。

详细信息（慢速解决方案）：

select 
    OrderId             =   r.OrderId                   
  , TotalQty            =   SUM(r.Quantity)
  , TotalGrossConsid    =   SUM(r.Price * r.Quantity)
from dbo.Order r
group by r.OrderId

总计（快速解决方案）：

select 
    t.OrderId                           
  , t.TotalQty          
  , t.TotalGrossConsid  
  , t.IsValid
from dbo.OrderTotal t

有时总数变得无效（某些工作必须重新计算更改的总数，但它会延迟）。正如您所了解的，第二个查询速度更快，并且有效总数比无效总数多。所以我正在寻找一个组合查询，它从第二个表（总计）返回有效总计，并使用第一个慢查询返回动态重新计算的总计。所以我的目标将达到：所有总数都是有效的，响应时间比完全重新计算更快。

这是我的尝试（混合解决方案）：

with fast_static(OrderId, TotalQty, TotalGrossConsid, IsValid)
as
(
    select 
        t.OrderId                           
      , t.TotalQty          
      , t.TotalGrossConsid  
      , t.IsValid
    from dbo.OrderTotal t
)

, slow_dynamic(OrderId, TotalQty, TotalGrossConsid)
(
   select 
        OrderId             =   r.OrderId                   
      , TotalQty            =   SUM(r.Quantity)
      , TotalGrossConsid    =   SUM(r.Price * r.Quantity)
    from dbo.Order r
)
select
    OrderId, TotalQty, TotalGrossConsid
from fast_static 
where IsValid = 1  
union all
select
  OrderId, TotalQty, TotalGrossConsid 
from slow_dynamic s 
    --inner join fast_static ff
    --on ff.OrderId = s.OrderId 
where   --ff.Valid = 0 -- too slow!!!
    s.OrderId in (select OrderId from fast_static f where f.Valid = 0)

我比较了快速解决方案和混合解决方案，我得到了 32% 到 68%（相对查询成本）。如果您可以看到注释变体，它等于 1% 到 99%（太糟糕了）。是否可以改进此查询？

添加

@gbn：

Valid  =  case when i.OrderId is null then 1 else 0 end
...
dbo.OrderTotal t  left join dbo.InvalidOrders i

是的，我有一份重新计算总数的工作，这个过程与查询请求不同步。InvalidOrders 表是一个小表，用于存储记录以知道总计无效（要重新计算）

解决方案

索引视图是最佳选择。请注意 SQL Server 版本（非企业版本的noexpand 提示）并准备重新创建一些数据库对象（SET ANSI_NULLS ON, SET QUOTED_IDENTIFIER ON）以开始在客户端使用索引视图。

sql-server-2008 performance

2 个回答

  1. Best Answer

    我不明白为什么你不能即时聚合以及为什么这么慢。“有效”的想法是解决 OrderTotals 或某些业务流程滞后的解决方法吗

    这两种想法都丢弃了 InvalidOrder 表，这是一种用于不良索引的解决方法。

    • 建议一：

    创建计算列

    ALTER TABLE dbo.Order ADD PriceXQuantity AS Price * Quantity PERSISTED

    添加索引

    CREATE INDEX IX_Totals ON dbo.Order (OrderID) INCLUDE Quantity, PriceXQuantity)

    走着瞧吧

    • 建议二：

    使用索引视图

    CREATE VIEW OrderTotals
WITH SCHEMABINDING
AS
select 
    OrderId             =   r.OrderId                   
  , TotalQty            =   SUM(r.Quantity)
  , TotalGrossConsid    =   SUM(r.Price * r.Quantity)
  , COUNT_BIG(*) AS Dummy
from dbo.Order r
group by r.OrderId
GO
CREATE UNIQUE CLUSTERED INDEX IXCU_OrderTotals ON OrderTotals (OrderId9
GO

    您也可以在此处使用计算列

    • 5

  2. 如果成本不会太高，可以考虑将IsValidflag移入Detail表中并建立索引吗？这会减慢您的事务，但会提高查询性能，因为（可能）大Detail表将在一个大表中访问range scan

    • 2

相关问题