SQL Server - Como as páginas de dados são armazenadas ao usar um índice clusterizado

Question

crokusek

Asked: 2023-12-06 09:58:11 +0800 CST2023-12-06 09:58:11 +0800 CST 2023-12-06 09:58:11 +0800 CST

Como permitir o envio de predicado em uma visualização que usa agrupar por

772

Temos uma tabela Ecom.McProductToVendorProductCodeMap que possui um PK de vários campos, conforme mostrado:

Em seguida, uma visualização agrupa essa tabela para calcular uma métrica e é agrupada pelos dois primeiros campos dessa PK:

ALTER view ECom.McProductToVendorProductMd5SourceView
as
select ClientAppPrivateLabelId,
       BrandId, 
       convert(nvarchar(32), HashBytes('MD5', 
              string_agg(
                  convert(varchar(max), MaterialNumber + ',' + VendorProductCode + ',' + convert(varchar(30), VendorProductStatusId)),    -- sense any MaterialNumber/VendorProductCode/Status changes
                  ',') within group (order by MaterialNumber)
          ), 2) as Md5,
       Count(*) as Count,
       max(ModifiedUtc) as ModifiedUtc
  from ECom.McProductToVendorProductCodeMap 
 group by ClientAppPrivateLabelId, BrandId

Agora, se selecionarmos na visualização usando esses 2 campos como predicados diretamente, ocorrerá uma busca de índice usando esses 2 campos (19 mil linhas e a dica de ferramenta mostra "Seek Predicate" nos 2 campos):

select * from ECom.McProductToVendorProductMd5SourceView
where ClientAppPrivateLabelId = 101 and BRandId = 3

No entanto, ao tentar ingressar na mesma visualização usando os mesmos 2 predicados, ele busca apenas ClientAppPrivateLabelId e não BrandId . A dica de junção de loop não ajudou e substituir a junção por uma aplicação cruzada também não ajudou.

select IsNull(convert(smallint, Value), 0) as BrandId 
  into #Brands 
  from string_split('2,3', ',');    
    
select ClientAppPrivateLabelId, b.BrandId, Md5, Count, ModifiedUtc
  from #Brands b
 inner loop join ECom.McProductToVendorProductMd5SourceView m
    on m.BrandId = b.BrandId
   and m.ClientAppPrivateLabelId = 101;

A visualização é simples, exceto pelo cálculo de janelas:

ALTER view ECom.McProductToVendorProductMd5SourceView
as
select ClientAppPrivateLabelId,
       BrandId, 
       convert(nvarchar(32), HashBytes('MD5', 
              string_agg(
                  convert(varchar(max), MaterialNumber + ',' + VendorProductCode + ',' + convert(varchar(30), VendorProductStatusId)),    -- sense any MaterialNumber/VendorProductCode/Status changes
                  ',') within group (order by MaterialNumber)
          ), 2) as Md5,
       Count(*) as Count,
       max(ModifiedUtc) as ModifiedUtc
  from ECom.McProductToVendorProductCodeMap 
 group by ClientAppPrivateLabelId, BrandId

Por que não usa BrandId? A tabela original define BrandId como um smallint não anulável.

Cole o plano: https://www.brentozar.com/pastetheplan/?id=ryZWp86Hp

Atualização nº 1 (05/12/2023)

Converteu a visualização em uma função com valor de tabela (TVF):

alter function ECom.McProductToVendorProductMd5(
   @pBrandId smallint,
   @pClientAppPrivateLabelId smallint
)
returns table as 
return
select ClientAppPrivateLabelId,
       BrandId, 
       convert(nvarchar(32), HashBytes('MD5', 
              string_agg(
                  -- Sense any MaterialNumber/VendorProductCode/Status changes
                  convert(varchar(max), MaterialNumber + ',' + VendorProductCode + ',' + convert(varchar(30), VendorProductStatusId)),    
                  ',') within group (order by MaterialNumber)
          ), 2) as Md5,
       Count(*) as Count,
       max(ModifiedUtc) as ModifiedUtc
  from ECom.McProductToVendorProductCodeMap m
 where m.BrandId = @pBrandId
   and m.ClientAppPrivateLabelId = @pClientAppPrivateLabelId
 group by ClientAppPrivateLabelId, BrandId

e ajustou a consulta para usá-la via aplicação cruzada:

select ClientAppPrivateLabelId, b.BrandId, Md5, Count, ModifiedUtc
  from #Brands b
 cross apply ECom.McProductToVendorProductMd5(b.BrandId, @pCaplId) m;

Mesmo problema: https://www.brentozar.com/pastetheplan/?id=SJnRODaBT

