在一次查詢中選擇多個感測器值

背景

我有幾個設備，每個設備都有幾個感測器。我時不時地記錄這些並將它們儲存在下面描述的表中。當有人請求一個網頁時，我會一一獲取其中的幾個值（最新記錄的）並將它們顯示給使用者。但是目前這需要很長時間，因為需要獲取的值太多，每個值的獲取大約需要 8 毫秒，而且我們談論的總頁面載入時間總共增加了大約 300 毫秒 - 對於一個相對較好的頁面。

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
);

該表每週在 Date 列上進行分區。

我現在應該做什麼

所以，我要做的是以下。我選擇目前日期/時間之前的每個分區中的最大值。並選出最大的值。

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;

我想做的事

我想在一個查詢中選擇所有值。例如，選擇 DeviceId=1 和 SensorId=1,2,3,4,5 的最新值。到目前為止，我已經提出了以下內容，其中我使用 IN 關鍵字進行選擇以獲取多個感測器的值。但是，我仍然需要將它們分組並整理出日期最高的那個。我正在考慮添加一個 GROUP BY 子句，但不知道如何正確處理（我嘗試過的那些到目前為止都失敗了）。

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;

首先，我注意到您的“我現在做什麼”查詢：

SELECT TOP (1)
   ca.SensorValue,
   ca.Date
FROM sys.partitions AS p
CROSS APPLY
(
   SELECT TOP (1)
       v.Date, 
       v.SensorValue
   FROM SensorValues 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;

…產生這樣的執行計劃：

該執行計劃的估計總成本為0.02個單位。超過 50% 的估計成本是最終排序，以 Top-N 模式執行。現在估計只是這樣，但一般來說排序可能很昂貴，所以讓我們在不改變語義的情況下刪除它：

SELECT TOP (1)
   ca.SensorId,
   ca.SensorValue,
   ca.Date
FROM
(
   -- Partition numbers
   SELECT DISTINCT
       partition_number = prv.boundary_id
   FROM
       sys.partition_functions AS pf
   JOIN sys.partition_range_values AS prv ON
       prv.function_id = pf.function_id
   WHERE
       pf.name = N'SensorValues_Date_PF'
       AND prv.boundary_id <= $PARTITION.SensorValues_Date_PF(@fDate)
) AS p
CROSS APPLY
   (
   SELECT TOP (1)
       v.Date,
       v.SensorValue,
       v.SensorId
   FROM dbo.SensorValues 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
ORDER BY
   p.partition_number DESC,
   ca.Date DESC

現在執行計劃沒有阻塞操作符，也沒有特別的排序。下面新查詢計劃的估計成本為0.01個單位，總成本平均分佈在數據訪問方法上：

隨著改進到位，我們為每個感測器 ID 生成結果所需的只是製作一個感測器 ID 列表以及每個感測器 IDAPPLY的前面程式碼：

SELECT
   PerSensor.SensorId,
   PerSensor.SensorValue,
   PerSensor.Date
FROM 
(
   -- Sensor ID list
   VALUES 
       (@fSensorId1),
       (@FSensorId2),
       (@FSensorId3)
) AS Sensor (Id)
CROSS APPLY
(
   -- Optimized code applied to each sensor
   SELECT TOP (1)
       ca.SensorId,
       ca.SensorValue,
       ca.Date
   FROM
   (
       -- Partition numbers
       SELECT DISTINCT
           partition_number = prv.boundary_id
       FROM
           sys.partition_functions AS pf
       JOIN sys.partition_range_values AS prv ON
           prv.function_id = pf.function_id
       WHERE
           pf.name = N'SensorValues_Date_PF'
           AND prv.boundary_id <= $PARTITION.SensorValues_Date_PF(@fDate)
   ) AS p
   CROSS APPLY
       (
       SELECT TOP (1)
           v.Date,
           v.SensorValue,
           v.SensorId
       FROM dbo.SensorValues AS v
       WHERE
           $PARTITION.SensorValues_Date_PF(v.Date) = p.partition_number
           AND v.DeviceId = @fDeviceId
           AND v.SensorId = Sensor.Id--@fSensorId1
           AND v.Date <= @fDate
       ORDER BY
           v.Date DESC
     ) AS ca
   ORDER BY
       p.partition_number DESC,
       ca.Date DESC
) AS PerSensor;

查詢計劃為：

三個感測器 ID 的估計查詢計劃成本為0.011 - 原始單感測器計劃的一半。

引用自：https://dba.stackexchange.com/questions/29355