Início / dba / Perguntas / 19159
Accepted
m__
Asked: 2012-06-13 00:06:35 +0800 CST

Ordem de classificação especificada na chave primária, mas a classificação é executada em SELECT

  • 772

Estou armazenando os dados do sensor em uma tabela SensorValues ​​. A tabela e a chave primária são as seguintes:

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

No entanto, quando seleciono o valor do sensor válido para um tempo específico, o plano de execução me diz que está fazendo uma classificação. Por que é que?

Eu teria pensado que, como armazeno os valores classificados pela coluna Data, a classificação não ocorreria. Ou é porque o índice não é classificado apenas pela coluna Date, ou seja, não pode assumir que o conjunto de resultados está classificado?

SELECT TOP 1 SensorValue
  FROM SensorValues
  WHERE SensorId = 53
    AND DeviceId = 3819
    AND Date < 1339225010
  ORDER BY Date DESC

Plano de execução

Editar: posso fazer isso em vez disso?

Como a tabela está ordenada DeviceId, SensorId, Date e eu faço um SELECT especificando apenas um DeviceId e um SensorId , o conjunto de saída já deve estar ordenado por Date DESC . Então, eu me pergunto se a seguinte pergunta produziria o mesmo resultado em todos os casos?

SELECT TOP 1 SensorValue
  FROM SensorValues
  WHERE SensorId = 53
    AND DeviceId = 3819
    AND Date < 1339225010

De acordo com @Catcall abaixo, a ordem de classificação não é igual à ordem de armazenamento. Ou seja, não podemos assumir que os valores retornados já estão em uma ordem de classificação.

Editar: tentei esta solução CROSS APPLY, sem sorte

@Martin Smith sugeriu que eu tentasse OUTER APPLY meu resultado nas partições. Encontrei uma postagem no blog ( Índices não agrupados alinhados na tabela particionada ) descrevendo esse problema semelhante e tentei a solução um tanto semelhante à sugerida por Smith. No entanto, sem sorte aqui, o tempo de execução está no mesmo nível da minha solução original.

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
sql-server primary-key

4 respostas

  1. Best Answer

    Para uma tabela não particionada, obtenho o seguinte plano

    Plano 1

    Existe um único predicado de busca em Seek Keys[1]: Prefix: DeviceId, SensorId = (3819, 53), Start: Date < 1339225010.

    O que significa que o SQL Server pode executar uma busca de igualdade nas duas primeiras colunas e, em seguida, iniciar uma busca de intervalo começando em 1339225010e ordenada FORWARD(conforme o índice é definido com [Date] DESC)

    O TOPoperador parará de solicitar mais linhas da busca depois que a primeira linha for emitida.

    Quando crio o esquema de partição e a função

    CREATE PARTITION FUNCTION PF (int)
AS RANGE LEFT FOR VALUES (1000, 1339225009 ,1339225010 , 1339225011);
GO
CREATE PARTITION SCHEME [MyPartitioningScheme]
AS PARTITION PF
ALL TO ([PRIMARY] );

    E preencha a tabela com os seguintes dados

    INSERT INTO [dbo].[SensorValues]    
/*500 rows matching date and SensorId, DeviceId predicate*/
SELECT TOP (500) 3819,53,1, ROW_NUMBER() OVER (ORDER BY (SELECT 0))           
FROM master..spt_values
UNION ALL
/*700 rows matching date but not SensorId, DeviceId predicate*/
SELECT TOP (700) 3819,52,1, ROW_NUMBER() OVER (ORDER BY (SELECT 0))           
FROM master..spt_values
UNION ALL 
/*1100 rows matching SensorId, DeviceId predicate but not date */
SELECT TOP (1100) 3819,53,1, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) + 1339225011      
FROM master..spt_values

    O plano no SQL Server 2008 é o seguinte.

    Plano 2

    O número real de linhas emitidas pela busca é 500. O plano mostra buscar predicados

    Seek Keys[1]: Start: PtnId1000 <= 2, End: PtnId1000 >= 1, 
Seek Keys[2]: Prefix: DeviceId, SensorId = (3819, 53), Start: Date < 1339225010

    Indicando que está usando a abordagem de pular varredura descrita aqui

    o otimizador de consulta é estendido para que uma operação de busca ou varredura com uma condição possa ser feita em PartitionID (como a coluna inicial lógica) e possivelmente em outras colunas de chave de índice e, em seguida, uma busca de segundo nível, com uma condição diferente, pode ser feita em uma ou mais colunas adicionais, para cada valor distinto que atenda à qualificação para a operação de busca de primeiro nível.

    Este plano é um plano serial e, portanto, para a consulta específica que você tem, parece que, se o SQL Server garantisse o processamento das partições em ordem decrescente, dateo plano original com o TOPainda funcionaria e poderia parar o processamento após a primeira linha correspondente ser encontrados em vez de continuar e produzir as 499 correspondências restantes.

    Na verdade, o plano para 2005 parece ter essa abordagem

    Planeje 2005

    Não tenho certeza se é direto obter o mesmo plano em 2008 ou talvez seja necessário um OUTER APPLYpara sys.partition_range_valuessimulá-lo.

    • 13

  2. Muitas pessoas acreditam que um índice clusterizado garante uma ordem de classificação na saída. Mas não é isso que ele faz; garante uma ordem de armazenamento em disco.

    Veja, por exemplo, esta postagem no blog , uma continuação e esta discussão mais longa .

    • 9

  3. Estou especulando que o SORT é necessário por causa do plano paralelo. Baseio isso em algum artigo de blog obscuro e distante: mas encontrei isso no MSDN, o que pode ou não justificar isso

    Então, tente com MAXDOP 1 e veja o que acontece...

    Também sugerido na postagem do blog do @sql kiwi no Simple Talk em "Exchange Operator", eu acho. E "dependência DOP" aqui

    • 5

  4. Basicamente, você está certo - como a chave primária está na ordem "DeviceId, SensorId, Date", os dados na chave não são classificados por data, portanto, não podem ser usados. Se sua chave estivesse em uma ordem diferente "Data, DeviceId, SensorId", os dados na chave seriam classificados por data, portanto, poderiam ser usados...

    • 1

relate perguntas