Ele está usando uma junção de mesclagem e não buscando no BrandId

1 respostas

Voted

Paul White · Answer 1 · 2023-12-06T20:04:43+08:00

O SQL Server está muito interessado em reescrever apply como uma junção antes do início da otimização. É muito bom nisso também. É menos bom transformar uma junção em uma aplicação, que é o que você deseja aqui.

Como resultado, quando você escreve uma junção, ela permanece como uma junção. Quando você escreve uma aplicação, ela é transformada em uma junção.

Não há nenhuma dica para evitar a reescrita inicial de apply para join, embora o sinalizador de rastreamento não documentado 9114 execute esta função. Comportamentos anteriores habilitados por sinalizadores de rastreamento não documentados como esse eventualmente surgiram como USE HINTopções, então talvez essa situação mude um dia.

Enquanto isso, para contornar isso, escreva a junção como uma aplicação e use um OUTER APPLYou redundante OFFSETpara evitar que o otimizador transforme a aplicação em uma junção.

O SQL Server é capaz de reescrever a aplicação externa e OFFSET/TOPuma junção em princípio. Isso não é feito OFFSET/TOPespecificamente porque as pessoas usaram isso para evitar a transformação em uma junção com tanta frequência no passado. A aplicação externa é menos passível de transformação, mas pode acontecer.

Aplicação externa

SELECT
    MA.ClientAppPrivateLabelId,
    B.BrandId,
    MA.Md5,
    MA.[Count],
    MA.ModifiedUtc
FROM #Brands AS B
OUTER APPLY
(
    SELECT
        M.* 
    FROM ECom.McProductToVendorProductMd5SourceView AS M
    WHERE
        M.ClientAppPrivateLabelId = 101
        AND M.BrandId = B.BrandId
) AS MA;

Deslocamento redundante

SELECT
    MA.ClientAppPrivateLabelId,
    B.BrandId,
    MA.Md5,
    MA.[Count],
    MA.ModifiedUtc
FROM #Brands AS B
CROSS APPLY
(
    SELECT
        M.* 
    FROM ECom.McProductToVendorProductMd5SourceView AS M
    WHERE
        M.ClientAppPrivateLabelId = 101
        AND M.BrandId = B.BrandId
    ORDER BY
        M.ClientAppPrivateLabelId,
        M.BrandId
        OFFSET 0 ROWS
) AS MA;

Se você quiser encapsular isso dentro da sua função, uma implementação possível é:

CREATE OR ALTER FUNCTION ECom.McProductToVendorProductMd5
(
    @pBrandId integer,
    @pClientAppPrivateLabelId integer
)
RETURNS table
AS
RETURN 
    SELECT
        M.ClientAppPrivateLabelId,
        M.BrandId,
        Md5 = 
            CONVERT(char(32),
                HASHBYTES(N'MD5',
                    STRING_AGG(CSV.cols, ',')
                        WITHIN GROUP (ORDER BY M.MaterialNumber)), 
                2),
        [Count] = COUNT_BIG(*),
        ModifiedUtc = MAX(M.ModifiedUtc)
    FROM 
        ECom.McProductToVendorProductCodeMap AS M
    CROSS APPLY 
    (
        VALUES
        (
            CONVERT(varchar(max),
                CONCAT_WS(',', M.MaterialNumber, M.VendorProductCode, M.VendorProductStatusId))
        )
    ) AS CSV (cols)
    WHERE
        M.BrandId = @pBrandId
        AND M.ClientAppPrivateLabelId = @pClientAppPrivateLabelId
    GROUP BY
        M.ClientAppPrivateLabelId,
        M.BrandId
    ORDER BY
        M.ClientAppPrivateLabelId
        OFFSET 0 ROWS;

Como permitir o envio de predicado em uma visualização que usa agrupar por

Aplicação externa

Deslocamento redundante

conectar ao servidor PostgreSQL: FATAL: nenhuma entrada pg_hba.conf para o host

Como fazer a saída do sqlplus aparecer em uma linha?

Selecione qual tem data máxima ou data mais recente

Como faço para listar todos os esquemas no PostgreSQL?

Listar todas as colunas de uma tabela especificada

Como usar o sqlplus para se conectar a um banco de dados Oracle localizado em outro host sem modificar meu próprio tnsnames.ora

Como você mysqldump tabela (s) específica (s)?

Listar os privilégios do banco de dados usando o psql

Como inserir valores em uma tabela de uma consulta de seleção no PostgreSQL?

Como faço para listar todos os bancos de dados e tabelas usando o psql?

Como permitir o envio de predicado em uma visualização que usa agrupar por

1 respostas

Aplicação externa

Deslocamento redundante

relate perguntas