TSQL Replacing "Quirky Update" Calculations with Windowing and CTE

Question

I am trying to come up with an alternative to using the "Quirky Update" using windowing and CTE for a somewhat complex calculation of "running performance". The quick math for running performance is ((1 + Running) * (1 + Daily)) - 1. This running needs to be updated for the current row and then used in the next rows calculation. It resets to 0 when the AssetID changes. The only alternative I have found is using exp(sum(log)) (shown in code sample) and though numbers are correct in the real world it is too slow to use, where using Quirky Update is near instantaneous. In my sample I have windowing replacing the Daily calculation, but because the running needs to update the current row and have that reflected in the next rows calculation, I couldn't think of a way using windowing and CTE to handle the running calculation. So my attempt is with Variables in the update statement similar to Quirky Update and they are wrong. Maybe there is a way similar to how I am calculating daily? Thanks in advance, Robb

if OBJECT_ID('tempdb..#DMVRunningPerformance') is not null
    drop table #DMVRunningPerformance

create table #DMVRunningPerformance (
    LongPosition_bt bit null,
    AssetID_in int null,
    Symbol_vc varchar(255),
    DayID_in int,
    Status_vc varchar(255) null,
    MV float,
    DailyPerf float null,
    RunningPerf float null)

    CREATE CLUSTERED INDEX [indexdown] ON #DMVRunningPerformance 
        (
            [LongPosition_bt] ASC,
            [AssetID_in] ASC,
            [DayID_in] DESC
        )
    WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

insert into #DMVRunningPerformance  
 values (1,100,'IBM',75006,NULL,201048.8987,NULL,NULL)
insert into #DMVRunningPerformance  
 values (1,100,'IBM',75005,NULL,200841.5658,NULL,NULL)
insert into #DMVRunningPerformance  
 values (1,100,'IBM',75004,NULL,200321.7043,NULL,NULL)
insert into #DMVRunningPerformance  
 values (1,100,'IBM',75003,NULL,201120.0467,NULL,NULL)
insert into #DMVRunningPerformance  
 values (1,100,'IBM',75002,NULL,201779.8805,NULL,NULL)
insert into #DMVRunningPerformance  
 values (1,100,'IBM',75001,NULL,201651.3917,NULL,NULL)
insert into #DMVRunningPerformance  
 values (1,100,'IBM',75000,NULL,201101.0320,NULL,NULL)
insert into #DMVRunningPerformance  
 values (1,200,'MSFT',75006,NULL,805048.8987,NULL,NULL)
insert into #DMVRunningPerformance  
 values (1,200,'MSFT',75005,NULL,801841.5658,NULL,NULL)
insert into #DMVRunningPerformance  
 values (1,200,'MSFT',75004,NULL,804321.7043,NULL,NULL)
insert into #DMVRunningPerformance  
 values (1,200,'MSFT',75003,NULL,809120.0467,NULL,NULL)
insert into #DMVRunningPerformance  
 values (1,200,'MSFT',75002,NULL,801779.8805,NULL,NULL)
insert into #DMVRunningPerformance  
 values (1,200,'MSFT',75001,NULL,801151.3917,NULL,NULL)
insert into #DMVRunningPerformance  
 values (1,200,'MSFT',75000,NULL,801901.0320,NULL,NULL)

-------------------  BEGIN Quirky Update   -------------------------------------    
declare @RunningPerformance as float=0
declare @AssetID_in int=-1,
        @DayID_in int=0,
        @LongPosition_bt bit=0,
        @Status_vc varchar(255)=NULL,
        @NewGroup_bt bit=1,
        @DailyPerformance float,
        @NDMarketValue float,
        @PDMarketValue float=0,
        @NDSymbol_vc varchar(255)='XXXXXXXXXX',
        @PDSymbol_vc varchar(255)='XXXXXXXXXX',
        @NDDayID_in int,
        @PDDayID_in int,
        @NDAssetID_in int=-1,
        @PDAssetID_in int=-1

update #DMVRunningPerformance
    set 
        @LongPosition_bt=LongPosition_bt,
        @NewGroup_bt=CASE WHEN @AssetID_in<>AssetID_in or @LongPosition_bt<>LongPosition_bt THEN 1 ELSE 0 END,
        @DailyPerformance=CASE WHEN @NewGroup_bt=1 THEN 0 ELSE
                            (@NDMarketValue-MV)/MV END,
        @RunningPerformance=CASE WHEN @NewGroup_bt=1 THEN 0 ELSE
                            ((1 + @RunningPerformance) * (1 + @DailyPerformance)) - 1 END
        ,DailyPerf=@DailyPerformance
        ,RunningPerf=@RunningPerformance
        ,@AssetID_in=AssetID_in
        ,@NDAssetID_in=AssetID_in
        ,@NDSymbol_vc=Symbol_vc
        ,@NDDayID_in=DayID_in
        ,@NDMarketValue=MV
