Skip to main content
Refactor, add explanation
Source Link
Paul White
  • 90.3k
  • 30
  • 423
  • 663

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.

Assuming your partitioning function is named PF_Daily, give the following a trial:

DECLARE 
    @SensorId integer = 1234 -- Use your target value here,
    @CurrentPartition integer = $PARTITION.PF_Daily(SYSUTCDATETIME()),
    @FirstPartition integer = $PARTITION.PF_Daily(DATEADD(HOUR, -12, SYSUTCDATETIME())),
    @StartDate datetime2(2) = DATEADD(HOUR, -12, SYSUTCDATETIME());

WITH 
    SkipScan1 AS
    (
        SELECT TOP (1) SD.[Key]
        FROM dbo.SensorData AS SD
        WHERE SD.SensorId = @SensorId
        AND $PARTITION.PF_Daily(SD.DateTimeUtc) = @CurrentPartition
        ORDER BY SD.[Key] ASC

        UNION ALL 
 
        SELECT NextKey.[Key]
        FROM 
         (
            SELECT 
                SD.[Key], 
                rn = ROW_NUMBER() 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
        ) AS NextKey
        WHERE NextKey.rn = 1
    ),
    SkipScan2 AS
    (
        SELECT TOP (1) SD.[Key]
        FROM dbo.SensorData AS SD
        WHERE SD.SensorId = @SensorId
        AND $PARTITION.PF_Daily(SD.DateTimeUtc) = @FirstPartition
        ORDER BY SD.[Key] ASC

        UNION ALL 

        SELECT NextKey.[Key]
        FROM 
        (
            SELECT 
                 SD.[Key], 
                rn = ROW_NUMBER() OVER (
                    ORDER BY SD.[Key]) 
            FROM SkipScan2 AS SS
            JOIN dbo.SensorData AS SD
                ON SD.[Key] > SS.[Key]
            WHERE SD.SensorId = @SensorId
            AND $PARTITION.PF_Daily(SD.DateTimeUtc) = @FirstPartition
        ) AS NextKey
        WHERE NextKey.rn = 1
    )
SELECT
    C1 =
        CASE 
            WHEN EXISTS
            (
                SELECT TOP (1) CA.*
                FROM SkipScan1 AS SS
                CROSS APPLY 
                (
                    SELECT TOP (1) SD.* 
                    FROM dbo.SensorData AS SD
                    WHERE 
                        $PARTITION.PF_Daily(SD.DateTimeUtc) = @CurrentPartition
                        AND SD.SensorId = @SensorId
                        AND SD.[Key] = SS.[Key]
                        AND SD.DateTimeUtc >= @StartDate
                ) AS CA
            )
            OR EXISTS
            (
                SELECT TOP (1) CA.*
                FROM SkipScan2 AS SS
                CROSS APPLY 
                (
                    SELECT TOP (1) SD.* 
                    FROM dbo.SensorData AS SD
                    WHERE 
                        $PARTITION.PF_Daily(SD.DateTimeUtc) = @FirstPartition
                        AND SD.SensorId = @SensorId
                        AND SD.[Key] = SS.[Key]
                        AND SD.DateTimeUtc >= @StartDate
                ) AS CA
                WHERE @FirstPartition < @CurrentPartition
            )
            THEN CONVERT(bit, 1)
            ELSE CONVERT(bit, 0)
        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 partitions.

Solution

We 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 and wrapped the skip scan and index search in the following inline table-valued function:

CREATE OR ALTER FUNCTION dbo.GetSensorDataUsingSkipScan
(
    @SensorId integer,
    @ForDate datetime2(2)
)
RETURNS table
AS
RETURN
    WITH 
        SkipScan AS
        (
            -- Anchor:
            -- Lowest Key value for the given SensorId and date partition
            SELECT TOP (1) SD.[Key]
            FROM dbo.SensorData AS SD
            WHERE SD.SensorId = @SensorId
            AND $PARTITION.PF_Daily(SD.DateTimeUtc) = $PARTITION.PF_Daily(@ForDate)
            ORDER BY SD.[Key] ASC

            UNION ALL  

            -- Recursive:
            -- Next highest Key value for the given SensorId and date partition
            SELECT NextKey.[Key]
            FROM  
            (
                SELECT  
                    SD.[Key], 
                    rn = ROW_NUMBER() OVER (
                        ORDER BY SD.[Key]) 
                FROM SkipScan AS SS
                JOIN dbo.SensorData AS SD
                    ON SD.[Key] > SS.[Key]
                WHERE SD.SensorId = @SensorId
                AND $PARTITION.PF_Daily(SD.DateTimeUtc) = $PARTITION.PF_Daily(@ForDate)
            ) AS NextKey
            WHERE NextKey.rn = 1
        )
    SELECT SD.*
    FROM SkipScan 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) = $PARTITION.PF_Daily(@ForDate)
            AND SD.SensorId = @SensorId
            AND SD.[Key] = SS.[Key]
            AND SD.DateTimeUtc >= @ForDate
    ) AS SD;

