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.
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?