I'm storing sensor data in a table SensorValues. The table and primary key is as follows:
CREATE TABLE [dbo].[SensorValues]( [DeviceId] [int] NOT NULL, [SensorId] [int] NOT NULL, [SensorValue] [int] NOT NULL, [Date] [int] NOT NULL, CONSTRAINT [PK_SensorValues] PRIMARY KEY CLUSTERED ( [DeviceId] ASC, [SensorId] ASC, [Date] DESC ) WITH ( FILLFACTOR=75, DATA_COMPRESSION = PAGE, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [MyPartitioningScheme]([Date])
Yet, when I select the sensor value valid for a specific time the execution plan tells me it is doing a sort. Why is that?
I would have thought that since I store the values sorted by the Date column, the sorting would not occure. Or is it because the index isn't solely sorted by the Date column, i.e. it can't assume that the result set is sorted?
SELECT TOP 1 SensorValue FROM SensorValues WHERE SensorId = 53 AND DeviceId = 3819 AND Date < 1339225010 ORDER BY Date DESC
Edit: Can I do this instead?
Since the table is sorted DeviceId, SensorId, Date and I do a SELECT specifying only one DeviceId and one SensorId, the output set should already be sorted by Date DESC. So I wonder if the following question would yield the same result in all cases?
SELECT TOP 1 SensorValue FROM SensorValues WHERE SensorId = 53 AND DeviceId = 3819 AND Date < 1339225010
According to @Catcall below, the sort order is not the same as the storage order. I.e. we can't assume that the returned values are already in a sorted order.
Edit: I've tried this CROSS APPLY solution, no luck
@Martin Smith suggested I'd try to OUTER APPLY my result against the partitions. I found a blog post (Aligned non-clustered indexes on partitioned table) describing this similar problem and tried the somewhat similar solution to what Smith suggested. However, no luck here, the execution time is on par with my original solution.
WITH Boundaries(boundary_id) AS ( SELECT boundary_id FROM sys.partition_functions pf JOIN sys.partition_range_values prf ON pf.function_id = prf.function_id WHERE pf.name = 'PF' AND prf.value <= 1339225010 UNION ALL SELECT max(boundary_id) + 1 FROM sys.partition_functions pf JOIN sys.partition_range_values prf ON pf.function_id = prf.function_id WHERE pf.name = 'PF' AND prf.value <= 1339225010 ), Top1(SensorValue) AS ( SELECT TOP 1 d.SensorValue FROM Boundaries b CROSS APPLY ( SELECT TOP 1 SensorValue FROM SensorValues WHERE SensorId = 53 AND DeviceId = 3819 AND "Date" < 1339225010 AND $Partition.PF(Date) = b.boundary_id ORDER BY Date DESC ) d ORDER BY d.Date DESC ) SELECT SensorValue FROM Top1