XML Server XML optimización del rendimiento

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, estoy seleccionando valores en los elementos xml como un único conjunto de resultados. El rendimiento es bastante pobre. He intentado con dos consultas diferentes. La primera toma aproximadamente 22 segundos para ejecutarse y la segunda tarda 7.

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

Entonces, leí un rumor en línea que si convierte los datos de NVARCHAR a un XML usando una tabla temporal o una variable de tabla, obtendrá una ganancia de rendimiento, que al menos en mi caso era cierta … 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] = dcvalue('name[1]','varchar(100)'), [Type] = dcvalue('transportName[1]','varchar(100)'), [Enabled] = dcvalue('enabled[1]','BIT'), [Queued] = dcvalue('properties[1]/destinationConnectorProperties[1]/queueEnabled[1]','varchar(100)'), [RetryInterval] = dcvalue('properties[1]/destinationConnectorProperties[1]/retryIntervalMillis[1]','INT'), [MaxRetries] = dcvalue('properties[1]/destinationConnectorProperties[1]/retryCount[1]','INT'), [RotateQueue] = dcvalue('properties[1]/destinationConnectorProperties[1]/rotate[1]','BIT'), [ThreadCount] = dcvalue('properties[1]/destinationConnectorProperties[1]/threadCount[1]','INT'), [WaitForPrevious] = dcvalue('waitForPrevious[1]','BIT'), [Destination] = COALESCE( dcvalue('properties[1]/channelId[1]','varchar(100)'), dcvalue('properties[1]/remoteAddress[1]','varchar(100)'), dcvalue('properties[1]/wsdlUrl[1]','varchar(1024)')), [DestinationPort] = COALESCE( dcvalue('properties[1]/remotePort[1]','varchar(100)'), dcvalue('properties[1]/port[1]','varchar(1024)')), [Service] = dcvalue('properties[1]/service[1]','varchar(1024)'), [Operation] = dcvalue('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] = dcvalue('(name/text())[1]','varchar(100)'), [Type] = dcvalue('(transportName/text())[1]','varchar(100)'), [Enabled] = dcvalue('(enabled/text())[1]','BIT'), [Queued] = dcvalue('(properties/destinationConnectorProperties/queueEnabled/text())[1]','varchar(100)'), [RetryInterval] = dcvalue('(properties/destinationConnectorProperties/retryIntervalMillis/text())[1]','INT'), [MaxRetries] = dcvalue('(properties/destinationConnectorProperties/retryCount/text())[1]','INT'), [RotateQueue] = dcvalue('(properties/destinationConnectorProperties/rotate/text())[1]','BIT'), [ThreadCount] = dcvalue('(properties/destinationConnectorProperties/threadCount/text())[1]','INT'), [WaitForPrevious] = dcvalue('(waitForPrevious/text())[1]','BIT'), [Destination] = COALESCE( dcvalue('(properties/channelId/text())[1]','varchar(100)'), dcvalue('(properties/remoteAddress/text())[1]','varchar(100)'), dcvalue('(properties/wsdlUrl/text())[1]','varchar(1024)')), [DestinationPort] = COALESCE( dcvalue('(properties/remotePort/text())[1]','varchar(100)'), dcvalue('(properties/port/text())[1]','varchar(1024)')), [Service] = dcvalue('(properties/service/text())[1]','varchar(1024)'), [Operation] = dcvalue('(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 de texto (), pero primero convierte las conversiones de la columna NVARCHAR a 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] = dcvalue('(name/text())[1]','varchar(100)'), [Type] = dcvalue('(transportName/text())[1]','varchar(100)'), [Enabled] = dcvalue('(enabled/text())[1]','BIT'), [Queued] = dcvalue('(properties/destinationConnectorProperties/queueEnabled/text())[1]','varchar(100)'), [RetryInterval] = dcvalue('(properties/destinationConnectorProperties/retryIntervalMillis/text())[1]','INT'), [MaxRetries] = dcvalue('(properties/destinationConnectorProperties/retryCount/text())[1]','INT'), [RotateQueue] = dcvalue('(properties/destinationConnectorProperties/rotate/text())[1]','BIT'), [ThreadCount] = dcvalue('(properties/destinationConnectorProperties/threadCount/text())[1]','INT'), [WaitForPrevious] = dcvalue('(waitForPrevious/text())[1]','BIT'), [Destination] = COALESCE( dcvalue('(properties/channelId/text())[1]','varchar(100)'), dcvalue('(properties/remoteAddress/text())[1]','varchar(100)'), dcvalue('(properties/wsdlUrl/text())[1]','varchar(1024)')), [DestinationPort] = COALESCE( dcvalue('(properties/remotePort/text())[1]','varchar(100)'), dcvalue('(properties/port/text())[1]','varchar(1024)')), [Service] = dcvalue('(properties/service/text())[1]','varchar(1024)'), [Operation] = dcvalue('(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) 

Puedo darte una respuesta y una suposición:

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

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

– Esta consulta leerá el XML con un lanzamiento fuera de una selección secundaria . Puede usar un CTE lugar, 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é /text() más rápido que sin /text() ?

Si miras mi ejemplo, el contenido de un elemento es todo desde el nodo de inicio hasta el nodo de cierre . El text() de un elemento es simplemente texto flotante entre dos elementos. Puedes 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). Para recuperarlo 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 correspondiente etiqueta de cierre, incluso si no hay nada más que el text() .

¿Por qué debería almacenar XML en el tipo apropiado?

¡XML no es solo texto con algunos personajes extravagantes! Es un documento con una estructura compleja. El XML no se almacena como el texto que ves . XML se almacena en una estructura de árbol. Siempre que arroje una cadena, que representa un XML, en un XML real, este trabajo muy caro debe hacerse. Cuando se le presenta el XML (o cualquier otro resultado), la cadena representativa se (re) crea desde cero.

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

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 usted podría esperar esto. ¡Este no es un sistema de procedimientos donde usted declara hacer esto, que hacer esto y luego hacer esto! . Usted le dice al motor lo que quiere, 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 piensa que esto es más barato que la costosa creación de una tabla derivada …