Início / dba / Perguntas / 176296
Accepted
Kevin3NF
Asked: 2017-06-15 09:48:05 +0800 CST

Otimização T-SQL

  • 772

Se a resposta for 'não', tudo bem...

Estou olhando para ver se isso pode ser otimizado... é parte de um proc armazenado muito maior. CGCodeé varchar(50), Yeare Monthsão smallint, FEINéchar(9)

select max(id)
from Table
where 1=1
    and cgcode = 123
    and datefromparts(cast(year as char(4)),cast(month as char(2)),'01') < getdate()
    and totalcount > 0 
group by cgcode, year, month, fein

Leituras lógicas do plano de execução real: 1.566.473

Dados brutos da tabela de origem mais de 32 milhões de registros

Linhas estimadas: 640K, 55K reais, antes do Group By entrar em ação

Avisos de conversão implícitos ativados Year/Month/CGCode(entra como bigint)

Tempo de execução de aproximadamente 7,5 segundos, fazendo uma busca de índice não clusterizado:

insira a descrição da imagem aqui

O conjunto de resultados finais é de 114 linhas (para isso CGCode, testamos com... outros variam)

O desempenho é praticamente o mesmo no Prod em hardware significativamente melhor do que na caixa Dev. Isso só vai piorar com o tempo, pois está puxando tudo mais antigo que o mês atual, para preencher um gráfico de histórico em uma interface do usuário.

Que outras informações posso fornecer?

Cole o plano

O índice que está sendo usado atualmente:

CREATE NONCLUSTERED INDEX [COIX_Table_TotalCount] ON [dbo].[Table]
(
    [TotalCount] ASC
)
INCLUDE (   [ID],
    [CGCode],
    [Year],
    [Month],
    [FEIN]) 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, 
        ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CGCodelimita os dados mais... 281 valores. yearé apenas 3-4 anos de dados, monthapenas 12 opções, é claro. TotalCounté um bigint, 17K valores distintos em 32 milhões de registros. Não faço ideia de qual é o propósito da TotalCountcoluna.

sql-server sql-server-2014

3 respostas

  1. Best Answer

    Em primeiro lugar, eu reescreveria essa condição. Como está, não pode usar nenhum índice de forma eficaz.:

    datefromparts(cast(year as char(4)),cast(month as char(2)),'01') < getdate()

    Reescreva-o para que não haja funções ou cálculos aplicados às colunas:

     ( year < year(getdate()) 
or year = year(getdate()) and month <= month(getdate())
 )

    A reescrita permitiria a utilização de um índice on (cgcode, year, month)- ou melhor ainda (cgcode, year, month, fein), considerando as GROUP BYpartes.

    Em seguida, se totalcountsempre fizer parte da WHEREcláusula ou se a totalcount > 0condição específica for sempre usada, você poderá obter ainda mais eficiência adicionando-a à INCLUDEparte ou com um índice filtrado e mais direcionado:

    -- option A
index (cgcode, year, month, fein, id) 
      include (totalcount)

-- option B
index (cgcode, year, month, fein, id)
      where (totalcount > 0)
    • 8

  2. Vou remover o cgcode do Group By, pois sei o que é cgcode e não é range.

    Então, se eu estiver correto, meu índice também mudará.

    Use Variable no lugar de getdate()

    declare @CurDate date=getdate()
declare @yr int=year(@CurDate ) 
declare @month tinyint=month(@CurDate )
declare @cg varchar(60)='123'

select @cg cgcode,max(id)
from Table
where  cgcode = @cg
    ( year < @yr  
or [year] = @yr and [month] <= @month
 )
    and totalcount > 0 
group by year, month, fein

    Também tecnicamente porque totalcount will be covering indexna opção AI não entendo, por essa lógica deve estar dentro do grupo de índices ou option Bestá correto.

    • 0

  3. Encontrei :)

    O clientid está sendo passado como INT... causando uma conversão implícita para varchar para o campo CGCode.

    select max(id)
from Table
where 1=1
    and cgcode = CAST(123 as varchar(50))  ---this fixed it
    and datefromparts(cast(year as char(4)),cast(month as char(2)),'01') < getdate()
    and totalcount > 0 
group by cgcode, year, month, fein

    De 7-30 segundos a 100ms. Eu vou levar isso.

    • 0

relate perguntas