Table Variable Solution
The next solution is the most efficient pure Transact-SQL one I have figured out so far. To reach that solution let’s look at Books Online (BOL), update statement topic, local variables in set clause:
@variable
Is a declared variable that is set to the value returned by expression.
SET @variable = column = expression sets the variable to the same value as the column. This differs from SET @variable = column, column = expression, which sets the variable to the pre-update value of the column.
That means we can use a stored procedure local variable to save the cumulative total and update the total column of the table variable we created to return the result set. Here is the code:
create procedure dbo.Sales_sel_by_StoreID_ProductID
@StoreID int,
@ProductID int
as begin
set noCount on
declare @report table(
transactionID int primary key clustered,
transactionTime dateTime not null,
amount money not null,
total money not null
)
declare @total money
set @total = 0
insert into @report(transactionID, transactionTime, amount, total)
select a.transactionID,
a.transactionTime,
a.amount,
0.0 as total
from
dbo.Sales a
where
a.StoreID = @StoreID and
a.ProductID = @ProductID
update r
set @total = r.total = @total + amount
from @report r
select r.transactionID, r.transactionTime, r.amount, r.total
from @report r
order by r.transactionID
set noCount off
end
This is the most efficient data set based solution! However, there is a small problem with this approach. How do we know that the update will be done exactly in the order of increasing transactionID? Is it enough to have a clustered primary key on that column to be safe? I think so, but I can’t prove it. A future service pack could change the way the execution plan for this procedure is built. Even though I don’t expect the way critical update executes to change any time soon, to be absolutely safe we have to force the update order more explicitly. One way to do that would be to use a derived table with an order by clause. An order by clause is not allowed in derived tables unless the “top” option is used too. So one possible “safe” solution is below:
create procedure dbo.Sales_sel_by_StoreID_ProductID
@StoreID int,
@ProductID int
as begin
set noCount on
declare @report table(
transactionID int primary key clustered,
amount money not null,
total money not null
)
declare @total money
set @total = 0
insert into @report(transactionID, amount, total)
select a.transactionID,
a.amount,
0.0 s total
from
dbo.Sales a (index=ix_sales)
where
a.StoreID = @StoreID and
a.ProductID = @ProductID
update r
set @total = r.total = @total + amount
from (select top 100 percent
transactionID
from @report
order by transactionID) as s
inner loop join @report r on r.transactionID = s.transactionID
option (force order)
select s.transactionID, s.transactionTime, s.amount, r.total
from @report r
join dbo.sales s on s.transactionID = r.transactionID
order by r.transactionID
set noCount off
end
However, if you try to change the order to descending you may be surprised by the result. The problem is that “select top N … order by” guarantees that the first N rows based on order will be returned, but the order they are returned is not guaranteed.
The only reliable way to force the update order is to force an index on the sales table:
create procedure dbo.Sales_sel_by_StoreID_ProductID
@StoreID int,
@ProductID int
as begin
set noCount on
declare @report table(
transactionID int primary key clustered,
total money not null
)
declare @total money
set @total = 0
insert into @report(transactionID, total)
select a.transactionID,
0.0 as total
from
dbo.Sales a (index=ix_sales)
where
a.StoreID = @StoreID and
a.ProductID = @ProductID
update r
set @total = r.total = @total + amount
from
dbo.Sales s (index=ix_sales)
join @report r on r.transactionID = s.transactionID
where
s.StoreID = @StoreID and
s.ProductID = @ProductID
option (force order)
select s.transactionID, s.transactionTime, s.amount, r.total
from @report r
join dbo.sales s on s.transactionID = r.transactionID
order by r.transactionID
set noCount off
end
It was not enough to have the sales table rows ordered using the index and joined with the table variable. We had to make sure that the inner table used is a derived one. For that reason, we added option (force order). The force order hint doesn’t force the data order; instead, it actually forces the join order.
Quote from BOL:
FORCE ORDER
Specifies that the join order indicated by the query syntax is preserved during query optimization.
On top of that, we didn’t want any fancy join algorithm to change the order in which rows are updated, so we used a loop join hint.
This solution is slower than the first table variable based solution but it is still more efficient than a solution using a cursor. The solution will work as long as Microsoft supports the query hints used.
Conclusion
None of the solutions mentioned is the one I prefer. The solution I recommend is to return a “raw” data set to the client and let the client loop through rows and calculate the running total. The query returning “raw” data is the fastest and the least resource intensive. Implementation of running total calculation is fast and straight forward for programming languages used on the client side.
]]>