Sql-server – Using totals on aggregates to improve performance

aggregatematerialized-viewperformancequerysql-server-2008

I have two tables: details and totals of these details.

Details (Slow solution):

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

Totals (Fast solution):

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

Sometimes totals become invalid (some job has to recalculate changed totals but it delays).
As you understand the second query is faster and the number of valid totals is more then invalid ones.
So I am looking for a combined query that returns valid totals from the second table (totals) and returns dynamically recalculated totals by using the first slow query. So my goal will be reached: all totals are valid and time to response is faster then full recalculation.

Here is my attempt (Hybrid solution):

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)

I have compared the Fast solution and Hybrid one, I got 32% to 68% (relative query costs). If you can see commented variant it equals to 1% to 99% (too bad). Is it possible to improve this query?

ADDED

@gbn:

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

Yes, I have a job to recalculate totals and this process is not synchronized to query requests. InvalidOrders tables is a little one that store records to know that totals is invalid (to be recalculated)

SOLUTION

Indexed views are the best choice. Be aware on SQL Server edition (noexpand hint for non-enterprise editions) and be ready to recreate some database objects (SET ANSI_NULLS ON, SET QUOTED_IDENTIFIER ON) to start using indexed views on client side.

Best Answer

I can't understand why you can't aggregate on the fly and why it's so slow. Is the "Valid" idea a workaround to deal with the lag of OrderTotals or some business process

Both of these ideas discard the InvalidOrder table which is a workaround for poor indexing.

  • Suggestion 1:

Create a computed column

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

Add an index

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

See what happens

  • Suggestion 2:

Use an Indexed view

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

You can use the computed column here too

Related Question