É possível obter plano paralelo baseado em busca para distinto/grupo por?

Eu tenho exatamente a mesma configuração e passei pelos mesmos estágios de reescrever a consulta.

No meu caso, os nomes e significados das tabelas são um pouco diferentes, mas a estrutura geral é a mesma. Sua tabela Transactionscorresponde à minha tabela PortalElevatorsabaixo. Tem aproximadamente 2.000 linhas. Sua mesa TxLogcorresponde à minha mesa PlaybackStats. Tem aproximadamente 150 milhões de linhas. Tem índice em (ElevatorID, DataSourceRowID), igual a você.

Executarei várias variantes da consulta nos dados reais e compararei planos de execução, E/S e estatísticas de tempo. Estou usando o SQL Server 2008 Standard.

GROUP BY com MAX

SELECT [ElevatorID], MAX([DataSourceRowID]) AS LastItemID
FROM [dbo].[PlaybackStats]
GROUP BY [ElevatorID]

O mesmo que para o seu otimizador verifica o índice e agrega os resultados. Lento.

linha individual

Vamos ver o que o otimizador faria se eu solicitasse MAXapenas uma linha:

SELECT MAX([dbo].[PlaybackStats].[DataSourceRowID]) AS LastItemID
FROM [dbo].[PlaybackStats]
WHERE [dbo].[PlaybackStats].ElevatorID = 1

O Optimizer é inteligente o suficiente para usar o índice e faz uma busca. A propósito, podemos ver que o otimizador usa o TOPoperador, mesmo que a consulta não o tenha. Este é um sinal claro de que os caminhos de otimização de MAXe TOPtêm algo em comum no motor, mas são diferentes como veremos a seguir.

CROSS APPLY com MAX

SELECT
    [dbo].[PortalElevators].elevatorsId
    ,LastItemID
FROM
    [dbo].[PortalElevators]
    CROSS APPLY
    (
        SELECT MAX([dbo].[PlaybackStats].[DataSourceRowID]) AS LastItemID
        FROM [dbo].[PlaybackStats]
        WHERE [dbo].[PlaybackStats].ElevatorID = [dbo].[PortalElevators].elevatorsId
    ) AS CA
;

O Optimizer ainda verifica o índice. Não é inteligente o suficiente converter MAXe TOPdigitalizar em buscas aqui. Lento. Não pensei nessa variante originalmente, minha próxima tentativa foi UDF escalar.

UDF escalar

Eu vi que o plano para obter MAXuma linha individual tinha busca de índice, então coloquei essa consulta simples em uma UDF escalar.

CREATE FUNCTION [dbo].[GetElevatorLastID]
(
    @ParamElevatorID int
)
RETURNS bigint
AS
BEGIN
    DECLARE @Result bigint;
    SELECT @Result = MAX([dbo].[PlaybackStats].[DataSourceRowID])
    FROM [dbo].[PlaybackStats]
    WHERE [dbo].[PlaybackStats].ElevatorID = @ParamElevatorID;
    RETURN @Result;
END

SELECT
    [dbo].[PortalElevators].elevatorsId
    ,[dbo].[GetElevatorLastID]([dbo].[PortalElevators].elevatorsId) AS LastItemID
FROM
    [dbo].[PortalElevators]
;

Ele corre rápido. Pelo menos, muito mais rápido do que Group by. Infelizmente, o plano de execução não mostra os detalhes do UDF e, o que é ainda pior, não mostra as estatísticas reais do IO (não inclui o IO gerado pelo UDF). Você precisa executar o Profiler para ver todas as chamadas da função e suas estatísticas. Este plano mostra apenas 6 leituras. O plano para linha individual tem 4 leituras, então o número real seria próximo a: 6 + 2779 * 4 = 6 + 11,116 = 11,122.

CROSS APPLY com TOP

Eventualmente, descobri o CROSS APPLYe como pode ser aplicado ;-) neste caso.

SELECT
    [dbo].[PortalElevators].elevatorsId
    ,LastItemID
