sql - 如何重写 SQL xpath 查询以创建索引 View

Question

我有一个 View 从带有 xml 数据的表中提取一些信息。
每个文档包含一个 Product和几个PurchaseOrderDetail .我想创建一个包含所有 PurchaseOrderDetail 的 View 每个Product .

CREATE VIEW [dbo].[XML_PurchaseOrders]
WITH SCHEMABINDING
AS
    SELECT
    p.n.value('.', 'int') AS PurchaseOrderID
    ,x.ProductID
    FROM dbo.XmlLoadData x
    CROSS APPLY x.PayLoad.nodes('declare namespace NS="http://schemas.datacontract.org/2004/07/XmlDbPerfTest"; 
    /NS:ProductAndRelated/NS:Product/NS:PurchaseOrderDetails/NS:PurchaseOrderDetail/NS:PurchaseOrderID') p(n)
GO

PayLoad列包含 XML 数据。

这种 View 的问题在于它非常缓慢，因为 XML 文档既大又丰富。

我想索引这个 View ，但这给了我错误

Cannot create index on view "XmlLoad.dbo.XML_PurchaseOrders" because it contains an APPLY. Consider not indexing the view, or removing APPLY.

是否可以重写 View 以添加索引？

我尝试了几次，主要是 select ... from (select ....) innnerSelect group by foo但所有这些都取决于关于 View 索引的一个或另一个规则。

Answer 1

Is it possible to rewrite the view to make it possible to add an index?

不，我不这么认为。相反，我将使用来自 this 的一些信息提出问题并提出另一种方法。

您可以将计算的持久列添加到您的表中，该列仅包含具有 ID 的 XML。不允许直接在计算列中使用 XQUERY 内容，但您可以使用用户定义的函数来完成。

这是函数:

create function dbo.GetPurchaseOrderID(@XMLData xml) 
returns xml with schemabinding
as
begin
  return @XMLData.query('declare namespace NS="http://schemas.datacontract.org/2004/07/XmlDbPerfTest"; 
    /NS:ProductAndRelated/NS:Product/NS:PurchaseOrderDetails/NS:PurchaseOrderDetail/NS:PurchaseOrderID')
end 

使用持久化的 XML 列创建表:

CREATE TABLE [dbo].[XmlLoadData](
    [ProductID] [int] NOT NULL identity,
    [PayLoad] [xml] NOT NULL,
    [Size]  AS (len(CONVERT([nvarchar](max),[PayLoad],0))),
    [PurchaseOrderIDs] AS dbo.GetPurchaseOrderID(PayLoad) PERSISTED,
 CONSTRAINT [PK_XmlLoadData] PRIMARY KEY CLUSTERED 
(
    [ProductID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
       IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 
       ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

查询以获取 ID:

;with xmlnamespaces('http://schemas.datacontract.org/2004/07/XmlDbPerfTest' as NS)
select ProductID,
       P.N.value('.', 'int') as PurchaseOrderID
from XmlLoadData
  cross apply PurchaseOrderIDs.nodes('NS:PurchaseOrderID') as P(N)

我的有限测试表明它要快一些。如果您的 XML 文档很大，它应该是一个更大的改进。我怀疑它能否将性能提高 1000 倍，因为您仍在处理解释 XML 但您告诉我。如果没有你的数据，我当然无法测试。