Solution
AssumingWe can use the existing index to find unique Key values by starting with the lowest Key, then finding the next value recursively. This is very efficient because it uses the first two columns of the existing index, an equality test on SensorId and an inequality on Key.
The list of distinct Key values then allows us to use the existing index efficiently, since we have SensorId, Key, and DateTimeUtc values.
I have assumed your partitioning function is named PF_Daily, give and wrapped the skip scan and index search in the following a trialinline table-valued function:
DECLARE
@SensorId integer = 1234 -- Use your target value here,
CREATE @CurrentPartitionOR integerALTER =FUNCTION $PARTITIONdbo.PF_Daily(SYSUTCDATETIMEGetSensorDataUsingSkipScan
()),
@FirstPartition@SensorId integer = $PARTITION.PF_Daily(DATEADD(HOUR, -12, SYSUTCDATETIME())),
@StartDate@ForDate datetime2(2) = DATEADD(HOUR, -12, SYSUTCDATETIME());
)
WITHRETURNS table
SkipScan1 AS
(RETURN
SELECT TOP (1)WITH SD.[Key]
FROM dbo.SensorDataSkipScan AS SD
WHERE SD.SensorId = @SensorId
AND $PARTITION.PF_Daily(SD.DateTimeUtc) = @CurrentPartition
ORDER BY SD.[Key] ASC
UNION ALL--
Anchor:
SELECT NextKey.[Key]
-- FROMLowest
Key value for the given SensorId and date (partition
SELECT
SD.[Key],
rn =TOP ROW_NUMBER(1) OVER (
ORDER BY SD.[Key])
FROM SkipScan1 AS SS
JOIN dbo.SensorData AS SD
ON SD.[Key] > SS.[Key]
WHERE SD.SensorId = @SensorId
AND $PARTITION.PF_Daily(SD.DateTimeUtc) = @CurrentPartition$PARTITION.PF_Daily(@ForDate)
) AS NextKey
ORDER WHEREBY NextKeySD.rn =[Key] 1ASC
),
SkipScan2 AS
(
UNION ALL
SELECT TOP (1) SD.[Key]
-- Recursive:
FROM dbo.SensorData AS SD
WHERE-- SD.SensorIdNext =highest @SensorId
Key value for the given SensorId and date ANDpartition
$PARTITION.PF_Daily(SD.DateTimeUtc) = @FirstPartition
ORDER BYSELECT SDNextKey.[Key] ASC
UNION ALL
FROM
SELECT NextKey.[Key]
FROM (
(
SELECT
SELECT
SD.[Key],
rn = ROW_NUMBER() OVER (
ORDER BY SD.[Key])
FROM SkipScan2SkipScan AS SS
JOIN dbo.SensorData AS SD
ON SD.[Key] > SS.[Key]
WHERE SD.SensorId = @SensorId
AND $PARTITION.PF_Daily(SD.DateTimeUtc) = @FirstPartition
$PARTITION.PF_Daily(@ForDate) AS NextKey
WHERE NextKey.rn = 1
)
SELECT
C1AS =NextKey
CASE
WHERE NextKey.rn WHEN= EXISTS1
()
SELECT TOP (1) CASD.*
FROM SkipScan1SkipScan AS SS
CROSS APPLY
(
(
-- Find the sensor data using the SensorId, Key, and Date
SELECT TOP (1) SD.*
FROM dbo.SensorData AS SD
WHERE
$PARTITION.PF_Daily(SD.DateTimeUtc) = @CurrentPartition$PARTITION.PF_Daily(@ForDate)
AND SD.SensorId = @SensorId
AND SD.[Key] = SS.[Key]
AND SD.DateTimeUtc >= @StartDate@ForDate
) AS CASD;
The exists test can then be rewritten to use the function as:
-- Search )parameters
DECLARE
@SensorId integer OR= EXISTS1234,
@StartDate datetime2(2) = DATEADD(HOUR, -12, SYSUTCDATETIME()),
@EndDate SELECTdatetime2(2) TOP= SYSUTCDATETIME(1) CA.*;
FROM SkipScan2 AS SS
SELECT
CASE
CROSSWHEN APPLYEXISTS
(
SELECT TOP (1) SD.*
FROM dbo.SensorData AS SD
WHEREGetSensorDataUsingSkipScan(@SensorId, @StartDate)
$PARTITION.PF_Daily(SD.DateTimeUtc) = @FirstPartition
AND SD.SensorIdOR =EXISTS @SensorId
AND SD.[Key] = SS.[Key](
SELECT * ANDFROM SDdbo.DateTimeUtc >= @StartDate
GetSensorDataUsingSkipScan(@SensorId, @EndDate) AS CA
WHERE @FirstPartition < @CurrentPartition
)
THEN CONVERT(bit, 1'true')
ELSE CONVERT(bit, 0'false')
END
OPTION (MAXRECURSION 0);
The code can be simplified to a single skip scan if you are happy to check only the most recent partition. I assumed your 12-hour range could span daily partitions.