SQL Server 2008 中使用 XML.Modify() 进行查询的性能优化 [英] Performance Optimization of query using XML.Modify() in SQL Server 2008
问题描述
我在 SQL Server 2008 中使用 FOR XML 生成 XML.
I am generating an XML using FOR XML in SQL Server 2008.
生成的 XML 输出为 5.18 MB，生成 XML 的过程仅需 5 秒.
The XML output generated is of 5.18 MB and the process up to generation of XML takes just 5 seconds.
现在，我使用 XML.modify 在这个 XML 中添加属性.修改后大小增加到 5.25 MB.但是该文件需要 17 分钟进行剩余的处理(添加属性).
Now, I am adding attributes in this XML using XML.modify. After modification the size increases to 5.25 MB. But the file takes 17 minutes for this remaining processing (adding the attributes).
我将 XML 数据保存在 XML 类型变量中，此 XML 文档中大约有 6000 行，我将在大约一半的行中添加属性.这些属性是在不同的节点下添加的，具有一定的条件.添加这些属性时会消耗大约 17 分钟(约 16 分钟)的最长时间.
I am keeping the XML data in an XML type variable,there are around 6000 rows in this XML document and i am adding the attributes in approx half of the rows. These attributes are added under different node with some condition. The maximum time in 17 minutes approx(16 minutes) are consumed while adding these attributes.
如果有人可以告诉我提高性能的最佳实践和方法，我会请求.
I would request, if some one can tell me the best practices and ways to improving the performance.
在添加样式之前生成的 XML
<?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 )
添加样式的代码(我添加的众多属性之一)
Code to add Style (One of the many attributes i am adding)
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]')
推荐答案
这个修改后的查询至少会通过避免如此多的重复命名空间声明来大幅减少字节数.如果你不能接受我的建议(阅读下文)，无论如何这应该会加快速度.3000 次 RBAR 修改调用应该会更快，大约 10% 的大小......
This modified query will at least reduce the number of bytes drastically by avoiding so many repeated namespace declarations. If you cannot go with my suggestion (read below), this should speed up things anyway. 3000 RBAR modify calls should be faster with about 10% of the size...
我希望您可以将其用于完整查询:
I hope you can use this for your full query:
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"，您会发现我在调用中直接引入了 MergeAcross-Attribut.如果样式是 1:n 并即时"添加它们，您如何看待向临时表添加更多列或为样式定义第二个临时表?
If you look closely to the "Col4" you'll see, that I introduced the MergeAcross-Attribut directly in the call. What do you think about adding more columns to your temp table or define a second temptable for styles if they are 1:n and add them "on the fly"?
这篇关于SQL Server 2008 中使用 XML.Modify() 进行查询的性能优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
