Início / dba / Perguntas / 320285
Accepted
lifeisajourney
Asked: 2022-11-29 09:14:52 +0800 CST

Desempenho de XML x UDF no SQL Server

  • 772

Eu tenho uma situação em que tenho que decidir entre receber XML como um parâmetro de entrada para o procedimento armazenado ou uma lista de valores separados por vírgula e analisá-los usando uma função com valor de várias tabelas.

Logicamente falando, eu preferiria XML em vez de udfs porque udfs podem causar muitos problemas de desempenho. Mas vejo que o XML no back-end também usa funções com valor de tabela para analisar o xml. Eu queria saber se o XML é realmente melhor do que o UDF de várias tabelas e, em caso afirmativo, o que o torna melhor?

CREATE FUNCTION [dbo].[tmp_array_commaValues] ( @string varchar(4000))
RETURNS  @final TABLE(Value varchar(100))
AS
begin

    WITH tmp_cte(start, stop) AS

    (
      SELECT  1, CHARINDEX(',' , @string )
      
      UNION ALL
      
      SELECT  stop + 1, CHARINDEX(',' ,@string  , stop + 1)
      FROM tmp_cte
      WHERE stop > 0
    )

    insert into @final
    SELECT   SUBSTRING(@string , start, CASE WHEN stop > 0 THEN stop-start ELSE 4000 END) AS stringValue
    FROM tmp_cte   

    return
end   

/* query */
Select value 
  From [dbo].[tmp_array_commaValues]('TAG,ABC,ZYX,BAG,TRY,LAG,LEAD,NETHERLANDS,TAG1,ABC1,ZYX1,BAG1,TRY1,SUN12,NANGE1')

/********** XML *********/
Declare @xml XML = '<root><id>Tag</id><id>Lag</id>
<id>Tag1</id><id>Lag1</id>
<id>Tag2</id><id>Lag2</id>
<id>Tag3</id><id>Lag3</id>
<id>Tag4</id><id>Lag4</id>
<id>Tag5</id><id>Lag5</id>
<id>Tag6</id><id>Lag6</id>
<id>Tag7</id><id>Lag7</id>
</root>'

select t.c.value('.','varchar(100)') as string
  From @xml.nodes('root/id/text()') As t(c)

Plano de execução para UDF: https://www.brentozar.com/pastetheplan/?id=SJpX2DMwi

Plano de execução para XML: https://www.brentozar.com/pastetheplan/?id=ByQv2PMvo

sql-server

1 respostas

  1. Best Answer

    udfs pode causar muitos problemas de desempenho

    Isso não é necessariamente verdade. Um código ruim causará problemas de desempenho, independentemente de onde for colocado. Existem algumas desvantagens com certos tipos de UDFs:

    1. As funções escalares são executadas RBAR (linha por linha agonizante) até o SQL Server 2019, onde certas funções escalares são inlineáveis .
    2. As funções escalares impedem o paralelismo em qualquer plano de qualquer objeto que as referencie diretamente ou qualquer objeto de dependência em qualquer parte da pilha de chamadas.
    3. As funções com valor de tabela de várias instruções evitam o paralelismo na zona em que são referenciadas no plano de consulta.

    É uma prática recomendada geralmente não usar funções escalares, por causa de suas múltiplas desvantagens, enquanto uma função com valor de tabela de instrução única é bastante adequada. As funções com valor de tabela de várias instruções são menos problemáticas, pois são serializadas apenas onde são referenciadas no plano de consulta, mas o plano de consulta como um todo ainda pode ter outras zonas paralelizadas.

    Para ler mais sobre coisas que inibem o paralelismo no SQL Server, consulte o artigo de Paul White Forçando um Plano de Execução de Consulta Paralela .

    Eu queria saber se o XML é realmente melhor do que o UDF de várias tabelas e, em caso afirmativo, o que o torna melhor?

    Como acontece com a maioria das coisas de desempenho, eu diria que depende exatamente do que você está fazendo com XML versus sua função com valor de tabela de várias instruções. Com os planos de execução que você forneceu, em uma rápida olhada, parece que o MSTVF pode ter um desempenho melhor (já que está processando menos dados e apenas um plano de execução mais simples com menos etapas, em geral). Mas não posso dizer conclusivamente.

    Você pode usar o Profiler ou Extended Events com o evento SQL:BatchCompleted ou executar SET IO, TIME STATISTICS ONe examinar a Messagesjanela no SSMS para ver CPU Time, Elapsed Timee outros TIME STATISTICSe IO STATISTICSpara ajudar a avaliar qual deles é executado com mais eficiência. Aquele com menos CPU Time, menor Elapsed Timee/ou menos Logical Readssão fatores gerais que você pode usar para determinar qual deles tem melhor desempenho.

    Além disso, a sugestão de Erik Darkling de usar Parâmetros com valor de tabela no procedimento geralmente é uma boa prática. Esta seria a minha escolha das 3 opções.

    Os parâmetros com valor de tabela têm alguma vantagem sobre XML ou análise de string?

    Sim, os dados já são apresentados em formato tabular e não precisam ser processados ​​(no SQL Server) para chegar lá. A única desvantagem tangível de usá-los é que eles são apresentados como uma variável de tabela na qual o SQL Server não mantém estatísticas. Minha solução usual para isso é imediatamente SELECTa variável de tabela em uma tabela temporária e, em vez disso, usar essa tabela temporária durante todo o procedimento.

    Ainda estamos no SQL Server 2012

    Eu recomendo procurar atualizar logo, pois há quatro versões principais atrás da mais recente e também não é mais suportada pelo Microsoft . A data de suporte estendido terminou em julho passado (2022).

    • 4

relate perguntas