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.
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.
EXEC sp_spaceused [SensorData]
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]
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.