GROUP BY com MAX versus apenas MAX

Criei a tabela big_table de acordo com seu esquema

create table big_table
(
    updatetime datetime not null,
    name char(14) not null,
    TheData float,
    primary key(Name,updatetime)
)

Em seguida, preenchi a tabela com 50.000 linhas com este código:

DECLARE @ROWNUM as bigint = 1
WHILE(1=1)
BEGIN
    set @rownum  = @ROWNUM + 1
    insert into big_table values(getdate(),'name' + cast(@rownum as CHAR), cast(@rownum as float))
    if @ROWNUM > 50000
        BREAK;  
END

Usando o SSMS, testei então as duas consultas e percebi que na primeira consulta você está procurando o MAX de TheData e na segunda, o MAX de updatetime

Assim, modifiquei a primeira consulta para obter também o MAX de updatetime

set statistics time on -- execution time
set statistics io on -- io stats (how many pages read, temp tables)

-- query 1
SELECT MAX([UpdateTime])
FROM big_table

-- query 2
SELECT MAX([UpdateTime]) AS value
from
   (
    SELECT [UpdateTime]
    FROM big_table
    group by [UpdateTime]
   ) as t


set statistics time off
set statistics io off

Usando o Statistics Time , recupero o número de milissegundos necessários para analisar, compilar e executar cada instrução

Usando o Statistics IO , recebo informações sobre a atividade do disco

STATISTICS TIME e STATISTICS IO fornecem informações úteis. Tais como as tabelas temporárias usadas (indicadas por worktable). Além disso, quantas páginas lógicas lidas foram lidas, o que indica o número de páginas do banco de dados lidas do cache.

Em seguida, ativo o plano de execução com CTRL+M (ativa a exibição do plano de execução real) e, em seguida, executo com F5.

Isso fornecerá uma comparação de ambas as consultas.

Aqui está a saída da guia Mensagens

-- Consulta 1

Tabela 'grande_tabela'. Contagem de varredura 1, leituras lógicas 543 , leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.

Tempos de execução do SQL Server: tempo de CPU = 16 ms, tempo decorrido = 6 ms .

-- Consulta 2

Tabela ' Mesa de trabalho '. Contagem de varredura 0, leituras lógicas 0, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.

Tabela 'grande_tabela'. Contagem de varredura 1, leituras lógicas 543 , leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.

Tempos de execução do SQL Server: tempo de CPU = 0 ms, tempo decorrido = 35 ms .

Ambas as consultas resultam em 543 leituras lógicas, mas a segunda consulta tem um tempo decorrido de 35ms enquanto a primeira tem apenas 6ms. Você também notará que a segunda consulta resulta no uso de tabelas temporárias em tempdb, indicadas pela palavra worktable . Mesmo que todos os valores para worktable estejam em 0, o trabalho ainda foi feito em tempdb.

Depois, há a saída da guia Plano de execução real ao lado da guia Mensagens

De acordo com o plano de execução fornecido pelo MSSQL, a segunda consulta que você forneceu tem um custo total do lote de 64%, enquanto a primeira custa apenas 36% do lote total, portanto, a primeira consulta requer menos trabalho.

Usando o SSMS, você pode testar e comparar suas consultas e descobrir exatamente como o MSSQL está analisando suas consultas e quais objetos: tabelas, índices e/ou estatísticas, se houver, estão sendo usados ​​para satisfazer essas consultas.

Uma observação adicional a ser lembrada ao testar é limpar o cache antes do teste, se possível. Isso ajuda a garantir que as comparações sejam precisas e isso é importante ao pensar na atividade do disco. Começo com DBCC DROPCLEANBUFFERS e DBCC FREEPROCCACHE para limpar todo o cache. Tenha cuidado, porém, para não usar esses comandos em um servidor de produção realmente em uso , pois você efetivamente forçará o servidor a ler tudo do disco para a memória.

Aqui está a documentação relevante.

1. Limpe o cache do plano com DBCC FREEPROCCACHE
2. Limpe tudo do buffer pool com DBCC DROPCLEANBUFFERS

