Using totals on aggregates to improve performance

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?

A

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

StackExchange Database Administrators Q#3039, answer score: 5