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?
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)
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.