Background
I have a couple of devices, each with a couple of sensors. I log these every now and then and stores them in a table described below. When someone requests a web page, I fetch a couple of these values (the latest logged) one by one and displays them to the user. But currently this takes too long time because there are too many values that needs to be fetched, the fetch takes about 8ms per value and in total we talk about around 300ms increase in total page load time - for a relatively good page.
CREATE TABLE [dbo].[SensorValues](
[DeviceId] [int] NOT NULL,
[SensorId] [int] NOT NULL,
[SensorValue] [int] NOT NULL,
[Date] [int] NOT NULL, --- stored as unixtime
CONSTRAINT [PK_SensorValues] PRIMARY KEY CLUSTERED
(
[DeviceId] ASC,
[SensorId] ASC,
[Date] DESC
);
The table is partitioned weekly on the Date column.
What I do now
So, what I do is the following. I select the largest value in each partition that is before the current date/time. and pick out the largest value.
SELECT TOP (1) ca.SensorValue, ca.Date
FROM sys.partitions AS p
CROSS APPLY
(
SELECT TOP (1) v.Date, v.SensorValue
FROM SensorValue AS v
WHERE $PARTITION.SensorValues_Date_PF(v.Date) = p.[partition_number]
AND v.DeviceId = @fDeviceId
AND v.SensorId = @fSensorId
AND v.Date <= @fDate
ORDER BY v.Date DESC
) AS ca
WHERE p.[partition_number] <= $PARTITION.SensorValues_Date_PF(@fDate)
AND p.[object_id] = OBJECT_ID(N'dbo.SensorValues', N'U')
AND p.index_id = 1
ORDER BY p.[partition_number] DESC, ca.Date DESC;
What I want to do
I want to select all values in one query. E.g. select the latest value for DeviceId=1 and SensorId=1,2,3,4,5. I have come up with the following so far, where I select with the IN keyword to get values for multiple sensors. However, I still need to group them and sort out the one with the highest date. I'm thinking of adding a GROUP BY clause, but don't know how to get it right (the ones I've tried has failed so far).
SELECT ca.SensorValue, ca.Date
FROM sys.partitions AS p
CROSS APPLY
(
SELECT TOP (1) v.Date, v.SensorValue
FROM SensorValue AS v
WHERE $PARTITION.SensorValues_Date_PF(v.Date) = p.[partition_number]
AND v.DeviceId = @fDeviceId
AND v.SensorId IN (@fSensorId1, @fSensorId2, @fSensorId3)
AND v.Date <= @fDate
ORDER BY v.Date DESC
) AS ca
WHERE p.[partition_number] <= $PARTITION.SensorValues_Date_PF(@fDate)
AND p.[object_id] = OBJECT_ID(N'dbo.SensorValues', N'U')
AND p.index_id = 1
ORDER BY p.[partition_number] DESC, ca.Date DESC;
JOIN
on that.