Query never completes when calling the AVG function in a subquery and cursor block while using a window function

Question

I'm querying trading candlesticks in my database. Firstly I'm fetching rows that have been specifically filtered, once I've got these rows I want to find the 19 previous candles for each candle and apply an AVG function.

The only issue is that AVG function in the cursor block is taking an indefinite time to complete (it never completed even after hours).

What could it be?

DECLARE @candles_table TABLE
(
     [CandleStickBitmexSqlId] BIGINT INDEX IX0 NONCLUSTERED
    ,[TimestampSnapShotUtcCandleStick] DATETIME INDEX IX1 NONCLUSTERED
    ,[TimestampSnapShotUnixEpochCandleStick] FLOAT INDEX IX2 NONCLUSTERED
    ,[Symbol] NVARCHAR(450)
    ,[Open] FLOAT NOT NULL
    ,[High] FLOAT NOT NULL
    ,[Low] FLOAT NOT NULL
    ,[Close] FLOAT NOT NULL INDEX IX3 NONCLUSTERED
    ,[Trades] BIGINT NOT NULL
    ,[Volume] BIGINT 
    ,[Vwap] FLOAT 
    ,[LastSize] FLOAT 
    ,[Turnover] BIGINT
    ,[HomeNotional] FLOAT 
    ,[ForeignNotional] FLOAT 
    ,[IsReplacingMissingCandle] BIT
    ,[IsCandleCompleted] BIT
    ,[IsCandleGeneratedByLiveTrade] BIT
    ,[CandleStickTimeFrame] NVARCHAR(450)
    ,[CounterBatch] INT
    ,[UpperBollinger] FLOAT
    ,[LowerBollinger] FLOAT
    ,[BB_UPPER_PRECENT] FLOAT
    ,[BB_LOWER_PRECENT] FLOAT
    ,[BB_MA] FLOAT
    ,[CountPointsCalculated] INT
)

DECLARE @candles_table_resp TABLE
(
     [CandleStickBitmexSqlId] BIGINT INDEX IX0 NONCLUSTERED
    ,[TimestampSnapShotUtcCandleStick] DATETIME INDEX IX1 NONCLUSTERED
    ,[TimestampSnapShotUnixEpochCandleStick] FLOAT INDEX IX2 NONCLUSTERED
    ,[Symbol] NVARCHAR(450)
    ,[Open] FLOAT NOT NULL
    ,[High] FLOAT NOT NULL
    ,[Low] FLOAT NOT NULL
    ,[Close] FLOAT NOT NULL INDEX IX3 NONCLUSTERED
    ,[Trades] BIGINT NOT NULL
    ,[Volume] BIGINT 
    ,[Vwap] FLOAT 
    ,[LastSize] FLOAT 
    ,[Turnover] BIGINT
    ,[HomeNotional] FLOAT 
    ,[ForeignNotional] FLOAT 
    ,[IsReplacingMissingCandle] BIT
    ,[IsCandleCompleted] BIT
    ,[IsCandleGeneratedByLiveTrade] BIT
    ,[CandleStickTimeFrame] NVARCHAR(450)
    ,[CounterBatch] INT
    ,[UpperBollinger] FLOAT
    ,[LowerBollinger] FLOAT
    ,[BB_UPPER_PRECENT] FLOAT
    ,[BB_LOWER_PRECENT] FLOAT
    ,[BB_MA] FLOAT
    ,[CountPointsCalculated] INT
)


INSERT INTO @candles_table
SELECT
  *