FROM
    [dbo].[PortalElevators]
    CROSS APPLY
    (
        SELECT TOP(1) [dbo].[PlaybackStats].[DataSourceRowID] AS LastItemID
        FROM [dbo].[PlaybackStats]
        WHERE [dbo].[PlaybackStats].ElevatorID = [dbo].[PortalElevators].elevatorsId
        ORDER BY [dbo].[PlaybackStats].[DataSourceRowID] DESC
    ) AS CA
;

Aqui, o otimizador é inteligente o suficiente para fazer aproximadamente 2.000 buscas. Você pode ver que o número de leituras é muito menor do que para group by. Velozes.

Curiosamente, o número de leituras aqui (11.850) é um pouco mais do que as leituras que estimei com UDF (11.122). As estatísticas de IO da tabela CROSS APPLYtêm 11.844 leituras e 2.779 contagens de varredura da tabela grande, o que fornece 11,844 / 2,779 ~= 4.26leituras por busca de índice. Muito provavelmente, as buscas para alguns valores usam 4 leituras e para outros 5, com média de 4,26. Existem 2.779 buscas, mas há valores apenas para 2.130 linhas. Como eu disse, é difícil obter um número real de leituras com UDF sem o profiler.

CTE recursivo

Como foi apontado nos comentários, Paul White descreveu um método Recursive Index Skip Scan para encontrar valores distintos em uma tabela grande sem executar uma varredura de índice completa, mas fazer buscas de índice recursivamente. Para iniciar a recursão, precisamos encontrar o valor MINou MAXpara uma âncora e, em seguida, cada etapa da recursão adiciona o próximo valor, um por um. O post explica em detalhes.

WITH RecursiveCTE
AS
(
    -- Anchor
    SELECT TOP (1) [ElevatorID], [DataSourceRowID]
    FROM [dbo].[PlaybackStats]
    ORDER BY [ElevatorID] DESC, [DataSourceRowID] DESC

    UNION ALL

    -- Recursive
    SELECT R.[ElevatorID], R.[DataSourceRowID]
    FROM
    (
        -- Number the rows
        SELECT
            T.[ElevatorID], T.[DataSourceRowID]
            ,ROW_NUMBER() OVER (ORDER BY T.[ElevatorID] DESC, T.[DataSourceRowID] DESC) AS rn
        FROM
            [dbo].[PlaybackStats] AS T
            INNER JOIN RecursiveCTE AS R ON R.[ElevatorID] > T.[ElevatorID]
    ) AS R
    WHERE
        -- Only the row that sorts lowest
        R.rn = 1
)
SELECT [ElevatorID], [DataSourceRowID]
FROM RecursiveCTE
OPTION (MAXRECURSION 0);

É bastante rápido, embora execute quase o dobro da quantidade de leituras do CROSS APPLY. Faz 12.781 leituras Worktablee 8.524 em PlaybackStats. Por outro lado, ele realiza tantas buscas quantos forem os valores distintos na tabela grande. CROSS APPLYwith TOPexecuta tantas buscas quanto as linhas da pequena tabela. No meu caso, a tabela pequena possui 2.779 linhas, mas a tabela grande possui apenas 2.130 valores distintos.

Resumo

                         Logical Reads       Duration
CROSS APPLY with MAX           482,121          6,604
GROUP BY with MAX              482,123          6,581
Scalar UDF                    ~ 11,122            728
Recursive                       21,305             30
CROSS APPLY with TOP            11,850              9 (nine!)

Executei cada consulta três vezes e escolhi o melhor momento. Não houve leituras físicas.

Conclusão

Neste caso especial de greatest-n-per-groupproblema temos:

• n=1;
• o número de grupos é muito menor que o número de linhas em uma tabela;
• há índice apropriado;

Dois melhores métodos são:

1. Caso tenhamos uma pequena tabela com a lista de grupos, o melhor método é CROSS APPLYcom TOP.

2. No caso de termos apenas uma tabela grande, o melhor método é Recursive Index Skip Scan.