Início / dba / Perguntas / 125710
Accepted
Steffen Mangold
Asked: 2016-01-09 16:48:06 +0800 CST

Gargalo de agregação GROUP BY

  • 772

Eu tenho um gargalo de desempenho com uma SELECT GROUP BYoperação.

Esquema

Eu tenho uma tabela assim:

CREATE TABLE [InverterData](
    [InverterID] [bigint] NOT NULL,
    [TimeStamp] [datetime] NOT NULL,    
    [ValueA] [decimal](18, 2) NULL,
    [ValueB] [decimal](18, 2) NULL
    CONSTRAINT [PrimaryKey_e149e28f-5754-4229-be01-65fafeebce16] PRIMARY KEY CLUSTERED 
    (
        [TimeStamp] DESC,
        [InverterID] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF
    , IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON)
)

e um Indexassim:

CREATE NONCLUSTERED INDEX [TimeStamp_Power-NonClusteredIndex] ON [dbo].[InverterData]
(
    [InverterID] ASC,
    [TimeStamp] ASC
)
INCLUDE 
(   
    [ValueA],
    [ValueB]
)

A [InverterData]tabela possui as seguintes estatísticas de armazenamento:

  • Espaço para dados: 26.901,86 MB
  • Contagem de linhas: 131.827.749
  • Particionado: verdadeiro
  • Contagem de partições: 62

Uso

Com meu esquema descrito (um mais algumas tabelas extras que não são importantes para minha pergunta), posso executar consultas super rápidas como esta:

SELECT [TimeStamp], [ValueA], [ValueB]

FROM [InverterData]

JOIN [Inverter] ON [Inverter].[ID] = [InverterData].[InverterID]
JOIN [DataLogger] ON [DataLogger].[ID] = [Inverter].[DataLoggerID]

WHERE [DataLogger].[ProjectID] = 20686
 AND [InverterData].[TimeStamp] >= '20160108'   
 AND [InverterData].[TimeStamp] < '20160109'

Tempo de execução : 178ms

Plano de execução:

insira a descrição da imagem aqui

Problema

Agora quero fazer um SELECT GROUP BYintervalo de [InverterID] e 15 minutos de [TimeStamp].

Alguns pensam assim:

SELECT [InverterID]
 , DATEADD(MINUTE, DATEDIFF(MINUTE, 0, [TimeStamp] ) / 15 * 15, 0) AS [TimeStamp]
 , SUM([ValueA]), SUM([ValueB])

FROM [InverterData]

JOIN [Inverter] ON [Inverter].[ID] = [InverterData].[InverterID]
JOIN [DataLogger] ON [DataLogger].[ID] = [Inverter].[DataLoggerID]

WHERE [DataLogger].[ProjectID] = 20686
 AND [InverterData].[TimeStamp] >= '20160107'
 AND [InverterData].[TimeStamp] < '20160108'

GROUP BY
 [InverterID], DATEADD(MINUTE, DATEDIFF(MINUTE, 0, [InverterData].[TimeStamp] ) / 15 * 15, 0)

Tempo de execução : 4637ms

Plano de execução:

insira a descrição da imagem aqui

tentativas

Acho que pode estar relacionado à Sortoperação necessária aqui:

insira a descrição da imagem aqui

Tanto quanto sei, é possível evitar isso SORTcriando um indexador correspondente. Mas não sei como fazer isso com meu agrupamento de intervalos de 15 minutos.

Pergunta

Como você pode ver, o tempo de execução do SELECT GROUP BYé muito mais longo. Mas não sei onde e como evitar o gargalo!?

Atualização 1 (relacionada à resposta de @Max Vernon)

Se for possível gostaria de ter uma solução mais rápida onde eu possa alterar o intervalo de forma flexível (por exemplo 10, 15 ou 6o minutos). Portanto, sem colunas calculadas.

sql-server sql-server-2008

1 respostas

  1. Best Answer

    Você pode adicionar uma coluna calculada à tabela e criar um índice a partir do cálculo.

    Por exemplo, a tabela seria:

    CREATE TABLE dbo.InverterData
(
     InverterID bigint NOT NULL
    , TS datetime NOT NULL    
    , ValueA decimal(18, 2) NULL
    , ValueB decimal(18, 2) NULL
    , TS15 AS (DATEADD(MINUTE, DATEDIFF(MINUTE, 0, TS ) / 15 * 15, 0))
    , CONSTRAINT PK_InverterData PRIMARY KEY CLUSTERED 
    (
        TS DESC,
        InverterID ASC
    ) WITH (
        PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF
        , IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON
        , ALLOW_PAGE_LOCKS = ON
        )
);

    Modifiquei o nome da TimeStampcoluna para, TSpois TimeStampé uma palavra reservada.

    Depois de olhar o comentário de Paul; Acho que um bom índice pode ser:

    CREATE INDEX IX_InverterData_ID_TS151 
ON dbo.InverterData (TS15, InverterID);

    Se você alterar a WHEREcláusula para operar na coluna indexada de carimbo de data/hora, não obterei nenhuma classificação no plano. Acho que a alteração na wherecláusula provavelmente não é um problema, pois você parece estar selecionando dias inteiros.

    SELECT InverterData.InverterID
    , InverterData.TS15
FROM InverterData
WHERE InverterData.TS15 >= '20140107'
    AND InverterData.TS15 < '20160108'
GROUP BY InverterData.InverterID
    , InverterData.TS15
OPTION (RECOMPILE);

    É verdade que não incluí as outras tabelas em meu exemplo, pois você não forneceu esses detalhes.

    O plano para a consulta acima é, com 100.000 linhas em minha tabela de exemplo:

    insira a descrição da imagem aqui

    Tendo dito tudo isso, sem as definições reais da tabela, incluindo o esquema de particionamento, etc., é difícil dizer o quão bem isso realmente funcionará para você.

    Como a adição de uma coluna computada (não persistente) é uma operação apenas de metadados, a modificação da tabela deve ser quase instantânea. Você provavelmente ainda deseja fazer isso quando sabe que não há outras transações (ou o mínimo possível) ocorrendo, de modo que o bloqueio de esquema necessário (embora de curta duração) não seja bloqueado. A DDL para modificar a tabela é:

    ALTER TABLE dbo.InverterData
ADD TS15 AS (DATEADD(MINUTE, DATEDIFF(MINUTE, 0, TS ) / 15 * 15, 0));
    • 7

relate perguntas