sql server - Servidor XML Optimización del rendimiento XML

sql-server performance

Question

Tengo 34 filas en una base de datos, cada fila tiene una columna que contiene xml: el xml está realmente en una columna NVARCHAR (MAX), no en una columna XML.

Para cada fila, selecciono valores en los elementos xml como un único conjunto de resultados. El rendimiento es bastante pobre. He intentado dos consultas diferentes. El primero tarda aproximadamente 22 segundos en ejecutarse y el segundo tarda 7.

Incluso a los 7 segundos, esto es mucho más lento de lo óptimo, espero 1-2 segundos como máximo.

Entonces leí un rumor en línea de que si convierte los datos de NVARCHAR a XML utilizando una tabla temporal o una variable de tabla, obtendrá una ganancia de rendimiento, que al menos en mi caso era cierto ... Ahora se ejecuta en menos de un segundo . Lo que estoy buscando ahora es una explicación que pueda decirme por qué estos 2 enfoques realmente afectan el rendimiento.

22 segundos:

SELECT
    c.ID,
    c.ChannelName,
    [Name] = d.c.value(''name[1]'',''varchar(100)''),
    [Type] = d.c.value(''transportName[1]'',''varchar(100)''),
    [Enabled] = d.c.value(''enabled[1]'',''BIT''),
    [Queued] = d.c.value(''properties[1]/destinationConnectorProperties[1]/queueEnabled[1]'',''varchar(100)''),
    [RetryInterval] = d.c.value(''properties[1]/destinationConnectorProperties[1]/retryIntervalMillis[1]'',''INT''),
    [MaxRetries] = d.c.value(''properties[1]/destinationConnectorProperties[1]/retryCount[1]'',''INT''),
    [RotateQueue] = d.c.value(''properties[1]/destinationConnectorProperties[1]/rotate[1]'',''BIT''),
    [ThreadCount] = d.c.value(''properties[1]/destinationConnectorProperties[1]/threadCount[1]'',''INT''),
    [WaitForPrevious] = d.c.value(''waitForPrevious[1]'',''BIT''),
    [Destination] = COALESCE(
        d.c.value(''properties[1]/channelId[1]'',''varchar(100)''),
        d.c.value(''properties[1]/remoteAddress[1]'',''varchar(100)''),
        d.c.value(''properties[1]/wsdlUrl[1]'',''varchar(1024)'')),

    [DestinationPort] = COALESCE(
        d.c.value(''properties[1]/remotePort[1]'',''varchar(100)''),
        d.c.value(''properties[1]/port[1]'',''varchar(1024)'')),
    [Service] = d.c.value(''properties[1]/service[1]'',''varchar(1024)''),
    [Operation] = d.c.value(''properties[1]/operation[1]'',''varchar(1024)'')
FROM
(
    SELECT
            [ID],
            [ChannelName] = [Name],
            [CFG] = Convert(XML, Channel)
    FROM
            dbo.CHANNEL
) c
CROSS APPLY c.CFG.nodes(''/channel/destinationConnectors/connector'') d(c)

7 segundos, debido al uso de texto (). No tengo idea de por qué el texto acelera las cosas.

