Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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

2 Answers 2

up vote 4 down vote accepted

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

share|improve this answer
    
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
2  
@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
2  
@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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.