O uso desses comandos pode não ser possível dependendo de como seu ambiente é usado.

Atualizado em 28/10 12h46

Foram feitas correções na imagem do plano de execução e na saída das estatísticas.

As inserções nesta tabela estão em blocos de 50.000 linhas com a mesma data. Então, pensei que agrupar por poderia facilitar o cálculo do MAX.

A reescrita pode ter ajudado se o SQL Server implementasse a verificação de salto de índice, mas não.

A varredura de salto de índice permite que um mecanismo de banco de dados busque o próximo valor de índice diferente em vez de varrer todas as duplicatas (ou subchaves irrelevantes) no meio. No seu caso, o skip-scan permitiria que o mecanismo encontrasse o MAX(UpdateTime)primeiro Name, pule para MAX(UpdateTime)o segundo Name... e assim por diante. A etapa final seria encontrar o MAX(UpdateTime)dos candidatos um por nome.

Você pode simular isso até certo ponto usando um CTE recursivo, mas é um pouco confuso e não tão eficiente quanto o skip-scan embutido seria:

WITH RecursiveCTE
AS
(
    -- Anchor: MAX UpdateTime for
    -- highest-sorting Name
    SELECT TOP (1)
        BT.Name,
        BT.UpdateTime
    FROM dbo.BigTable AS BT
    ORDER BY
        BT.Name DESC,
        BT.UpdateTime DESC

    UNION ALL

    -- Recursive part
    -- MAX UpdateTime for Name
    -- that sorts immediately lower
    SELECT
        SubQuery.Name,
        SubQuery.UpdateTime
    FROM 
    (
        SELECT
            BT.Name,
            BT.UpdateTime,
            rn = ROW_NUMBER() OVER (
                ORDER BY BT.Name DESC, BT.UpdateTime DESC)
        FROM RecursiveCTE AS R
        JOIN dbo.BigTable AS BT
            ON BT.Name < R.Name
    ) AS SubQuery
    WHERE
        SubQuery.rn = 1
)
-- Final MAX aggregate over
-- MAX(UpdateTime) per Name
SELECT MAX(UpdateTime) 
FROM RecursiveCTE
OPTION (MAXRECURSION 0);

Esse plano executa uma busca singleton para cada distinto Namee, em seguida, encontra o mais alto UpdateTimedos candidatos. Seu desempenho em relação a uma varredura completa simples da tabela depende de quantas duplicatas existem por Name, e se as páginas tocadas pelas buscas de singleton estão na memória ou não.

Soluções alternativas

Se você conseguir criar um novo índice nesta tabela, uma boa opção para esta consulta seria um índice UpdateTimesomente:

CREATE INDEX IX__BigTable_UpdateTime 
ON dbo.BigTable (UpdateTime);

Este índice permitirá que o mecanismo de execução encontre o mais alto UpdateTimecom uma busca singleton até o final da árvore b do índice:

Este plano consome apenas alguns IOs lógicos (para navegar nos níveis da árvore b) e é concluído imediatamente. Observe que a varredura de índice no plano não é uma varredura completa do novo índice - ela simplesmente retorna uma linha do 'final' do índice.

Se você não deseja criar um novo índice completo na tabela, considere uma exibição indexada contendo apenas os UpdateTimevalores exclusivos:

CREATE VIEW dbo.BigTableUpdateTimes
WITH SCHEMABINDING AS
SELECT 
    UpdateTime, 
    NumRows = COUNT_BIG(*)
FROM dbo.BigTable AS BT
GROUP BY
    UpdateTime;
GO
CREATE UNIQUE CLUSTERED INDEX cuq
ON dbo.BigTableUpdateTimes (UpdateTime);

Isso tem a vantagem de criar apenas uma estrutura com tantas linhas quantos forem os UpdateTimevalores exclusivos, embora toda consulta que altere dados na tabela base tenha operadores extras adicionados ao seu plano de execução para manter a exibição indexada. A consulta para encontrar o UpdateTimevalor máximo seria:

SELECT MAX(BTUT.UpdateTime)
FROM dbo.BigTableUpdateTimes AS BTUT
    WITH (NOEXPAND);