Início / dba / Perguntas / 334217
Accepted
Yano_of_Queenscastle
Asked: 2023-12-20 19:13:19 +0800 CST

Como o SQL Server otimiza JOIN em hierarquiaid::IsDescendantOf()?

  • 772

Tenho uma tabela com estrutura em árvore (definida por hierarchyidcoluna) e quero selecionar todos os descendentes de um determinado registro. Para isso, estou usando hiearchyid.IsDescendantOf()o método.

Eu esperava que, como não estou fazendo comparações simples, mas estou executando operações (neste caso, estou chamando o IsDescendantOf()método), obterei um plano de execução terrível com varreduras de índice e outros enfeites.

Mesmo assim, o SQL Server o otimizou para uma pequena busca de índice.

Plano de execução

Estou intrigado por que e como.

A chamada de métodos em tipos CLR geralmente é otimizada? Presumi que o SQL Server vê os tipos CLR como uma caixa preta opaca e, portanto, não pode fazer sua mágica funcionar nele. (Uma vez que também não é possível fazer isso em funções SQL nativas.)

Ou isso é apenas para este método específico? (Como os hieararchyidvalores são ordenados em profundidade, eu poderia obter resultados semelhantes apenas fazendo comparações.)

Demonstração:

CREATE TABLE dbo.HierarchyExample (
    Id INT PRIMARY KEY,
    Hieararchy HIERARCHYID NOT NULL
);

INSERT INTO dbo.HierarchyExample(Id, Hieararchy)
VALUES
(1,  hierarchyid::Parse('/1/')),
(2,  hierarchyid::Parse('/1/1/')),
(3,  hierarchyid::Parse('/1/2/')),
(4,  hierarchyid::Parse('/1/3/')),
(5,  hierarchyid::Parse('/1/3/1/')),
(6,  hierarchyid::Parse('/1/3/2/')),
(7,  hierarchyid::Parse('/1/3/3/')),
(8,  hierarchyid::Parse('/1/4/')),
(9,  hierarchyid::Parse('/1/4/1/')),
(10, hierarchyid::Parse('/1/4/2/'));

CREATE INDEX IX_HierarchyExample_Hierarchy 
ON dbo.HierarchyExample (Hieararchy);

SELECT descendant.*
FROM HierarchyExample ancestor
    INNER JOIN HierarchyExample descendant
        ON descendant.Hieararchy.IsDescendantOf(ancestor.Hieararchy) = 1
WHERE ancestor.Id = 1

DROP TABLE IF EXISTS dbo.HierarchyExample;

O plano de execução

O violino

sql-server

1 respostas

  1. Best Answer

    descendant.Hieararchy.IsDescendantOf(ancestor.Hieararchy) = 1certamente não parece que deveria ser sargável, mas parece fazer algumas travessuras logo no início do processo para este caso específico .

    Se eu tentar

    declare @x hierarchyid;     

SELECT *
FROM HierarchyExample descendant
WHERE descendant.Hieararchy.IsDescendantOf(@x) = 1
OPTION (querytraceon 3604, querytraceon 8605, querytraceon 8606);

    O plano de execução resultante mostra uma busca

    Seek Keys[1]: Start: [tempdb].[dbo].[HierarchyExample].Hieararchy >= Scalar Operator([@x]), 
               End:  [tempdb].[dbo].[HierarchyExample].Hieararchy <= Scalar Operator([@x].DescendantLimit())

    A expressão de intervalo já está presente na árvore convertida .

    A árvore convertida é a árvore de análise do analisador, importada e transformada (“convertida”) em uma estrutura de árvore com a qual os estágios iniciais de compilação consideram conveniente trabalhar.

    *** Converted Tree: ***
    LogOp_Project QCOL: [descendant].Id QCOL: [descendant].Hieararchy

        LogOp_Select

            LogOp_Get TBL: HierarchyExample(alias TBL: descendant) HierarchyExample TableID=1093578934 TableReferenceID=0 IsRow: COL: IsBaseRow1000 

            ScaOp_Logical x_lopAnd

                ScaOp_Comp x_cmpLe

                    ScaOp_Identifier COL: @x 

                    ScaOp_Identifier QCOL: [descendant].Hieararchy

                ScaOp_Comp x_cmpLe

                    ScaOp_Identifier QCOL: [descendant].Hieararchy

                    ScaOp_UdtFunction EClrFunctionType_UdtMethodDescendantLimit IsDet NoDataAccess  TI(hierarchyid,Null,Var,ML=892) 

                        ScaOp_Identifier COL: @x 

        AncOp_PrjList

    Portanto, concluo que durante a análise isso é convertido em um predicado de intervalo potencialmente pesquisável.

    Essa transformação é mencionada de passagem no artigo Suporte relacional para cenários de esquema flexível, embora não entre em grandes detalhes.

    Para apoiar os cenários considerados, definimos uma operação específica que é reescrita internamente antes que o processamento relacional ocorra. O fato de uma coluna H1ser descendente de H2é expresso por H1.IsDescendant(H2)e após a tradução torna-se o predicado de intervalo: H2 >= H1 and H2 <= H1.DescendantLimit().

    Depois disso, a otimização acontece normalmente. As regras de transformação de consulta usadas depois disso são apenas SelIdxToRnge SelToTrivialFilter- nada específico para HierarchyId.

    A árvore convertida para seu exemplo de junção mais complicado está abaixo, mostrando praticamente a mesma coisa

    *** Converted Tree: ***
    LogOp_Project QCOL: [descendant].Id QCOL: [descendant].Hieararchy

        LogOp_Select

            LogOp_Join

                LogOp_Get TBL: HierarchyExample(alias TBL: ancestor) HierarchyExample TableID=1093578934 TableReferenceID=0 IsRow: COL: IsBaseRow1000 

                LogOp_Get TBL: HierarchyExample(alias TBL: descendant) HierarchyExample TableID=1093578934 TableReferenceID=0 IsRow: COL: IsBaseRow1001 

                ScaOp_Logical x_lopAnd

                    ScaOp_Comp x_cmpLe

                        ScaOp_Identifier QCOL: [ancestor].Hieararchy

                        ScaOp_Identifier QCOL: [descendant].Hieararchy

                    ScaOp_Comp x_cmpLe

                        ScaOp_Identifier QCOL: [descendant].Hieararchy

                        ScaOp_UdtFunction EClrFunctionType_UdtMethodDescendantLimit IsDet NoDataAccess  TI(hierarchyid,Null,Var,ML=892) 

                            ScaOp_Identifier QCOL: [ancestor].Hieararchy

            ScaOp_Comp x_cmpEq

                ScaOp_Identifier QCOL: [ancestor].Id

                ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=1)

        AncOp_PrjList 

*******************
    • 5

relate perguntas