Server 2008中使用XML.Modify()查询的性能优化

Question

我正在Server 2008中生成用于XML的XML。

生成的XML输出为5.18MB，生成XML的过程仅需5秒。

现在，我使用XML.modify在这个XML中添加属性。修改后，尺寸增加到5.25MB。但是文件需要17分钟才能完成剩下的处理(添加属性)。

我将XML数据保存在XML类型变量中，这个XML文档中大约有6000行，我将在大约一半的行中添加属性。这些属性是在不同的节点上添加的，具有一定的条件。添加这些属性时消耗的最大时间为17分钟(约16分钟)。

如果有人能告诉我提高性能的最佳做法和方法，我会提出要求。

添加样式之前生成的

        <?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

        <?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>

代码以生成基本的XML

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 )

代码添加样式(我要添加的许多属性之一)

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]')

Answer 1

通过避免重复的名称空间声明，此修改后的查询至少将大大减少字节数。如果你不能接受我的建议(请阅读下面的文章)，这将加速事情的发展。3000 RBAR修改调用应该更快，大约10%的大小..。

我希望您可以将此用于完整的查询：

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;

如果你仔细看一下"Col4“，你会发现，我在调用中引入了MergeAcros-attri但是。在临时表中添加更多列，或者为样式定义第二个温度表(如果它们为1:n并“动态”添加它们)，您会怎么想？