如何重写 SQL xpath 查询以创建索引视图

发布于 2024-12-18 09:47:17 字数 975 浏览 1 评论 0原文

我有一个视图，它从包含 xml 数据的表中提取一些信息。
每个文档包含一个 Product 和多个 PurchaseOrderDetail。我想为每个 Product 创建一个包含所有 PurchaseOrderDetail 的视图。

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 数据。

此视图的问题在于，由于 XML 文档又大又多，所以速度非常慢。

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

无法在视图“XmlLoad.dbo.XML_PurchaseOrders”上创建索引，因为它包含 APPLY。考虑不对视图建立索引，或删除 APPLY。

是否可以重写视图以添加索引？

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

原文

I have a view that extracts some info from a table with xml data.
Each document contains one Product and several PurchaseOrderDetail. I want to create a view with all PurchaseOrderDetail for each 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

The PayLoad column contains the XML data.

The problem with this view is that it is painfully slow since the XML documents are both large and plentiful.

I would like to index this view but that gives me the error

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

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

I have tried several attempts, mostly with select ... from (select ....) innnerSelect group by foo but the all fall down on one or another rule regarding indexes on views.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

小姐丶请自重 2024-12-25 09:47:17

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 解释，但您告诉了我。如果没有您的数据，我当然无法进行测试。

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

No. I don't think so. Instead I'm going to use some info from this question and suggest another way.

You can add a calculated persisted column to your table that only holds the XML that has the id's. It is not allowed to use XQUERY stuff directly in a computed column but you can do it with a user defined function.

Here is the function:

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

Create the table with the persisted XML column:

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]

Query to get the IDs:

;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)

Limited testing on my part shows that it is a bit faster. If your XML documents are large it ought to be a bigger improvement. I doubt it will improve performance by 1000x because you are still dealing with interpreting XML but you tell me. I have of course no way of testing that without your data.

回复收藏 0 原文