O PERCENTILE_CONT ainda oferece um desempenho terrível ao calcular medianas nas versões 2022 do SQL Server?

Presumo que uma das fontes online a que você se refere seja Qual é a maneira mais rápida de calcular a mediana?

"2012_A" e "2012_B" abaixo referem-se às consultas desse artigo.

Usando dados de teste semelhantes...

CREATE TABLE dbo.obj(id INT IDENTITY(1,1), val INT, PRIMARY KEY (val, id));
 
INSERT dbo.obj(val) 
SELECT TOP (10000000) CRYPT_GEN_RANDOM(3) as val
FROM sys.all_columns AS c 
CROSS JOIN sys.all_objects AS o
CROSS JOIN sys.all_objects AS o2

2012_A com nível de compatibilidade 2014

Tentar a mesma instância do SQL Server 2022 como os testes subsequentes, mas com o nível de compatibilidade de 120 (SQL Server 2014) levou 1 minuto e 56 segundos

--Table 'Worktable'. Scan count 6, logical reads 60127770, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
--Table 'obj'. Scan count 1, logical reads 21109, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
--CPU time = 82000 ms,  elapsed time = 116489 ms.
SELECT TOP 1 PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY val) OVER () 
FROM dbo.obj
OPTION (USE hint('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_120'))

2012_A em 2022

No SQL Server 2019+, o plano de execução agora pode usar agregações janeladas em modo de lote e isso foi consideravelmente mais rápido que o esforço anterior, com um tempo decorrido de 7,5 segundos, mas ainda é mais lento que o método mais rápido proposto naquele artigo (talvez atualize este método de "terrível" para "ruim") .

--Table 'obj'. Scan count 1, logical reads 21109, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
--Table 'Worktable'. Scan count 2, logical reads 918272, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
-- CPU time = 4407 ms,  elapsed time = 7525 ms.
SELECT TOP 1 PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY val) OVER () 
FROM dbo.obj;

PERCENTILE_CONTé implementado como uma função analítica e todas as 10.000.000 linhas de origem são colocadas em spool no Window Aggregate à direita para que possam ter o resultado adicionado a elas assim que for calculado. Isso é inerentemente intensivo em recursos.

PERCENTILE_CONTnão pôde ser implementado como um agregado de streaming porque você precisa ler todo o stream para obter a contagem e, assim, conhecer as linhas de seu interesse.

O método "vencedor" do artigo SQL Performance ainda vence em 2022 - com um tempo decorrido de cerca de 1 segundo.

2012_B

--Table 'obj'. Scan count 9, logical reads 21499, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
--Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
--CPU time = 251 ms,  elapsed time = 133 ms.
DECLARE @c BIGINT = (SELECT COUNT(*) FROM dbo.obj);
 

--Table 'obj'. Scan count 1, logical reads 10567, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
--CPU time = 313 ms,  elapsed time = 859 ms.
SELECT  AVG(1.0 * val)
FROM (
    SELECT val FROM dbo.obj
     ORDER BY val
     OFFSET (@c - 1) / 2 ROWS
     FETCH NEXT 1 + (1 - @c % 2) ROWS ONLY
) AS x;

Resultado Aproximado

O SQL Server 2022 inclui uma nova função agregada APPROX_PERCENTILE_CONT. Como esta é uma função agregada, não requer uma OVERcláusula - ou o que TOP 1adicionei à PERCENTILE_CONTconsulta acima

--Table 'obj'. Scan count 9, logical reads 21499, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
--Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
--CPU time = 4983 ms,  elapsed time = 881 ms.
select APPROX_PERCENTILE_CONT (0.5) WITHIN GROUP (ORDER BY val)
FROM dbo.obj

Embora o tempo decorrido tenha sido competitivo com 2012_B, no geral, ele usou muito mais tempo de CPU e, além disso, retorna apenas uma aproximação do resultado correto - portanto, para este teste, eu ainda preferiria 2012_B

Ainda é PERCENTILE_CONTextremamente lento nesta tarefa nas versões recentes (ou seja, 2022 ou posteriores) do SQL Server?

É sim. Nenhuma das facilidades de percentil é especificamente otimizada para o caso mediano. A natureza geral da implementação (para qualquer percentil, não apenas 0,5) significa que ela é mais flexível, mas menos eficiente do que poderia ser uma função ou agregação de janela somente mediana específica.

Usando os dados de teste de Martin, descobri que 2012_B foi executado em cerca de 400ms :

DECLARE @c BIGINT = (SELECT COUNT(*) FROM dbo.obj);

DECLARE @Start datetime2 = SYSUTCDATETIME();

SELECT  AVG(1.0 * val)
FROM (
    SELECT val FROM dbo.obj
     ORDER BY val
     OFFSET (@c - 1) / 2 ROWS
     FETCH NEXT 1 + (1 - @c % 2) ROWS ONLY
) AS x;

SELECT [2012_B] = DATEDIFF(MILLISECOND, @Start, SYSUTCDATETIME());

Isso pode ser aprimorado no nível de compatibilidade 150 (SQL Server 2019) ou posterior usando o modo de lote no Rowstore (BMOR) por meio de uma expressão diferente do mesmo algoritmo básico:

DECLARE @c bigint = (SELECT COUNT_BIG(*) FROM dbo.obj);

DECLARE @Start datetime2 = SYSUTCDATETIME();

SELECT 
    IIF
    (
        @c % 2 = 1, 
        SUM(SQ1.val), 
        SUM(SQ1.val) * 5e-1
    ) 
FROM 
(
    SELECT TOP (1 + (1 - @c % 2))
        A.val
    FROM 
    (
        SELECT 
            O.val, 
            rn = ROW_NUMBER() OVER (ORDER BY O.val ASC)
        FROM dbo.obj AS O
    ) AS A
    WHERE
        A.rn >= ((@c - 1) / 2)
    ORDER BY
        A.rn
) AS SQ1;

SELECT BMOR = DATEDIFF(MILLISECOND, @Start, SYSUTCDATETIME());

Essa implementação é executada em cerca de 275 ms , com todas as operações caras usando o processamento em lote (destaque em azul).

Observação BMOR requer Enterprise Edition ou equivalente.