type - La mejor forma de destruir datos XML en columnas de la base de datos de SQL Server
xml in sql server 2014 (8)
¿Cuál es la mejor manera de triturar datos XML en varias columnas de bases de datos? Hasta ahora, he usado principalmente los nodos y las funciones de valor de esta manera:
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)
Sin embargo, me parece que esto se está volviendo muy lento incluso para datos xml de tamaño moderado.
Esta no es una respuesta, más una adición a esta pregunta: acabo de encontrar el mismo problema y puedo dar cifras como lo pide EDG en el comentario.
Mi prueba tiene xml, lo que da como resultado la inserción de 244 registros, por lo que 244 nodos.
El código que estoy reescribiendo tarda en promedio 0,4 segundos en ejecutarse (se ejecutan 10 pruebas, se extienden de .56 segundos a .344 segundos). El rendimiento no es la razón principal por la que se está reescribiendo el código, pero el nuevo código debe funcionar también. o mejor. Este viejo código enlaza los nodos xml, llamando a sp para insertar una vez por ciclo
El nuevo código es más o menos una sola sp; pasar el xml adentro; tritúralo.
Las pruebas con el nuevo código activado muestran que la nueva sp tarda en promedio 3.7 segundos, casi 10 veces más lenta.
Mi consulta está en el formulario publicado 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, el servidor sql está haciendo una "Función de valor de tabla [XMLReader]" por separado, devolviendo las 244 filas, uniendo todas las copias de seguridad con bucles anidados (unión interna). Entonces, en mi caso, donde estoy triturando / insertando en alrededor de 30 columnas, esto parece suceder por separado 30 veces.
Voy a tener que volcar este código, no creo que ninguna optimización supere este método siendo 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 desde una búsqueda web (como yo lo hice), le recomendaría que realice algunas pruebas de rendimiento antes de usar este tipo de trituración en SQL Server.
Hay un objeto COM de carga masiva XML ( Ejemplo .NET )
Desde MSDN :
Puede insertar datos XML en una base de datos de SQL Server utilizando una instrucción INSERT y la función OPENXML; sin embargo, la utilidad Bulk Load proporciona un mejor rendimiento cuando necesita insertar grandes cantidades de datos XML.
Mi solución actual para grandes conjuntos de XML (> 500 nodos) es usar SQL Bulk Copy (System.Data.SqlClient.SqlBulkCopy) usando un DataSet para cargar el XML en la memoria y luego pasar la tabla a SqlBulkCopy (definir un esquema XML ayuda )
Obviamente hay una trampa como el uso innecesario de un DataSet y la carga de todo el documento en la memoria primero. Me gustaría ir más lejos en el futuro e implementar mi propio IDataReader para eludir el método DataSet, pero actualmente el DataSet es "lo suficientemente bueno" para el trabajo.
Básicamente, nunca encontré una solución a mi pregunta original sobre el bajo rendimiento para ese tipo de trituración de XML. Podría ser lento debido a que las consultas xml mecanografiadas son inherentemente lentas o tienen algo que ver con las transacciones y el registro de SQL Server. Supongo que las funciones xml mecanografiadas nunca se diseñaron para operar en tamaños de nodo no triviales.
Carga masiva de XML: Intenté esto y fue rápido, pero tuve problemas para hacer funcionar el dll COM en entornos de 64 bits y generalmente trato de evitar los dll de COM que ya no parecen ser compatibles.
sp_xml_preparedocument / OPENXML: Nunca he pasado por este camino, así que estaría interesado en ver cómo funciona.
No afirmaría que esta es la "mejor" solución, pero he escrito un procedimiento SQL CLR 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 extremadamente rápido / eficiente (aunque este no era un objetivo de diseño). Acabo de triturar una variable xml de 25MB (sin tipo) en menos de 20 segundos, devolviendo 25,000 filas de una tabla bastante ancha.
Espero que esto ayude a alguien: http://architectshack.com/ClrXmlShredder.ashx
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 la optimización del rendimiento de la función de valor . También puede usar columnas xml tipeadas, que rinden mejor.
Tropezando con esta pregunta mientras tenía un problema muy similar, había estado ejecutando una consulta procesando un archivo XML de 7.5MB (~ aproximadamente 10,000 nodos) durante alrededor de 3.5 ~ 4 horas antes de finalmente darme por vencido.
Sin embargo, después de investigar un poco más, descubrí que al haber tipeado el XML usando un esquema y había creado un Índice XML (lo hubiera insertado a granel en una tabla), la misma consulta se completó en ~ 0.04ms.
¿Cómo es eso para una mejora en el rendimiento?
Código para crear un esquema:
IF EXISTS ( SELECT * FROM sys.xml_schema_collections where [name] = ''MyXmlSchema'')
DROP XML SCHEMA COLLECTION [MyXmlSchema]
GO
DECLARE @MySchema XML
SET @MySchema =
(
SELECT * FROM OPENROWSET
(
BULK ''C:/Path/To/Schema/MySchema.xsd'', SINGLE_CLOB
) AS xmlData
)
CREATE XML SCHEMA COLLECTION [MyXmlSchema] AS @MySchema
GO
Código para crear la tabla con una columna XML escrita:
CREATE TABLE [dbo].[XmlFiles] (
[Id] [uniqueidentifier] NOT NULL,
-- Data from CV element
[Data] xml(CONTENT dbo.[MyXmlSchema]) NOT NULL,
CONSTRAINT [PK_XmlFiles] PRIMARY KEY NONCLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Código para crear índice
CREATE PRIMARY XML INDEX PXML_Data
ON [dbo].[XmlFiles] (Data)
Sin embargo, hay algunas cosas a tener en cuenta. La implementación de SQL Server de Schema no es compatible con xsd: include. Esto significa que si tiene un esquema que hace referencia a otro esquema, deberá copiarlos en un único esquema y agregarlos.
También obtendría un error:
XQuery [dbo.XmlFiles.Data.value()]: Cannot implicitly atomize or apply ''fn:data()'' to complex content elements, found type ''xs:anyType'' within inferred type ''element({http://www.mynamespace.fake/schemas}:SequenceNumber,xs:anyType) ?''.
si traté de navegar por encima del nodo que había seleccionado con la función de nodos. P.ej
SELECT
,C.value(''CVElementId[1]'', ''INT'') AS [CVElementId]
,C.value(''../SequenceNumber[1]'', ''INT'') AS [Level]
FROM
[dbo].[XmlFiles]
CROSS APPLY
[Data].nodes(''/CVSet/Level/CVElement'') AS T(C)
Se encontró que la mejor manera de manejar esto era usar la APLICACIÓN EXTERNA para realizar en efecto una "combinación externa" en el XML.
SELECT
,C.value(''CVElementId[1]'', ''INT'') AS [CVElementId]
,B.value(''SequenceNumber[1]'', ''INT'') AS [Level]
FROM
[dbo].[XmlFiles]
CROSS APPLY
[Data].nodes(''/CVSet/Level'') AS T(B)
OUTER APPLY
B.nodes (''CVElement'') AS S(C)
Espero que eso ayude a alguien ya que ese ha sido mi día.
Tuvimos un problema similar aquí. Nuestro DBA (SP, usted el hombre) echó un vistazo a mi código, hizo un pequeño ajuste a la sintaxis y obtuvimos la velocidad que esperábamos. Era inusual porque mi selección de XML era bastante rápida, pero el inserto era muy lento. Intente esta sintaxis en su lugar:
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ó nuestra inserción de 2K filas de ''Debo haber escrito eso mal - déjame detenerlo'' a unos 3 segundos. Que fue 2 veces más rápido que las declaraciones de inserción sin formato que habíamos estado ejecutando a través de la conexión.
en mi caso estoy ejecutando SQL 2005 SP2 (9.0).
Lo único que ayudó fue agregar OPTION (OPTIMIZE FOR (@your_xml_var = NULL)). La explicación está en el enlace a continuación.
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 ) )