FROM
(
    SELECT 
     MyT2.[CandleStickBitmexSqlId] 
    ,MyT2.[TimestampSnapShotUtcCandleStick] 
    ,MyT2.[TimestampSnapShotUnixEpochCandleStick] 
    ,MyT2.[Symbol] 
    ,MyT2.[Open] 
    ,MyT2.[High] 
    ,MyT2.[Low] 
    ,MyT2.[Close] 
    ,MyT2.[Trades]
    ,MyT2.[Volume]
    ,MyT2.[Vwap] 
    ,MyT2.[LastSize] 
    ,MyT2.[Turnover] 
    ,MyT2.[HomeNotional] 
    ,MyT2.[ForeignNotional] 
    ,MyT2.[IsReplacingMissingCandle] 
    ,MyT2.[IsCandleCompleted] 
    ,MyT2.[IsCandleGeneratedByLiveTrade] 
    ,MyT2.[CandleStickTimeFrame] 
    ,ROW_NUMBER() OVER(ORDER BY MyT2.[CandleStickTimeFrame] DESC) AS [CounterBatch] 
    ,MyT2.[UpperBollinger] 
    ,MyT2.[LowerBollinger]
    ,CAST(((([High]*100)/MyT2.[UpperBollinger] )-100) AS decimal(10,4)) AS BB_UPPER_PRECENT
    ,CAST(((([Low]*100*-1)/MyT2.[LowerBollinger])+100) AS decimal(10,4)) AS BB_LOWER_PRECENT 
    ,MyT2.[BB_MA]
    ,MyT2.[CountPointsCalculated]

    FROM (
        SELECT
             [CandleStickBitmexSqlId] 
            ,[TimestampSnapShotUtcCandleStick] 
            ,[TimestampSnapShotUnixEpochCandleStick] 
            ,[Symbol] 
            ,[Open] 
            ,[High] 
            ,[Low] 
            ,[Close] 
            ,[Trades]
            ,[Volume]
            ,[Vwap] 
            ,[LastSize] 
            ,[Turnover] 
            ,[HomeNotional] 
            ,[ForeignNotional] 
            ,[IsReplacingMissingCandle] 
            ,[IsCandleCompleted] 
            ,[IsCandleGeneratedByLiveTrade] 
            ,[CandleStickTimeFrame] 

            ,NULL AS [CounterBatch]

            ,AVG([Close]) OVER (ORDER BY [CandleStickTimeFrame] DESC ROWS BETWEEN 0 FOLLOWING AND 19 FOLLOWING) + (STDEV([Close]) OVER (ORDER BY [CandleStickTimeFrame] DESC ROWS BETWEEN 0 FOLLOWING AND 19 FOLLOWING) * 2) AS UpperBollinger
            ,AVG([Close]) OVER (ORDER BY [CandleStickTimeFrame] DESC ROWS BETWEEN 0 FOLLOWING AND 19 FOLLOWING) - (STDEV([Close]) OVER (ORDER BY [CandleStickTimeFrame] DESC ROWS BETWEEN 0 FOLLOWING AND 19 FOLLOWING) * 2) AS LowerBollinger 
            ,NULL AS [BB_UPPER_PRECENT] 
            ,NULL AS [BB_LOWER_PRECENT] 
            ,AVG([Close]) OVER (ORDER BY [CandleStickTimeFrame] DESC ROWS BETWEEN 0 FOLLOWING AND 19 FOLLOWING) AS [BB_MA] 
            ,COUNT([Close]) OVER(ORDER BY [CandleStickTimeFrame] DESC ROWS BETWEEN 0 FOLLOWING AND 19 FOLLOWING) AS [CountPointsCalculated]


        FROM [bitmex].[CandleStickBitmexSql]
        WHERE CandleStickTimeFrame like 'min5' 
        AND  
        '2021-03-16 19:00:00.000' <= TimestampSnapShotUtcCandleStick 
        AND 
        TimestampSnapShotUtcCandleStick <= '2021-04-01 17:15:00.000'

    ) AS MyT2
    WHERE 
    [CountPointsCalculated] >= 20 
    AND 
    ( 
    [LOW] < [LowerBollinger] 
    )       
) AS MyT3
ORDER BY MyT3.[TimestampSnapShotUtcCandleStick] DESC




SELECT * FROM @candles_table_resp
SELECT * FROM @candles_table


DECLARE @c_CandleStickBitmexSqlId BIGINT 
DECLARE @c_TimestampSnapShotUtcCandleStick DATETIME 
DECLARE @c_TimestampSnapShotUnixEpochCandleStick FLOAT 
DECLARE @c_Symbol NVARCHAR(450)
DECLARE @c_Open FLOAT 
DECLARE @c_High FLOAT 
DECLARE @c_Low FLOAT 
DECLARE @c_Close FLOAT 
DECLARE @c_Trades BIGINT 
DECLARE @c_Volume BIGINT 
DECLARE @c_Vwap FLOAT 
DECLARE @c_LastSize FLOAT 
DECLARE @c_Turnover BIGINT
DECLARE @c_HomeNotional FLOAT 
DECLARE @c_ForeignNotional FLOAT 
DECLARE @c_IsReplacingMissingCandle BIT
DECLARE @c_IsCandleCompleted BIT
DECLARE @c_IsCandleGeneratedByLiveTrade BIT
DECLARE @c_CandleStickTimeFrame NVARCHAR(450)
DECLARE @c_CounterBatch INT
DECLARE @c_UpperBollinger FLOAT
DECLARE @c_LowerBollinger FLOAT
DECLARE @c_BB_UPPER_PRECENT FLOAT
DECLARE @c_BB_LOWER_PRECENT FLOAT
DECLARE @c_BB_MA FLOAT
DECLARE @c_CountPointsCalculated INT

DECLARE db_cursor CURSOR FOR 
SELECT
     [CandleStickBitmexSqlId] 
    ,[TimestampSnapShotUtcCandleStick] 
    ,[TimestampSnapShotUnixEpochCandleStick] 
    ,[Symbol] 
    ,[Close] 
    ,[CandleStickTimeFrame] 
    ,[CounterBatch] 
FROM @candles_table
OPEN db_cursor  
FETCH NEXT FROM db_cursor 
INTO 
 @c_CandleStickBitmexSqlId
