thanks for this article.
A solution to this problem is to use procedural code (at least for SQL2K).
Probably not the intention of the author...:D
DECLARE @tblResult TABLE (asset_id char(9), sum_amt decimal(19,3), count_amt int)
DECLARE @asset_id char(9), @sum_amt decimal(19,3), @count_amt int
SELECT @asset_id = ''-- This is our iteration key
SET @sum_amt = 0-- Will be incremented for each record
SET @count_amt = 0-- Will be incremented for each record
WHILE 1=1 BEGIN-- Loop over all asset_ids
-- Load the next asset_id and add amount to running total var and increase count for the new record.
SELECT TOP 1 @asset_id = asset_id, @sum_amt = @sum_amt + cap_issue_amt, @count_amt = @count_amt + 1
FROM dbo.RRefasset WHERE asset_id > @asset_id ORDER BY asset_id ASC
IF @@ROWCOUNT = 0 BREAK
-- Insert new row into temporary results table
INSERT @tblResult SELECT @asset_id, @sum_amt , @count_amt
SELECT asset_id, sum_amt, count_amt FROM @tblResult
This runs in 3 seconds on my machine for final 40000 rows.
If I understand this correctly, with the triangle there is no perfect execution plan since the engine starts with matching only a few rows ("index seek class") and then ends up with matching the full table ("table scan class") for the last rows. Do you have more details on this?
(Edit: Added linebreak in code to avoid scrolling)