SELECT
    c.ID,
    c.ChannelName,
    [Name] = d.c.value(''(name/text())[1]'',''varchar(100)''),
    [Type] = d.c.value(''(transportName/text())[1]'',''varchar(100)''),
    [Enabled] = d.c.value(''(enabled/text())[1]'',''BIT''),
    [Queued] = d.c.value(''(properties/destinationConnectorProperties/queueEnabled/text())[1]'',''varchar(100)''),
    [RetryInterval] = d.c.value(''(properties/destinationConnectorProperties/retryIntervalMillis/text())[1]'',''INT''),
    [MaxRetries] = d.c.value(''(properties/destinationConnectorProperties/retryCount/text())[1]'',''INT''),
    [RotateQueue] = d.c.value(''(properties/destinationConnectorProperties/rotate/text())[1]'',''BIT''),
    [ThreadCount] = d.c.value(''(properties/destinationConnectorProperties/threadCount/text())[1]'',''INT''),
    [WaitForPrevious] = d.c.value(''(waitForPrevious/text())[1]'',''BIT''),
    [Destination] = COALESCE(
        d.c.value(''(properties/channelId/text())[1]'',''varchar(100)''),
        d.c.value(''(properties/remoteAddress/text())[1]'',''varchar(100)''),
        d.c.value(''(properties/wsdlUrl/text())[1]'',''varchar(1024)'')),

    [DestinationPort] = COALESCE(
        d.c.value(''(properties/remotePort/text())[1]'',''varchar(100)''),
        d.c.value(''(properties/port/text())[1]'',''varchar(1024)'')),
    [Service] = d.c.value(''(properties/service/text())[1]'',''varchar(1024)''),
    [Operation] = d.c.value(''(properties/operation/text())[1]'',''varchar(1024)'')
FROM
(
    SELECT
            [ID],
            [ChannelName] = [Name],
            [CFG] = Convert(XML, Channel)
    FROM
            dbo.CHANNEL
) c
CROSS APPLY c.CFG.nodes(''/channel/destinationConnectors/connector'') d(c)

Esta consulta utiliza el enfoque text () pero primero convierte la columna NVARCHAR en una columna xml en una variable de tabla. Se ejecuta en menos de un segundo ...

DECLARE @Xml AS TABLE (
    [ID] NVARCHAR(36) NOT NULL Primary Key,
    [Name] NVARCHAR(100) NOT NULL,
    [CFG] XML NOT NULL
);

INSERT INTO @Xml (ID, Name, CFG)
SELECT
    c.ID,
    c.Name,
    Convert(XML, c.Channel)
FROM
    [dbo].[CHANNEL] c;

SELECT
    c.ID,
    c.ChannelName,
    [Name] = d.c.value(''(name/text())[1]'',''varchar(100)''),
    [Type] = d.c.value(''(transportName/text())[1]'',''varchar(100)''),
    [Enabled] = d.c.value(''(enabled/text())[1]'',''BIT''),
    [Queued] = d.c.value(''(properties/destinationConnectorProperties/queueEnabled/text())[1]'',''varchar(100)''),
    [RetryInterval] = d.c.value(''(properties/destinationConnectorProperties/retryIntervalMillis/text())[1]'',''INT''),
    [MaxRetries] = d.c.value(''(properties/destinationConnectorProperties/retryCount/text())[1]'',''INT''),
    [RotateQueue] = d.c.value(''(properties/destinationConnectorProperties/rotate/text())[1]'',''BIT''),
    [ThreadCount] = d.c.value(''(properties/destinationConnectorProperties/threadCount/text())[1]'',''INT''),
    [WaitForPrevious] = d.c.value(''(waitForPrevious/text())[1]'',''BIT''),
    [Destination] = COALESCE(
        d.c.value(''(properties/channelId/text())[1]'',''varchar(100)''),
        d.c.value(''(properties/remoteAddress/text())[1]'',''varchar(100)''),
        d.c.value(''(properties/wsdlUrl/text())[1]'',''varchar(1024)'')),

    [DestinationPort] = COALESCE(
        d.c.value(''(properties/remotePort/text())[1]'',''varchar(100)''),
        d.c.value(''(properties/port/text())[1]'',''varchar(1024)'')),
    [Service] = d.c.value(''(properties/service/text())[1]'',''varchar(1024)''),
    [Operation] = d.c.value(''(properties/operation/text())[1]'',''varchar(1024)'')
FROM
(
    SELECT
            [ID],
            [ChannelName] = [Name],
            [CFG]
    FROM
            @Xml
) c
CROSS APPLY c.CFG.nodes(''/channel/destinationConnectors/connector'') d(c)

Answer 1

Puedo darte una respuesta y una suposición:

Primero uso una variable de tabla declarada para simular su escenario:

