9

I have the following time-series Table in SQL:

CREATE TABLE [dbo].[SensorData](
 [DateTimeUtc] [datetime2](2) NOT NULL,
 [SensorId] [int] NOT NULL,
 [Key] [varchar](20) NOT NULL,
 [Value] [decimal](19, 4) NULL,
CONSTRAINT [PK_SensorData] PRIMARY KEY CLUSTERED 
(
  [SensorId] ASC,
  [Key] ASC,
  [DateTimeUtc] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = 
ON, Data_Compression=PAGE) ON PS_Daily(DateTimeUtc))

Now based on this index, every single query needs to have the following parameters in the query where filter:

[SensorId],
[Key],
[DateTimeUtc]

When the query has all three the query returns really fast as expected.

Now I am stuck with a specific query, that does not have any specific value for [Key]. For example:

Check if there is ANY data for sensor: 1234 in the past 12 hours. In this case, we have a filter value for DateTime and SensorId. But this query returns really slowly.

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

I know the easy answer is to add a new index on the table with only [SensorId],[DateTimeUtc]; however, this will add a substantial amount of space to the db based on its size and will also slow down inserts.

Is there any way I can get the above query to hit the clustered index?

The reason I used the clustered index key order I did, was after reading up on how you should order it, items of which values will be the most unique should be first.

I ran EXEC sp_spaceused [SensorData]

SP_SpaceUsed

Query:

SELECT 
CASE WHEN ( EXISTS (SELECT 
    1 AS [C1]
    FROM [dbo].[SensorData] AS [Extent1]
    WHERE ([Extent1].[DateTimeUtc] > @p__linq__0) AND ([Extent1].[DateTimeUtc] <= @p__linq__1) AND ([Extent1].[SensorId] = @p__linq__2)
)) THEN cast(1 as bit) ELSE cast(0 as bit) END AS [C1]
FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]

Sql Plan

There are generally about 10 to 40 distinct keys per SensorId per time period. But we obviously store those 10 to 40 keys thousands of times a day per SensorId.

2
  • You say the advice is "items of which values will be the most unique should be first", but surely that's not true, SensorID will be a highly repeated value in your table, right?
    – Stobor
    Feb 17 at 0:28
  • Your question could be improved by providing DDL for the partitioning scheme, a link to the xml form of the actual (post-execution) execution plan (not a picture) perhaps via brentozar.com/pastetheplan. Also quantify "returns really slowly", and give your SQL Server version and edition. Do you have freedom to write your own query or must you use LINQ?
    – Paul White
    Feb 17 at 12:54

3 Answers 3

5

Have you thought about using ADX (Azure Data Explorer) to store this table? Such queries are a breeze for ADX. ADX is a time series optimized cluster on Azure. For me, it seems to be the natural solution for this type of problem.

It will manage the partitions, indexes and you can create a retention policy automatically.

If you are planning to consume the data with Power BI (or Grafana), you could also do Direct Query on ADX, so no need to import a huge amount of data when processing your data model. Also, the connector for ADX supports very well query folding, so if you are using Power BI (for example) the queries for your visuals will be translated to Kusto on the fly.

If your sensor data comes from Event Hubs or IoT Hub, you can ingest the data directly to ADX (if it is a supported file type), or even use Stream Analytics to create a custom deserializer to ingest diverse data.

Please, let me know if you need more context.

0
4

Your current clustered index definition is (SensorId, Key, DateTimeUtc) which will cover queries with predicates on all three of those fields. It also should cover any predicates using any sequential subset of that definition (reading from left to right), e.g. a predicate using SensorId and Key or even a predicate just using SensorId. This is because the order that you list the fields in an index definition is the order that the B-Tree of that index is sorted on.

In your case the B-Tree is currently sorted by SensorId then by Key and finally by DateTimeUtc. A more helpful index design to support the additional use case you mentioned would be to re-arrange the columns in the definition so it's (DateTimeUtc, SensorId, Key) instead (as mentioned in the comments). That single clustered index will cover your use case where you're using all three fields to filter on, and it supports the use case where you just want to know if there's any data for a given date range for a given sensor. It even also covers you if you wanted to know if there was any data at all for any given date range.

As discussed in the comments, if you're planning to do range filtering on DateTimeUtc, you may find better performance still leading with the SensorId that you're filtering on with an equality match. But you could still arrange the columns in the clustered index definition as (SensorId, DateTimeUtc, Key) that way it covers both your aforementioned use cases.

So just by re-arranging the columns in your clustered index definition, you're able to accomplish your goal without introducing any new indexes and without taking up any additional disk space.

Side note: you can use the system stored procedure sp_spaceused to almost immediately find out how big (by rows and consumed disk size) a given table is. For example EXEC sp_spaceused 'dbo.SensorData';.

3
  • 1
    @Zapnologica Note that this new index order does not help you as much if your query filters on a range of DateTimeUTC and an exact SensorId, it only helps if DateTimeUTC is exact. In other words, selectivity is not as relevant if you are looking at range predicates, because once you are searching by range you cannot filter the next column. Feb 16 at 21:20
  • That is exactly what I was thinking last night, I ONLY query by "RANGE" cause we will never know the exact millisecond which the time was at. How does that range search affect the index? I actually think this is why we put the index in the order we did, because sensorId will always be explit, key will be explicit if it is present else it wont be there, and time will always be a range scan. Feb 17 at 5:21
  • 1
    @Zapnologica Yes there is some truth to what Charlieface said. I updated my answer accordingly to account for that. While I feel my first suggestion will still be faster than what you originally had because it makes the index covering, my additional suggestion should address the point Charlieface makes & still cover you for both your use cases. You may find the most performance gain with that definition. Unfortunately the only way to really know is to test both ways. My recommendation would be for you to create a copy of the table in a Development environment so you can test without downtime.
    – J.D.
    Feb 17 at 12:07
4

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.

Solution

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.

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.