Início / user-108757

Metro Smurf's questions

Martin Hope
Metro Smurf
Asked: 2017-05-15 08:22:54 +0800 CST
  • 15

Ao usar uma subconsulta para encontrar a contagem total de todos os registros anteriores com um campo correspondente, o desempenho é terrível em uma tabela com apenas 50 mil registros. Sem a subconsulta, a consulta é executada em alguns milissegundos. Com a subconsulta, o tempo de execução é superior a um minuto.

Para esta consulta, o resultado deve:

  • Inclua apenas os registros dentro de um determinado intervalo de datas.
  • Inclua uma contagem de todos os registros anteriores, sem incluir o registro atual, independentemente do intervalo de datas.

Esquema de Tabela Básico

Activity
======================
Id int Identifier
Address varchar(25)
ActionDate datetime2
Process varchar(50)
-- 7 other columns

Dados de exemplo

Id  Address     ActionDate (Time part excluded for simplicity)
===========================
99  000         2017-05-30
98  111         2017-05-30
97  000         2017-05-29
96  000         2017-05-28
95  111         2017-05-19
94  222         2017-05-30

resultados esperados

Para o intervalo de datas de 2017-05-29a2017-05-30

Id  Address     ActionDate    PriorCount
=========================================
99  000         2017-05-30    2  (3 total, 2 prior to ActionDate)
98  111         2017-05-30    1  (2 total, 1 prior to ActionDate)
94  222         2017-05-30    0  (1 total, 0 prior to ActionDate)
97  000         2017-05-29    1  (3 total, 1 prior to ActionDate)

Os registros 96 e 95 são excluídos do resultado, mas são incluídos na PriorCountsubconsulta

Consulta atual

select 
    *.a
    , ( select count(*) 
        from Activity
        where 
            Activity.Address = a.Address
            and Activity.ActionDate < a.ActionDate
    ) as PriorCount
from Activity a
where a.ActionDate between '2017-05-29' and '2017-05-30'
order by a.ActionDate desc

Índice atual

CREATE NONCLUSTERED INDEX [IDX_my_nme] ON [dbo].[Activity]
(
    [ActionDate] ASC
)
INCLUDE ([Address]) WITH (
    PAD_INDEX = OFF, 
    STATISTICS_NORECOMPUTE = OFF, 
    SORT_IN_TEMPDB = OFF, 
    DROP_EXISTING = OFF, 
    ONLINE = OFF, 
    ALLOW_ROW_LOCKS = ON, 
    ALLOW_PAGE_LOCKS = ON
)

Pergunta

  • Quais estratégias podem ser usadas para melhorar o desempenho dessa consulta?

Edit 1
Em resposta à pergunta do que posso modificar no banco de dados: posso modificar os índices, mas não a estrutura da tabela.

Edit 2
Agora adicionei um índice básico na Addresscoluna, mas isso não pareceu melhorar muito. Atualmente, estou encontrando um desempenho muito melhor ao criar uma tabela temporária e inserir os valores sem o PriorCounte atualizar cada linha com suas contagens específicas.

Editar 3
O Spool de Índice Joe Obbish (resposta aceita) encontrado foi o problema. Depois de adicionar um novo nonclustered index [xyz] on [Activity] (Address) include (ActionDate), os tempos de consulta caíram de mais de um minuto para menos de um segundo sem usar uma tabela temporária (consulte a edição 2).

performance subquery