Início / dba / Perguntas / 311406
Accepted
Ross Bush
Asked: 2022-04-28 10:29:02 +0800 CST

UDF e comportamento de função integrada

  • 772

Faça as seguintes consultas:

DECLARE @X VARCHAR(200) = '1,2,3,4'

SELECT
    *,
    dbo.aUserDefinedScalarFunction(4) AS ScalarValue
FROM
   MyTable T
   INNER JOIN dbo.aUserDefineTableFunction(@X) A ON T.SomeID=A.SomeID
WHERE 
  (T.ID1 IS NULL OR T.ID1 IN (SELECT [value] FROM STRING_SPLIT(@X,',')))
  AND
  (T.ID2 IS NULL OR T.ID2 IN (SELECT Value FROM dbo.MySplitterFunction(@X))

Normalmente crio #tempTables indexadas para as WHEREcondições acima, descobri que para ter um desempenho melhor em grandes conjuntos de dados. No entanto, ainda não consigo encontrar respostas definitivas para as seguintes perguntas:

  1. O analisador de consultas otimizará aUserDefinedScalarFunction(4) como ScalarValue ou será avaliado para cada registro?

  2. O INNER JOIN dbo.aUserDefineTableFunction(@X) será materializado em uma tabela temporária uma vez ou será executado para cada registro? A função retorna table (não uma variável de tabela).

  3. O resultado de SELECT [value] FROM STRING_SPLIT(@X,',') é otimizado ou é avaliado para cada comparação?

  4. O resultado de SELECT Value FROM dbo.MySplitterFunction(@X) é otimizado ou é avaliado durante cada comparação?

sql-server optimization

2 respostas

  1. Best Answer

    1. Isso depende da função. Para uma função determinística especificada com schemabinding , o otimizador geralmente poderá avaliar seu resultado apenas uma vez, armazenando a resposta em cache.

      Para detalhes, consulte:

    2. As funções com valor de tabela inline são expandidas no texto da consulta de chamada antes da otimização, portanto, não há oportunidade especial para avaliação e armazenamento em cache antecipados. Isso não significa que o plano de execução não apresentará otimizações de cache e reutilização. Onde aplicável, o otimizador irá considerá-los (por exemplo, um spool) exatamente como faria para uma consulta sem uma função embutida.

    3. Geralmente, o otimizador será capaz de dizer que SPLIT_STRINGem uma entrada constante retorna resultados determinísticos. É provável que o plano avalie a divisão apenas uma vez, mas depende da forma do plano.

    4. Assumindo que a função divisora ​​é uma função com valor de tabela de várias instruções, é muito provável que o resultado seja avaliado uma vez. Para obter detalhes e como saber se o armazenamento em cache ocorre a partir do plano de execução, consulte:

    Tudo isso dito, você está certo em ser cauteloso sobre tudo isso. O otimizador frequentemente poderá avaliar expressões uma vez e reutilizar o resultado, mas isso não é garantido e geralmente requer análise especializada para determinar.

    Forçar a materialização usando tabelas temporárias é frequentemente a solução mais robusta, pois garante uma determinada ordem e número de avaliações.

    • 3

  2. Cada linha avaliará as funções na cláusula where. Pesquise a consulta SARGABLE. https://www.sqlshack.com/how-to-use-sargable-expressions-in-t-sql-queries-performance-advantages-and-examples/

    DECLARE @X VARCHAR(200) = '1,2,3,4'

DECLARE @StringElements TABLE (Snippet VARCHAR(200))
INSERT INTO @StringElements (Snippet)
(SELECT [value] FROM STRING_SPLIT(@X,','))

DECLARE @XBits TABLE (Snippet VARCHAR(200))
INSERT INTO @XBits 
SELECT Value FROM dbo.MySplitterFunction(@X)

DECLARE @ScalarValue INT = dbo.aUserDefinedScalarFunction(4) --AS ScalarValue


SELECT
    *,
    @ScalarValue AS ScalarValue
FROM
   MyTable T
   INNER JOIN dbo.aUserDefineTableFunction(@X) A ON T.SomeID=A.SomeID
WHERE 
  (T.ID1 IS NULL OR T.ID1 IN (SELECT snippet [value] FROM @StringElements))
  AND
  (T.ID2 IS NULL OR T.ID2 IN (SELECT Snippet [Value] FROM @Xbits))
    • -2

relate perguntas