,@c_TimestampSnapShotUtcCandleStick
,@c_TimestampSnapShotUnixEpochCandleStick
,@c_Symbol
,@c_Close
,@c_CandleStickTimeFrame   
,@c_CounterBatch
WHILE @@FETCH_STATUS = 0  
BEGIN 
    

      --INSERT INTO @candles_table_resp
      SELECT TOP (19) 
        *
        ,@c_CounterBatch AS [CounterBatch]
        --********** THE FOLLOWING AVG FUNCTIONS ARE BLOCKING/SLOWING THE QUERY ***************** 
        ,AVG(csbitmex.[Close]) OVER (ORDER BY csbitmex.[CandleStickTimeFrame] DESC ROWS BETWEEN 0 FOLLOWING AND 19 FOLLOWING) + (STDEV(csbitmex.[Close]) OVER (ORDER BY csbitmex.[CandleStickTimeFrame] DESC ROWS BETWEEN 0 FOLLOWING AND 19 FOLLOWING) * 2) AS [UpperBollinger]
        ,AVG([Close]) OVER (ORDER BY [CandleStickTimeFrame] DESC ROWS BETWEEN 0 FOLLOWING AND 19 FOLLOWING) - (STDEV([Close]) OVER (ORDER BY [CandleStickTimeFrame] DESC ROWS BETWEEN 0 FOLLOWING AND 19 FOLLOWING) * 2) AS [LowerBollinger] 
        --,NULL AS [UpperBollinger] 
        --,NULL AS [LowerBollinger] 
        ,NULL AS [BB_UPPER_PRECENT] 
        ,NULL AS [BB_LOWER_PRECENT] 
        ,NULL AS [BB_MA] 
        ,NULL AS [CountPointsCalculated] 
      FROM [bitmex].[CandleStickBitmexSql] AS csbitmex
      WHERE 
      TimestampSnapShotUtcCandleStick < @c_TimestampSnapShotUtcCandleStick
      AND 
      [Symbol] LIKE 'XBTUSD' 
      AND 
      [CandleStickTimeFrame] LIKE 'min5' 
      ORDER BY TimestampSnapShotUtcCandleStick DESC 



    FETCH NEXT FROM db_cursor 
    INTO 
     @c_CandleStickBitmexSqlId
    ,@c_TimestampSnapShotUtcCandleStick
    ,@c_TimestampSnapShotUnixEpochCandleStick
    ,@c_Symbol
    ,@c_Close
    ,@c_CandleStickTimeFrame  
    ,@c_CounterBatch
END 
CLOSE db_cursor  
DEALLOCATE db_cursor





SELECT
     [CandleStickBitmexSqlId]
    ,[TimestampSnapShotUtcCandleStick] 
    ,[TimestampSnapShotUnixEpochCandleStick] 
    ,[Symbol] 
    ,[Open] 
    ,[High] 
    ,[Low] 
    ,[Close] 
    ,[Trades] 
    ,[Volume] 
    ,[Vwap] 
    ,[LastSize] 
    ,[Turnover] 
    ,[HomeNotional] 
    ,[ForeignNotional] 
    ,[IsReplacingMissingCandle] 
    ,[IsCandleCompleted] 
    ,[IsCandleGeneratedByLiveTrade] 
    ,[CandleStickTimeFrame] 
    ,[CounterBatch] 
    ,[UpperBollinger] 
    ,[LowerBollinger] 
    ,[BB_UPPER_PRECENT] 
    ,[BB_LOWER_PRECENT] 
    ,[BB_MA] 
    ,[CountPointsCalculated] 
FROM @candles_table_resp AS tcr
ORDER BY 
tcr.CounterBatch,
tcr.TimestampSnapShotUtcCandleStick DESC

Begs the question: why are you using a cursor, can this not be done set-based? I also don't get why you have `19 following` in a query that starts `select top (3)` — Charlieface, Mar 17 '21 at 01:17
@Charlieface updated top 19, but this is not the issue blocking the query — Carlo Luther, Mar 17 '21 at 01:28
I didn't say it was. The cursor almost certainly is though. This whole procedure could probably be done in a single `select` — Charlieface, Mar 17 '21 at 01:30
@Charlieface Yes, I know cursors are slow and that there are in general other better options. Honestly I've been thinking about it in the last 3 days but nothing popped in my mind. — Carlo Luther, Mar 17 '21 at 01:32
Just join the cursor query with the `select top` query, either use a rownumbering solution to get the top 19 per group, or use `cross apply`. [There are hundreds of examples of this online](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Charlieface, Mar 17 '21 at 01:37
@Charlieface besides the commands you mentioned please point out some other topics that I might read. Thank you — Carlo Luther, Mar 17 '21 at 01:39

Query never completes when calling the AVG function in a subquery and cursor block while using a window function

0 Answers0