DECLARE @tbl TABLE(s NVARCHAR(MAX));
INSERT INTO @tbl VALUES
(N''<root>
    <SomeElement>This is first text of element1
        <InnerElement>This is text of inner element1</InnerElement>
        This is second text of element1
    </SomeElement>
    <SomeElement>This is first text of element2
        <InnerElement>This is text of inner element2</InnerElement>
        This is second text of element2
    </SomeElement>
</root>'')
,(N''<root>
    <SomeElement>This is first text of elementA
        <InnerElement>This is text of inner elementA</InnerElement>
        This is second text of elementA
    </SomeElement>
    <SomeElement>This is first text of elementB
        <InnerElement>This is text of inner elementB</InnerElement>
        This is second text of elementB
    </SomeElement>
</root>'');

- Esta consulta leerá el XML con una conversión de una sub-selección . En su lugar, puede usar un CTE , pero esto debería ser solo azúcar sintáctico ...

SELECT se.value(N''(.)[1]'',''nvarchar(max)'') SomeElementsContent
      ,se.value(N''(InnerElement)[1]'',''nvarchar(max)'') InnerElementsContent
      ,se.value(N''(./text())[1]'',''nvarchar(max)'') ElementsFirstText
      ,se.value(N''(./text())[2]'',''nvarchar(max)'') ElementsSecondText
FROM (SELECT CAST(s AS XML) FROM @tbl) AS tbl(TheXml)
CROSS APPLY TheXml.nodes(N''/root/SomeElement'') AS A(se);

- La segunda parte usa una tabla para escribir en el XML escrito y leer desde allí:

DECLARE @tbl2 TABLE(x XML)
INSERT INTO @tbl2
SELECT CAST(s AS XML) FROM @tbl;

SELECT se.value(N''(.)[1]'',''nvarchar(max)'') SomeElementsContent
      ,se.value(N''(InnerElement)[1]'',''nvarchar(max)'') InnerElementsContent
      ,se.value(N''(./text())[1]'',''nvarchar(max)'') ElementsFirstText
      ,se.value(N''(./text())[2]'',''nvarchar(max)'') ElementsSecondText
FROM @tbl2 t2
CROSS APPLY t2.x.nodes(N''/root/SomeElement'') AS A(se);

¿Por qué es /text() más rápido que sin /text() ?

Si miras mi ejemplo, el contenido de un elemento es todo, desde el nodo inicial hasta el nodo de cierre . El text() de un elemento es solo texto flotante entre dos elementos. Puede ver esto en los resultados de la selección anterior. El text() es una porción almacenada por separado en una estructura de árbol en realidad (lea la siguiente sección). Ir a buscarlo es una acción de un solo paso . De lo contrario, se debe analizar una estructura compleja para encontrar todo entre la etiqueta de apertura y su etiqueta de cierre correspondiente, incluso si no hay nada más que el text() .

¿Por qué debo almacenar XML en el tipo apropiado?

¡XML no es solo texto con algunos caracteres extra tontos! Es un documento con una estructura compleja. El XML no se almacena como el texto que ve . XML se almacena en una estructura de árbol. Siempre que convierta una cadena, que representa un XML, en un XML real, este trabajo muy costoso debe hacerse. Cuando se le presenta el XML (o cualquier otra salida), la cadena de representación se (re) construye desde cero.

¿Por qué el enfoque prefabricado es más rápido?

Esto es adivinar ...
En mi ejemplo, ambos enfoques son bastante iguales y conducen a (casi) el mismo plan de ejecución.
SQL Server no funcionará todo de la manera que podría esperar esto. ¡Este no es un sistema de procedimiento en el que declaras hacer esto, que hacer esto y después hacer esto! . Le dices al motor lo que quieres, y el motor decide cómo hacerlo mejor. ¡Y el motor es bastante bueno con esto!
Antes de que comience la ejecución, el motor intenta estimar los costos de los enfoques. CONVERT (o CAST ) es una operación bastante barata. Podría ser que el motor decida trabajar en la lista de sus llamadas y hacer el reparto para cada necesidad una y otra vez, porque cree que esto es más barato que la creación costosa de una tabla derivada ...