Optimización del performance de la consulta mediante XML.Modify () en SQL Server 2008

Estoy generando un XML usando FOR XML en SQL Server 2008.

La salida XML generada es de 5.18 MB y el process hasta la generación de XML toma solo 5 segundos.

Ahora, agrego attributes en este XML usando XML.modify. Después de la modificación, el tamaño aumenta a 5.25 MB. Pero el file tarda 17 minutos para este procesamiento restante (agregando los attributes).

Mantengo los datos XML en una variable de tipo XML, hay alnetworkingedor de 6000 filas en este documento XML y estoy agregando los attributes en aproximadamente la mitad de las filas. Estos attributes se agregan bajo diferentes nodos con alguna condición. El time máximo en 17 minutos aproximadamente (16 minutos) se consume al agregar estos attributes.

Lo solicitaría, si alguien me puede decir las mejores prácticas y forms de mejorar el performance.

XML generado antes de agregar los styles

<?xml version="1.0"?> <?mso-application progid="Excel.Sheet"?> <Workbook xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns="urn:schemas-microsoft-com:office:spreadsheet"> <Worksheet xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns="urn:schemas-microsoft-com:office:spreadsheet" ss:Name="INDEX"> <Table xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns="urn:schemas-microsoft-com:office:spreadsheet"> <Column></Column> <Row xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns="urn:schemas-microsoft-com:office:spreadsheet"> <Cell xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns="urn:schemas-microsoft-com:office:spreadsheet"> <Data ss:Type="String">#</Data> </Cell> <Cell xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns="urn:schemas-microsoft-com:office:spreadsheet"> <Data ss:Type="String">Study Name</Data> </Cell> 

XML después de agregar attributes

  <?xml version="1.0"?> <?mso-application progid="Excel.Sheet"?> <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"> <Styles xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"> <Worksheet xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ss:Name="INDEX"> <Table xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ss:DefaultRowHeight="50"> <Row xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ss:StyleID="s66"> <Cell xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ss:MergeAcross="5"> <Data ss:Type="String">#</Data> </Cell> <Cell xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"> <Data ss:Type="String">Study Name</Data> </Cell> 

Código para generar XML básico

 CREATE TABLE TempData ( [ID] [INT] NOT NULL IDENTITY(1, 1) PRIMARY KEY ,[Col1] [NVARCHAR](MAX) ,[Col2] [NVARCHAR](MAX) ,[Col3] [NVARCHAR](MAX) ,[WORKSHEET_ID] INT ,[FORM_OID] [NVARCHAR](MAX) ,[Col4] [NVARCHAR](MAX) ,[Col5] [NVARCHAR](MAX) ,[Col6] [NVARCHAR](MAX) ,[Col7] [NVARCHAR](MAX) ,[Col8] [NVARCHAR](MAX) ,[Col9] [NVARCHAR](MAX) ,[RANKING] INT ); SELECT @xml = (SELECT ( SELECT CASE WHEN a2.[FORM_OID]='INDEX' THEN a2.[FORM_OID] ELSE SUBSTRING(CONVERT(varchar(10),(a2.[WORKSHEET_ID]-1))+'_'+ a2.[FORM_OID],0,31) END '@ss:Name',( SELECT '' as [Column],( SELECT (SELECT 'String' as [Data/@ss:Type], [Col1] as [Data] FOR XML PATH('Cell'), TYPE), (SELECT 'String' as [Data/@ss:Type], [Col2] as [Data] FOR XML PATH('Cell'), TYPE), (SELECT 'String' as [Data/@ss:Type], [Col3] as [Data] FOR XML PATH('Cell'), TYPE), (SELECT 'String' as [Data/@ss:Type], [Col4] as [Data] FOR XML PATH('Cell'), TYPE), (SELECT 'String' as [Data/@ss:Type], [Col5] as [Data] FOR XML PATH('Cell'), TYPE), (SELECT 'String' as [Data/@ss:Type], [Col6] as [Data] FOR XML PATH('Cell'), TYPE), (SELECT 'String' as [Data/@ss:Type], [Col7] as [Data] FOR XML PATH('Cell'), TYPE), (SELECT 'String' as [Data/@ss:Type], [Col8] as [Data] FOR XML PATH('Cell'), TYPE), (SELECT 'String' as [Data/@ss:Type], [Col9] as [Data] FOR XML PATH('Cell'), TYPE), (SELECT 'String' as [Data/@ss:Type], [RANKING] as [Data] FOR XML PATH('Cell'), TYPE) FROM TempData a1 WHERE a1.[WORKSHEET_ID]=a2.[WORKSHEET_ID] GROUP BY [ID],[Col1],[Col2],[Col3],[FORM_OID],[Col4],[Col5],[Col6],[Col7],[Col8],[Col9],[RANKING] --form oid order by [ID] FOR XML PATH('Row'), TYPE ) FOR XML PATH('Table'), TYPE ) FROM TempData a2 group by [WORKSHEET_ID],[FORM_OID] FOR XML PATH('Worksheet'), TYPE) FOR XML PATH('Workbook'), TYPE ) 

