# 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?

@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.

• So you have a job that precalculates totals? What does the IsValid flag say? How many rows do you have, do you have indexes, what are the table schemas? – gbn May 30 '11 at 8:13
• So is OrderTotal a view with a LEFT JOIN? Or this the code you use to generate the INSERT/UPDATE for OrderTotal? What abozut schemas + indexes too? Don't hide information... – gbn May 30 '11 at 8:25
• @gbn I have added some comments to my question. Orders, OrderTotals, InvalidOrders: Some code changes order details and put record to the InvalidOrder. Some job recalculate totals based on InvalidTotals and removes record from the InvalidOrder. Some code queries totals. Here are processes as are. – garik May 30 '11 at 8:32
• @garik: you shouldn't need InvalidOrder: this is a hack for poor indexing or poor design. – gbn May 30 '11 at 8:33
• @gbn - orders presumably can have a number of `details`. This looks like an OLAP problem to me - pre-computed aggregates to improve query performance in a datawarehouse – Jack Douglas May 30 '11 at 8:43

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
``````

``````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

• 1) Because of some client-side code requests ALL totals. 2) Because of too many records per second inserts to the details table. Do you still think that indexed view (or recalculated field) will not be too slow (reindexing, recreating view or something else)? I think it is a big question. Change my mind. – garik May 30 '11 at 8:43
• @garik: I have 40,000 rows per second INSERTs. I have aggregates on the same table in real time too. – gbn May 30 '11 at 8:51
• You are right! Indexed view is something awesome. Thank you for real life example (...40,000). Done. – garik May 30 '11 at 9:20
• @gbn - that's a pretty cool feature, is the indexed view always in sync with the base tables or does it refresh when you commit? – Jack Douglas May 30 '11 at 9:34
• @garik: I tend to always use it (even on Enterprise): I've found SQL Server frankyl quite braindead when using indexed views sometimes. You can also wrap the indexed view in another view which does have noexpand because hints propogate "inwards": so no need in your outer view – gbn May 30 '11 at 11:06

If the cost would not be too high, you could consider moving the `IsValid` flag into the `Detail` table and index it? This would slow your transactions but improve query performance as the (presumably) large `Detail` table would be accessed in one big `range scan`