Início / dba / Perguntas / 29355
Accepted
m__
Asked: 2012-11-28 06:03:41 +0800 CST

SELECIONE vários valores de sensor em uma consulta

  • 772

Fundo

Tenho alguns dispositivos, cada um com alguns sensores. Eu os registro de vez em quando e os armazeno em uma tabela descrita abaixo. Quando alguém solicita uma página da Web, busco alguns desses valores (o último registrado) um por um e os mostro ao usuário. Mas atualmente isso leva muito tempo porque há muitos valores que precisam ser buscados, a busca leva cerca de 8 ms por valor e, no total, falamos de cerca de 300 ms de aumento no tempo total de carregamento da página - para uma página relativamente boa.

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

A tabela é particionada semanalmente na coluna Data.

O que faço agora

Então, o que eu faço é o seguinte. Eu seleciono o maior valor em cada partição que está antes da data/hora atual. e escolha o maior valor.

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;

O que eu quero fazer

Eu quero selecionar todos os valores em uma consulta. Por exemplo, selecione o valor mais recente para DeviceId=1 e SensorId=1,2,3,4,5. Eu criei o seguinte até agora, onde seleciono com a palavra-chave IN para obter valores para vários sensores. No entanto, ainda preciso agrupá-los e resolver aquele com a data mais alta. Estou pensando em adicionar uma cláusula GROUP BY, mas não sei como acertar (as que tentei até agora falharam).

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;
sql-server sql-server-2012

1 respostas

  1. Best Answer

    Primeiras coisas primeiro, noto que sua consulta 'o que eu faço agora':

    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;

    ...produz um plano de execução como este:

    Plano original

    Este plano de execução tem um custo total estimado de 0,02 unidades. Mais de 50% desse custo estimado é a classificação final, executada no modo Top-N. Agora, as estimativas são apenas isso, mas as classificações podem ser caras em geral, então vamos removê-las sem alterar a semântica:

    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

    Agora, o plano de execução não possui operadores de bloqueio e nenhuma classificação em particular. O custo estimado do novo plano de consulta abaixo é de 0,01 unidades e o custo total é distribuído uniformemente pelos métodos de acesso aos dados:

    Plano de consulta aprimorado

    Com a melhoria implementada, tudo o que precisamos para produzir um resultado para cada Sensor ID é fazer uma lista de Sensor IDs e APPLYo código anterior a cada um:

    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;

    O plano de consulta é:

    Plano de consulta final

    O custo estimado do plano de consulta para três IDs de sensor é 0,011 - metade do plano original de sensor único.

    • 7

relate perguntas