Código para agregar estilo (uno de los muchos attributes que estoy agregando)

 SET @xml.modify('declare default element namespace "urn:schemas-microsoft-com:office:spreadsheet"; declare namespace ss="urn:schemas-microsoft-com:office:spreadsheet" ; declare namespace x="urn:schemas-microsoft-com:office:excel"; insert attribute ss:MergeAcross{"5"} into (/Workbook/Worksheet[position()=sql:variable("@worksheets")][1]/Table/Row[1]/Cell[1])[1]') 

Esta consulta modificada networkingucirá al less drásticamente el número de bytes al evitar tantas declaraciones de espacio de nombres repetidas. Si no puede seguir con mi sugerencia (lea a continuación), esto debería acelerar las cosas de todos modos. 3000 RBAR modificar llamadas debería ser más rápido con aproximadamente 10% del tamaño …

Espero que pueda usar esto para su consulta completa:

 CREATE TABLE TempData ( [ID] [INT] NOT NULL IDENTITY(1, 1) PRIMARY KEY ,[Col1] [NVARCHAR](MAX) ,[Col2] [NVARCHAR](MAX) ,[Col3] [NVARCHAR](MAX) ,[WORKSHEET_ID] INT ,[FORM_OID] [NVARCHAR](MAX) ,[Col4] [NVARCHAR](MAX) ,[Col5] [NVARCHAR](MAX) ,[Col6] [NVARCHAR](MAX) ,[Col7] [NVARCHAR](MAX) ,[Col8] [NVARCHAR](MAX) ,[Col9] [NVARCHAR](MAX) ,[RANKING] INT ); INSERT INTO TempData(Col1,Col2,Col3,WORKSHEET_ID,FORM_OID,Col4,Col5,Col6,Col7,Col8,Col9,RANKING) VALUES('test1','test2','test3',1,'formOID','test4','test5','test6','test7','test8','test9',100); DECLARE @xml XML; WITH XMLNAMESPACES('urn:schemas-microsoft-com:office:spreadsheet' AS ss ,'urn:schemas-microsoft-com:office:excel' AS x ,DEFAULT 'urn:schemas-microsoft-com:office:spreadsheet') SELECT @xml = ( SELECT CASE WHEN a2.[FORM_OID]='INDEX' THEN a2.[FORM_OID] ELSE SUBSTRING(CONVERT(varchar(10),(a2.[WORKSHEET_ID]-1))+'_'+ a2.[FORM_OID],0,31) END '@ss:Name', ( SELECT '' as [Column], ( SELECT 'String' as [Cell/Data/@ss:Type], [Col1] as [Cell/Data], '', 'String' as [Cell/Data/@ss:Type], [Col2] as [Cell/Data], '', 'String' as [Cell/Data/@ss:Type], [Col3] as [Cell/Data], '', 5 AS [Cell/@ss:MergeAcross], --Read below! 'String' as [Cell/Data/@ss:Type], [Col4] as [Cell/Data], '', 'String' as [Cell/Data/@ss:Type], [Col5] as [Cell/Data], '', 'String' as [Cell/Data/@ss:Type], [Col7] as [Cell/Data], '', 'String' as [Cell/Data/@ss:Type], [Col8] as [Cell/Data], '', 'String' as [Cell/Data/@ss:Type], [Col9] as [Cell/Data], '', 'String' as [Cell/Data/@ss:Type], [RANKING] as [Cell/Data] FROM TempData a1 WHERE a1.[WORKSHEET_ID]=a2.[WORKSHEET_ID] GROUP BY [ID],[Col1],[Col2],[Col3],[FORM_OID],[Col4],[Col5],[Col6],[Col7],[Col8],[Col9],[RANKING] --form oid order by [ID] FOR XML PATH('Row'), TYPE ) FOR XML PATH('Table'), TYPE ) FROM TempData a2 group by [WORKSHEET_ID],[FORM_OID] FOR XML PATH('Worksheet'), ROOT('Workbook') ); SELECT @xml; --CleanUp --DROP TABLE TempData; 

Si observa detenidamente el "Col4" verá que introduje el MergeAcross-Attribut directamente en la llamada. ¿Qué piensas sobre agregar más columnas a tu tabla temporal o definir un segundo temptable para los styles si son 1: n y agregarlos "sobre la marcha"?