La Mejor manera de triturar datos XML en columnas de base de datos de SQL Server

En mi caso estoy ejecutando SQL 2005 SP2 (9.0).

Lo único que ayudó fue agregar la OPCIÓN ( OPTIMIZAR PARA ( @your_xml_var = NULL ) ). La explicación está en el siguiente enlace.

Ejemplo:

INSERT INTO @tbl (Tbl_ID, Name, Value, ParamData)
SELECT     1,
    tbl.cols.value('name[1]', 'nvarchar(255)'),
    tbl.cols.value('value[1]', 'nvarchar(255)'),
    tbl.cols.query('./paramdata[1]')
FROM @xml.nodes('//root') as tbl(cols) OPTION ( OPTIMIZE FOR ( @xml = NULL ) )

Https://connect.microsoft.com/SQLServer/feedback/details/562092/an-insert-statement-using-xml-nodes-is-very-very-very-slow-in-sql2008-sp1

No estoy seguro de cuál es el mejor método. Usé la construcción OPENXML:

INSERT INTO Test
SELECT Id, Data 
FROM OPENXML (@XmlDocument, '/Root/blah',2)
WITH (Id   int         '@ID',
      Data varchar(10) '@DATA')

Para acelerarlo, puede crear índices XML. Puede establecer el índice específicamente para valor optimización del rendimiento de la función. También puede utilizar columnas xml escritas, que funciona mejor.

Tuvimos un problema similar aquí. Nuestro DBA (SP, you the man) echó un vistazo a mi código, hizo un pequeño ajuste a la sintaxis, y obtuvimos la velocidad que habíamos estado esperando. Era inusual porque mi select from XML era bastante rápido, pero el insert era muy lento. Así que prueba esta sintaxis:

INSERT INTO some_table (column1, column2, column3)
    SELECT 
        Rows.n.value(N'(@column1/text())[1]', 'varchar(20)'), 
        Rows.n.value(N'(@column2/text())[1]', 'nvarchar(100)'), 
        Rows.n.value(N'(@column3/text())[1]', 'int')
    FROM @xml.nodes('//Rows') Rows(n) 

Así que especificar el parámetro text() realmente parece hacer una diferencia en el rendimiento. Tomó nuestro inserto de 2K filas de 'Debo haber escrito que mal-déjame detenerlo' a unos 3 segundos. Que fue 2 veces más rápido que las sentencias raw insert que habíamos estado ejecutando a través de la conexión.

No diría que esta es la "mejor" solución, pero he escrito un procedimiento CLR SQL genérico para este propósito exacto: toma una estructura Xml "tabular" (como la devuelta por FOR XML RAW) y genera un conjunto de resultados.

No requiere ninguna personalización / conocimiento de la estructura de la "tabla" en el Xml, y resulta ser extremadamente rápido / eficiente (aunque esto no era un objetivo de diseño). Acabo de triturar una variable xml de 25 MB (sin escribir) en menos de 20 segundos, devolviendo 25.000 filas de una mesa bastante ancha.

Espero que esto ayude a alguien: http://architectshack.com/ClrXmlShredder.ashx

Esto no es una respuesta, más bien una adición a esta pregunta - acabo de encontrar el mismo problema y puedo dar cifras como edg pide en el comentario.

Mi prueba tiene xml que resulta en 244 registros que se insertan - por lo que 244 nodos.

El código que estoy reescribiendo tarda en promedio 0.4 segundos en ejecutarse.(10 pruebas de ejecución, propagación de .56 segundos para .344 segs) El rendimiento no es la razón principal por la que se está reescribiendo el código, pero el nuevo código necesita tener un rendimiento igual o mejor. Este código antiguo recorre los nodos xml, llamando a un sp para insertar una vez por bucle

El nuevo código es más o menos un solo sp; pasa el xml; tritúralo.

Las pruebas con el nuevo código activado muestran que el nuevo sp tarda en promedio 3,7 segundos, casi 10 veces más lento.

Mi consulta está en la forma publicada en esta pregunta;

INSERT INTO some_table (column1, column2, column3)
SELECT
Rows.n.value('(@column1)[1]', 'varchar(20)'),
Rows.n.value('(@column2)[1]', 'nvarchar(100)'),
Rows.n.value('(@column3)[1]', 'int'),
FROM @xml.nodes('//Rows') Rows(n)

El plan de ejecución parece mostrar que para cada columna, sql server está haciendo una "Función de valor de tabla [XmlReader]" separada que devuelve las 244 filas, unir todas las copias de seguridad con Bucles Anidados (Unión interna). Así que en mi caso donde estoy triturando / insertando en aproximadamente 30 columnas, esto parece suceder por separado 30 veces.

Voy a tener que volcar este código, no creo que ninguna optimización vaya a superar que este método sea inherentemente lento. Voy a probar el método sp_xml_preparedocument/OPENXML y ver si el rendimiento es mejor para eso. Si alguien se encuentra con esta pregunta de una búsqueda en la web (como lo hice) Yo altamente le recomendamos que haga algunas pruebas de rendimiento antes de usar este tipo de trituración en SQL Server

Hay un XML Bulk load objeto COM (. NET Example)

De MSDN :

Puede insertar datos XML en un SQL Base de datos del servidor mediante el uso de una INSERCIÓN sentencia y la función OPENXML; sin embargo, la utilidad de carga a granel proporciona un mejor rendimiento cuando usted necesidad de insertar grandes cantidades de XML datos.

Mi solución actual para conjuntos XML grandes (> 500 nodos) es usar SQL Bulk Copy (System.Datos.SqlClient.SqlBulkCopy) mediante el uso de un conjunto de datos para cargar el XML en la memoria y luego pasar la tabla a SqlBulkCopy (definir un esquema XML ayuda).

Obviamente hay una trampa como usar innecesariamente un conjunto de datos y cargar primero todo el documento en la memoria. Me gustaría ir más lejos en el futuro e implementar mi propio IDataReader para evitar el método de conjunto de datos, pero actualmente el conjunto de datos es " bueno suficiente" para el trabajo.

Básicamente nunca encontré una solución a mi pregunta original sobre el rendimiento lento para ese tipo de trituración XML. Podría ser lento debido a que las consultas xml escritas son inherentemente lentas o algo que tenga que ver con las transacciones y el registro de SQL Server. Supongo que las funciones xml escritas nunca fueron diseñadas para operar en tamaños de nodo no triviales.

Carga masiva XML: Probé esto y fue rápido, pero tuve problemas para que el dll COM funcionara bajo entornos de 64 bits y generalmente trato de evitar COM dlls que ya no parecen ser compatibles.

Sp_xml_preparedocument/OPENXML: Nunca fui por este camino, así que estaría interesado en ver cómo funciona.