Início / user-2065144

Nelson O's questions

Martin Hope
Nelson O
Asked: 2025-01-03 02:05:40 +0800 CST
  • 7

Basicamente, minha pergunta se resume a observar esses dois planos de consulta (do SQL Server 2019):

  1. Planeje usando uma função escalar definida em SQL.

Plano de consulta de função escalar SQL

  1. Planeje usando uma função escalar definida pelo CLR.

Plano de consulta de função escalar CLR

Eu defini uma função escalar para analisar uma representação de string de um endereço IP no binary(16)valor do endereço IPv6 (mapeando endereços IPv4 para IPv6). Primeiro, implementei em SQL, mas depois também implementei em C# usando a IPAddressclasse interna para analisar o valor. Estou tentando usar essa função para unir uma tabela contendo strings de endereço IP a uma tabela com uma lista de blocos CIDR analisados ​​(índice clusterizado em [Start]e [End] binary(16)valores).

Havia duas maneiras de escrever a consulta SQL:

  1. Use a função escalar diretamente nos JOINcritérios.
SELECT
    *
FROM
    [dbo].[values]
    val
LEFT JOIN
    [dbo].[cidr]
    cidr
        ON [dbo].[fn_ParseIP](val.[IpAddress]) BETWEEN cidr.[Start] AND cidr.[End]
;
  1. Calcule a função escalar em uma APPLYcláusula antes de referenciá-la no JOIN.
SELECT
    val.*, cidr.*
FROM
    [dbo].[values]
    val
CROSS APPLY
    (
        SELECT [ParsedIpAddress] = [dbo].[fn_ParseIP](val.[IpAddress])
    )
    calc
LEFT JOIN
    [dbo].[cidr]
    cidr
        ON calc.[ParsedIpAddress] BETWEEN cidr.[Start] AND cidr.[End]
;

Em meus testes, [dbo].[values]continha 17 linhas (e foi definido usando VALUESem vez de ser uma tabela real) e [dbo].[cidr]continha 986.320 linhas.

Ao usar uma função escalar definida em SQL, a consulta 1 leva cerca de 7,5 minutos para ser executada e a consulta 2 leva menos de 1 segundo.

Ao usar uma função escalar CLR, ambas as consultas levam cerca de 2,5 minutos para serem executadas, mas a consulta 2 tem um nó extra no plano de consulta para calcular a função escalar após a junção.

A diferença é que, em última análise, ao referenciar a função escalar que foi definida em SQL, consigo fazê-la gerar o primeiro plano em que ela calcula os resultados da função escalar primeiro e, em seguida, usa-os como o predicado seek no [dbo].[cidr]índice clusterizado ao executar a junção. Mas ao usar a função CLR, ela sempre executa o cálculo como parte da busca do índice clusterizado (e filtro), então ela está executando a função significativamente com mais frequência para obter os resultados.

Minha suposição é que esse comportamento pode ser devido ao planejador de consultas acreditar que a função é não determinística, mas tenho a função CLR implementada com os seguintes atributos:

    [SqlFunction(
        DataAccess = DataAccessKind.None,
        IsDeterministic = true,
        IsPrecise = true
    )]
    [return: SqlFacet(IsFixedLength = true, IsNullable = true, MaxSize = 16)]
    public static SqlBinary fn_CLR_ParseIP([SqlFacet(MaxSize = 50)] SqlString ipAddress)
    { }

Minha esperança era que eu pudesse confiar na biblioteca padrão .NET para lidar com a análise de endereço IP para mim no SQL. Atualmente, temos alguns processos que funcionam apenas com endereços IPv4, e preciso atualizá-los para funcionar com IPv6 também. Em alguns dos nossos grandes bancos de dados, esse processamento é muito lento, então eu esperava que a lógica de análise no .NET fosse mais eficiente. Parece que a função CLR em si é mais rápida do que minha implementação SQL, mas o efeito no plano de consulta é significativamente pior.

Provavelmente posso reescrever a consulta para usar tabelas temporárias para analisar os endereços IP primeiro, e isso deve resolver esse problema. Também posso obter resultados decentes ao definir uma função de valor de tabela CLR equivalente que retorna apenas uma única linha.

No entanto, gostaria de saber se há algo que estou esquecendo que tornaria mais fácil usar uma função escalar CLR como parte de um predicado de filtro. É apenas uma má ideia e eu deveria prosseguir com algumas dessas alternativas, ou há algo que eu poderia fazer que tornaria mais fácil trabalhar com a função CLR como uma substituição imediata para a função SQL?

Para quem estiver interessado, aqui está a consulta final que está apresentando bom desempenho usando o conceito dado na resposta de T N.

SELECT
    *
FROM
    [dbo].[values]
    val
CROSS APPLY
    (
        SELECT [IpAddress] = [dbo].[fn_CLR_ParseIP](val.[IpAddress])
    )
    parsed
OUTER APPLY
    (
        SELECT TOP (1)
            *
        FROM
            [dbo].[cidr]
            _cidr
        WHERE
                _cidr.[range_start] <= parsed.[IpAddress]
            AND _cidr.[range_end]   >= parsed.[IpAddress]
        ORDER BY
            _cidr.[range_start] DESC
    )
    cidr
;
sql-server