Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2005
    Posts
    319

    Is there an easy way to calculate a range bar from (stock) tick prices?

    Assume you have a table called Tick with 2 columns
    (
    tickId bigint IDENTITY(1,1)
    , price int -- usually money data type, making it int for simplicity
    )

    I am tasked with creating bars that are 10 units long.

    Now the catch is I'm not looking for the tickId where price is >= t1(price) + 10 where t1(price) is the price for the first row where tickId = 1. (it could also be where price <= t1(price) - 10)

    Here is sample data:

    1, 25
    2, 26
    3, 23
    4, 26
    5, 27
    6, 30
    7, 34
    8, 32
    9, 30
    10, 33

    What I am looking for are rows 3 (23) and 7 (34)

    Currently I have:

    Code:
    DECLARE @tickDiff int
    SET @tickDiff = 10
    
    DECLARE @r1TickId bigint
    DECLARE @r3TickId bigint
    DECLARE @r4TickId bigint
    
    SET @r1TickId = 1
    
    SELECT @r3TickId = MIN(r1.tickId)
    FROM dbo.Tick r1
    JOIN dbo.Tick r2
    ON r1.tickId < r2.tickId
    AND r1.price >= r2.price + @tickDiff
    WHERE r1.tickId >= @r1TickId
    
    SELECT @r4tickId = MIN(r4.tickId)
    FROM dbo.Tick r3
    JOIN dbo.Tick r4
    ON r3.tickId < r4.tickId
    AND r3.price >= r4.price + @tickDiff
    WHERE r3.tickId = @r3TickId
    This seems to work but it is taking multiple minutes to run for about 50k rows of data (which I created off of the 24 million row table I have just looking at data from today). So it takes ~5 minutes to create the first bar which is not acceptible.

    If my logic above seems acceptable are there any indexes you could recommend. Database engine tuning advisor didn't find any.

  2. #2
    Join Date
    Jun 2005
    Posts
    319
    I feel like the set based operation is killing the performance since it has to evaluate every combination of tickId pairs and I would be better off creating a while loop or cursor to perform as such:

    1) using the price for tickId 1, find the tickId with a price >= price + 10 and the tickId with a price <= price - 10.
    2) take the lesser tickId from (1)
    -- at this point I will have a subset of data and I need to further evaluate it to see if there is a better paring (i.e. with an end range < the end range found in (1))
    3) evaluate further using query in first post from this subset of data

    So that I would be decreasing my set from 50k down to 100 records or so, make sense?

  3. #3
    Join Date
    Jun 2005
    Posts
    319
    Well, looks like I answered my own question, here is the answer:

    Code:
    DECLARE
    	  @secId int
    	, @r1TickId bigint
    	, @tickDiff money
    	
    SELECT @r1TickId = 1
    		
    SELECT @tickDiff = 10
    
    DECLARE @r2TickId bigint
    DECLARE @r3TickId bigint
    DECLARE @r4TickId bigint
    
    SELECT
    	@r2tickId = MIN(r2.tickId)
    FROM
    		dbo.Tick r1 (nolock)
    JOIN	dbo.Tick r2 (nolock)
    	ON	r1.tickId < r2.tickId
    	AND	ABS(r1.price - r2.price) >= @tickDiff
    WHERE
    	r1.tickId = @r1TickId
    
    SELECT 
    	@r3TickId = MIN(r1.tickId)
    FROM
    		dbo.Tick r1 (nolock)
    JOIN	dbo.Tick r2 (nolock)
    	ON	r1.tickId < r2.tickId
    	AND	ABS(r1.price - r2.price) >= @tickDiff
    WHERE
    	r1.tickId between @r1tickId and @r2tickId
    AND	r1.secId = @secId
    
    SELECT
    	@r4tickId = MIN(r4.tickId)
    FROM
    		dbo.Tick r3 (nolock)
    JOIN	dbo.Tick r4 (nolock)
    	ON	r3.tickId < r4.tickId
    	AND	ABS(r3.price - r4.price) >= @tickDiff
    WHERE
    	r3.tickId = @r3TickId
    	
    SELECT * FROM dbo.Tick (nolock) WHERE 	
    tickId in (@r1tickId, @r3tickId, @r4tickId)
    ORDER BY tickId

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •