Início / dba / Perguntas / 104667
Accepted
yossico
Asked: 2015-06-22 00:21:47 +0800 CST

Consulta de intervalos de dados enormes

  • 772

Minha pergunta é mais ou menos teórica - qual é a melhor abordagem para obter dados de consulta por intervalos.

A situação

Eu tenho um sistema que gerencia contadores (milhares de contadores - conta consumo de eletricidade/água etc...), a cada 15 minutos o contador é amostrado e armazenado no banco de dados. existe uma tabela que armazena todos os dados dos contadores [id, counterID, Timestamp, CounterValue].

Agora, quero consultar os dados sobre o consumo do intervalo (horário/diário/semanal/mensal).

O consumo intervalado definido como a primeira medição (contagem) no início do próximo intervalo menos a primeira medição no início do intervalo atual. como todas as medições são agregadas (contadores) o delta entre dois contadores representa o consumo no intervalo.

Como há MUITOS dados e quero que as consultas sejam rápidas, preciso de uma maneira melhor do que apenas consultar a tabela inteira ... (eu acho ...) consultar todos os dados (mesmo com pequena quantidade de dados - essa consulta é lento...):

SELECT *, dateadd(hour,datediff(hour,0, dateOfData),0) as roundedTime
FROM (  SELECT *, ROW_NUMBER() OVER(PARTITION BY  counterId,datediff(hour, 0, dateOfData) ORDER BY dateOfData asc) row_number
        FROM counters_data) A
WHERE row_number = 1
Order by dateOfData;

Pensei em algumas maneiras de fazer isso:

  1. Adicione colunas booleanas [isHourly,isDaily,isWeekly...] na tabela de medições e quando eu armazenar os dados, atualizarei esses sinalizadores.
  2. Adicione tabelas para cada intervalo com colunas [id, date, counterId, counterDataRecord] para que haja tblDailyCounter, tblWeeklyCounter etc...

há alguma outra ideia que estou perdendo?

quais são os prós e contras dessas abordagens?

qual é a prática mais recomendada/melhor?

sql-server performance

1 respostas

  1. Best Answer

    Bem, esta resposta depende de como seu banco de dados foi projetado e licenciado.

    Se você possui uma licença Enterprise, deve dar uma olhada no particionamento. Dessa forma, você pode particionar seus dados, o que o ajudará a consultar essas tabelas. Se você fizer um bom particionamento, poderá ler apenas uma pequena parte da tabela do disco, em vez de consultar tudo.

    Não sei como seu aplicativo grava em seu banco de dados. Mas talvez seja possível sinalizar o primeiro registro de uma hora para que você não precise filtrá-lo mais tarde dessa maneira?

    Outra ideia é um SQL Agent, que executará essas agregações em segundo plano para você. Talvez isso possa ser por hora e gravar todos os valores agregados em outra tabela cumulativa, que pode ser consultada por você ainda mais rapidamente.

    • 2

relate perguntas