The exists test can then be rewritten to use the function as:

-- Search parameters
DECLARE  
    @SensorId integer = 1234,
    @StartDate datetime2(2) = DATEADD(HOUR, -12, SYSUTCDATETIME()),
    @EndDate datetime2(2) = SYSUTCDATETIME();

SELECT
    CASE
        WHEN EXISTS 
        (
            SELECT * FROM dbo.GetSensorDataUsingSkipScan(@SensorId, @StartDate)
        )
        OR EXISTS 
        (
            SELECT * FROM dbo.GetSensorDataUsingSkipScan(@SensorId, @EndDate)
        )
        THEN CONVERT(bit, 'true')
        ELSE CONVERT(bit, '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.

Source Link
Paul White
  • 90.3k
  • 30
  • 423
  • 663

Does adding Where [Key] is not null make SQL hit that index?

No, because "is not null" is an inequality.

Rearranging the keys of the clustered index is unlikely to bring you the benefits you are after because equality tests must come before any inequalities for a b-tree index. (SensorId, DateTimeUtc, Key) would speed up your exists query, but slow down queries that also provide an equality test for the Key column. All rows that match the seek on SensorId and the DateTimeUtc range would need a residual predicate for the Key test.

Nevertheless, it is possible to speed up your exists query using only the existing clustered index, given there are relatively few unique Key values:

There are generally about 10 to 40 distinct keys per SensorId per time period.

The technique is to simulate an index "skip scan" as I describe in my article, Finding Distinct Values Quickly. There is a slight complication due to the partitioning scheme, which adds the partition id as an implied leading key to the index, but the general idea is the same.

Assuming your partitioning function is named PF_Daily, give the following a trial:

DECLARE 
    @SensorId integer = 1234 -- Use your target value here,
    @CurrentPartition integer = $PARTITION.PF_Daily(SYSUTCDATETIME()),
    @FirstPartition integer = $PARTITION.PF_Daily(DATEADD(HOUR, -12, SYSUTCDATETIME())),
    @StartDate datetime2(2) = DATEADD(HOUR, -12, SYSUTCDATETIME());

WITH 
    SkipScan1 AS
    (
        SELECT TOP (1) SD.[Key]
        FROM dbo.SensorData AS SD
        WHERE SD.SensorId = @SensorId
        AND $PARTITION.PF_Daily(SD.DateTimeUtc) = @CurrentPartition
        ORDER BY SD.[Key] ASC

        UNION ALL 

        SELECT NextKey.[Key]
        FROM 
        (
            SELECT 
                SD.[Key], 
                rn = ROW_NUMBER() 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
        ) AS NextKey
        WHERE NextKey.rn = 1
    ),
    SkipScan2 AS
    (
        SELECT TOP (1) SD.[Key]
        FROM dbo.SensorData AS SD
        WHERE SD.SensorId = @SensorId
        AND $PARTITION.PF_Daily(SD.DateTimeUtc) = @FirstPartition
        ORDER BY SD.[Key] ASC

        UNION ALL 

        SELECT NextKey.[Key]
        FROM 
        (
            SELECT 
                SD.[Key], 
                rn = ROW_NUMBER() OVER (
                    ORDER BY SD.[Key]) 
            FROM SkipScan2 AS SS
            JOIN dbo.SensorData AS SD
                ON SD.[Key] > SS.[Key]
            WHERE SD.SensorId = @SensorId
            AND $PARTITION.PF_Daily(SD.DateTimeUtc) = @FirstPartition
        ) AS NextKey
        WHERE NextKey.rn = 1
    )
SELECT
    C1 =
        CASE 
            WHEN EXISTS
            (
                SELECT TOP (1) CA.*
                FROM SkipScan1 AS SS
                CROSS APPLY 
                (
                    SELECT TOP (1) SD.* 
                    FROM dbo.SensorData AS SD
                    WHERE 
                        $PARTITION.PF_Daily(SD.DateTimeUtc) = @CurrentPartition
                        AND SD.SensorId = @SensorId
                        AND SD.[Key] = SS.[Key]
                        AND SD.DateTimeUtc >= @StartDate
                ) AS CA
            )
            OR EXISTS
            (
                SELECT TOP (1) CA.*
                FROM SkipScan2 AS SS
                CROSS APPLY 
                (
                    SELECT TOP (1) SD.* 
                    FROM dbo.SensorData AS SD
                    WHERE 
                        $PARTITION.PF_Daily(SD.DateTimeUtc) = @FirstPartition
                        AND SD.SensorId = @SensorId
                        AND SD.[Key] = SS.[Key]
                        AND SD.DateTimeUtc >= @StartDate
                ) AS CA
                WHERE @FirstPartition < @CurrentPartition
            )
            THEN CONVERT(bit, 1)
            ELSE CONVERT(bit, 0)
        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 partitions.