英文:
XQuery On SQL Server - Performance Issues
问题
在SQL Server中使用XQuery工作。我理解使用CROSS/OUTER APPLY来处理节点应该有助于提高性能，然而上面的查询（如下）性能明显比下面的差。有人能解释为什么会这样，并且也许提供一些关于XQuery性能的指导吗？我已经搜索了我能找到的所有相关问题，但似乎没有直接相关的内容。
性能较差
SELECT x.ApplicationId
, t.value('(reportId/text())[1]','varchar(100)') AS ReportId
, t.value('(reportType/text())[1]','varchar(100)') AS ReportType
, t.value('(tracking-number/text())[1]','varchar(50)') AS TrackingNumber
, n.value('(firstName/text())[1]','varchar(100)') AS FirstName
, n.value('(middleName/text())[1]','varchar(100)') AS MiddleInitial
, n.value('(lastName/text())[1]','varchar(100)') AS LastName
, ssn.value('(ssn/text())[1]','varchar(50)') AS SSN
, dob.value('(dob/text())[1]','varchar(30)') AS DateOfBirth
FROM #xml x
CROSS APPLY x.xmlResponse.nodes('/xml-response') t1(t)
OUTER APPLY t1.t.nodes('personPii/applicantInformation') t2(ai)
OUTER APPLY t2.ai.nodes('name') t3(n)
OUTER APPLY t2.ai.nodes('ssn') t4(ssn)
OUTER APPLY t2.ai.nodes('dob') t5(dob)
性能较好 - 为什么？
SELECT x.ApplicationId
, x.XMLResponse.value('(/xml-response/reportId)[1]','varchar(100)') AS ReportId
, x.XMLResponse.value('(/xml-response/reportType)[1]','varchar(100)') AS ReportType
, x.XMLResponse.value('(/xml-response/tracking-number)[1]','varchar(50)') AS TrackingNumber
, x.XMLResponse.value('(/xml-response/personPii/applicantInformation/firstName)[1]','varchar(100)') AS FirstName
, x.XMLResponse.value('(/xml-response/personPii/applicantInformation/middleName)[1]','varchar(100)') AS MiddleInitial
, x.XMLResponse.value('(/xml-response/personPii/applicantInformation/lastName)[1]','varchar(100)') AS LastName
, x.XMLResponse.value('(/xml-response/personPii/applicantInformation/ssn/ssn)[1]','varchar(50)') AS SSN
, x.XMLResponse.value('(/xml-response/personPii/applicantInformation/dob/dob)[1]','varchar(30)') AS DateOfBirth
FROM #xml x
英文:
Working on XQuery in SQL Server. My understanding was that using CROSS/OUTER APPLY for the nodes would help improve performance, however the top query (below) performance SIGNIFICANTLY worse than the lower. Can anyone help explain why that is, and perhaps any pointers on XQuery performance? I've searched all of the questions I can find but nothing seems directly on point.
Poor Performer
SELECT x.ApplicationId
, t.value('(reportId/text())[1]','varchar(100)') AS ReportId
, t.value('(reportType/text())[1]','varchar(100)') AS ReportType
, t.value('(tracking-number/text())[1]','varchar(50)') AS TrackingNumber
, n.value('(firstName/text())[1]','varchar(100)') AS FirstName
, n.value('(middleName/text())[1]','varchar(100)') AS MiddleInitial
, n.value('(lastName/text())[1]','varchar(100)') AS LastName
, ssn.value('(ssn/text())[1]','varchar(50)') AS SSN
, dob.value('(dob/text())[1]','varchar(30)') AS DateOfBirth
FROM #xml x
CROSS APPLY x.xmlResponse.nodes('/xml-response') t1(t)
OUTER APPLY t1.t.nodes('personPii/applicantInformation') t2(ai)
OUTER APPLY t2.ai.nodes('name') t3(n)
OUTER APPLY t2.ai.nodes('ssn') t4(ssn)
OUTER APPLY t2.ai.nodes('dob') t5(dob)
Better Performer -why?
SELECT x.ApplicationId
, x.XMLResponse.value('(/xml-response/reportId)[1]','varchar(100)') AS ReportId
, x.XMLResponse.value('(/xml-response/reportType)[1]','varchar(100)') AS ReportType
, x.XMLResponse.value('(/xml-response/tracking-number)[1]','varchar(50)') AS TrackingNumber
, x.XMLResponse.value('(/xml-response/personPii/applicantInformation/firstName)[1]','varchar(100)') AS FirstName
, x.XMLResponse.value('(/xml-response/personPii/applicantInformation/middleName)[1]','varchar(100)') AS MiddleInitial
, x.XMLResponse.value('(/xml-response/personPii/applicantInformation/lastName)[1]','varchar(100)') AS LastName
, x.XMLResponse.value('(/xml-response/personPii/applicantInformation/ssn/ssn)[1]','varchar(50)') AS SSN
, x.XMLResponse.value('(/xml-response/personPii/applicantInformation/dob/dob)[1]','varchar(30)') AS DateOfBirth
FROM #xml x
答案1
得分: 0
在这里，您可以看到节点（nodes）的工作方式与简单的XML之间的区别。第一个版本无法简单地获取多个字段值。
英文:
Adding an example of nodes vs simple xml:
create table #data (xml xml)
insert into #data
select '<root><data><field>X</field><field>Y</field></data></root>'
select xml.value('root[1]/data[1]/field[1]', 'nvarchar(max)')
from #data d
select n.value('(text())[1]', 'nvarchar(max)')
from #data d
cross apply xml.nodes('root/data/field') t(n)
Here, you can see the difference how nodes work. The first version cannot simply fetch more than one field value
答案2
得分: 0
以下是翻译好的部分：
这是另一个示例供您参考。
值得注意的要点：
- XML 本质上是分层的，因此
CROSS APPLY模拟了州和它们的城市之间的一对多关系。 - 出于性能原因，最好始终对非类型化的 XML 元素使用
.../text()。
SQL
DECLARE @xml XML =
N'<root>
<state>
<StateName>Florida</StateName>
<Abbr>FL</Abbr>
<Capital>Tallahassee</Capital>
<cities>
<city>
<city>Miami</city>
<population>470194</population>
</city>
<city>
<city>Orlando</city>
<population>285713</population>
</city>
</cities>
</state>
<state>
<StateName>Texas</StateName>
<Abbr>TX</Abbr>
<Capital>Austin</Capital>
<cities>
<city>
<city>Houston</city>
<population>2100263</population>
</city>
<city>
<city>Dallas</city>
<population>5560892</population>
</city>
</cities>
</state>
</root>';
SELECT state.value('(StateName/text())[1]', 'VARCHAR(20)') AS 状态
, state.value('(Abbr/text())[1]', 'VARCHAR(20)') AS 缩写
, state.value('(Capital/text())[1]', 'VARCHAR(20)') AS 首都
, city.value('(city/text())[1]', 'VARCHAR(20)') AS 城市
, city.value('(population/text())[1]', 'INT') AS 人口
FROM @xml.nodes('/root/state') AS t1(state)
CROSS APPLY state.nodes('cities/city') AS t2(city);
**输出**
| 状态 | 缩写 | 首都 | 城市 | 人口 |
|--------|------|-------------|---------|------------|
| Florida | FL | Tallahassee | Miami | 470194 |
| Florida | FL | Tallahassee | Orlando | 285713 |
| Texas | TX | Austin | Houston | 2100263 |
| Texas | TX | Austin | Dallas | 5560892 |
<details>
<summary>英文:</summary>
Here is another example for you.
Notable points:
- XML is inherently hierarchical, so the `CROSS APPLY` simulates one-to-many relationship between states and their cities.
- It is always better to use `.../text()` for the untyped XML elements for
performance reasons.
**SQL**
DECLARE @xml XML =
N'<root>
<state>
<StateName>Florida</StateName>
<Abbr>FL</Abbr>
<Capital>Tallahassee</Capital>
<cities>
<city>
<city>Miami</city>
<population>470194</population>
</city>
<city>
<city>Orlando</city>
<population>285713</population>
</city>
</cities>
</state>
<state>
<StateName>Texas</StateName>
<Abbr>TX</Abbr>
<Capital>Austin</Capital>
<cities>
<city>
<city>Houston</city>
<population>2100263</population>
</city>
<city>
<city>Dallas</city>
<population>5560892</population>
</city>
</cities>
</state>
</root>';
SELECT state.value('(StateName/text())[1]', 'VARCHAR(20)') AS State
, state.value('(Abbr/text())[1]', 'VARCHAR(20)') AS Abbr
, state.value('(Capital/text())[1]', 'VARCHAR(20)') AS Capital
, city.value('(city/text())[1]', 'VARCHAR(20)') AS city
, city.value('(population/text())[1]', 'INT') AS population
FROM @xml.nodes('/root/state') AS t1(state)
CROSS APPLY state.nodes('cities/city') AS t2(city);
**Output**
| State | Abbr | Capital | city | population |
|---------|------|-------------|---------|------------|
| Florida | FL | Tallahassee | Miami | 470194 |
| Florida | FL | Tallahassee | Orlando | 285713 |
| Texas | TX | Austin | Houston | 2100263 |
| Texas | TX | Austin | Dallas | 5560892 |
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论