FROM #DMVRunningPerformance OPTION (MAXDOP 1)

select 'this data is correct'
select * from #DMVRunningPerformance
-------------------  END Quirky Update   -------------------------------------  

-- reset performance
update #DMVRunningPerformance set DailyPerf=null, RunningPerf=NULL

-------------------  BEGIN windowing and CTE   -------------------------------------    
-- update daily perf with CTE
;WITH dailyCTE as
    (
    select LongPosition_bt,AssetID_in,Symbol_vc,DayID_in,PreviousAssetID,
        MV,
        DailyPerformance = CASE WHEN YesterdayMV IS NULL THEN 0 
                            ELSE (YesterdayMV - MV) / MV END
    FROM
        (
            select p.LongPosition_bt,p.AssetID_in,p.Symbol_vc,p.DayID_in,
                p.MV,
                lag(p.MV,1) over (Partition by LongPosition_bt,AssetID_in
                                    order by DayID_in desc) as YesterdayMV,
                lag(p.AssetID_in,1) over (Partition by LongPosition_bt,AssetID_in
                                    order by DayID_in desc) as PreviousAssetID
                from #DMVRunningPerformance p
        ) p1
    )
    Update #DMVRunningPerformance 
        set DailyPerf=dailyCTE.DailyPerformance,
        @NewGroup_bt=CASE WHEN PreviousAssetID is null THEN 1 ELSE 0 END,
        @RunningPerformance=CASE WHEN PreviousAssetID is null THEN DailyPerformance ELSE
                            ((1 + @RunningPerformance) * (1 + DailyPerformance)) - 1 END,
        RunningPerf=@RunningPerformance
        from dailyCTE join #DMVRunningPerformance d
            on dailyCTE.LongPosition_bt=d.LongPosition_bt and dailyCTE.AssetID_in=d.AssetID_in and dailyCTE.DayID_in=d.DayID_in

select 'this data daily is correct, running is incorrect'

select * from #DMVRunningPerformance

-- alternate method for running perf
-- works but for realworld data takes orders of magnitude longer than quirky update
if 1=2
select 
    dr.AssetID_in,dr.LongPosition_bt,dr.DayID_in,dr.MV,dr.DailyPerf-1 as DailyPerformance,
    RunningPerformance_fl = exp((
                    select
                        sum(log(DailyPerf))
                    from
                        #DMVRunningPerformance
                    where
                        AssetID_in=dr.AssetID_in and LongPosition_bt=dr.LongPosition_bt and
                        DayID_in >= DR.DayID_in)) - 1
 from #DMVRunningPerformance dr
    order by
        dr.assetid_in,DR.DayID_in desc

-------------------  END windowing and CTE   -------------------------------------  



drop table #DMVRunningPerformance

score 0 · Answer 1 · answered Feb 19 '16 at 17:26

0

This is pretty easy using a recursive CTE. The implementation below is a quick and dirty version, so you may be able to remove some of the extra/redundant columns. (DailyPerf2 and RunningPerf2 are the new results.)

;with orderedRows as
(
    select *, row_number() over (partition by AssetId_in order by DayId_in desc) as rn
    from #DMVRunningPerformance
), grouped as
(
    select *, cast(0.0 as float) as DailyPerf2, cast(0.0 as float) as RunningPerf2
    from orderedRows
    where rn = 1

    union all

    select r.*
        , cast((prevR.MV - r.MV) / r.MV as float)
        , cast(((1 + prevR.RunningPerf2) * (1 + (prevR.MV - r.MV) / r.MV)) - 1 as float)
    from orderedRows r
    inner join grouped prevR on r.AssetId_in = prevR.AssetID_in and r.rn = prevR.rn + 1
)
select * 
from grouped
order by AssetID_in, DayID_in desc

answered Feb 19 '16 at 17:26

Becuzz

6,846
26
39

I tried a similar solution with recursive CTE and the problem again is way too slow with realworld data. I would try to create a sample set with real data but would be too much to put in a post. I'll put your code in my real world sample and see how it performs. Thanks. – user789221 Feb 19 '16 at 17:59
@user789221 Well, then you probably aren't going to get any better with a CTE. You may want to check the execution plan to look for any potential improvements. – Becuzz Feb 19 '16 at 18:01
Quick and dirty to type out, but not really quick in terms of performance. Often with large datasets and calculations like this, you'll have better performance using a `CURSOR`. – TT. Feb 19 '16 at 18:41
The original solution was using cursors but again, they are orders of magnitude slower that the quirky update. – user789221 Mar 23 '16 at 12:04

TSQL Replacing "Quirky Update" Calculations with Windowing and CTE

1 Answers1