SQL Server 2005中的XML数据类型

Question

更新：这个问题是音符相关的XML，我重复使用一个nvarchar（MAX），而不是和仍然相同的问题表。我将重新发布一个新话题。

我有一个表约一百万条记录，该表具有XML领域。查询运行速度非常慢，即使只选择一个ID。有什么我可以做的，以提高这个速度，我已经尝试设置文本行，但SQL服务器不会允许我，我收到错误“无法切换到表中的行文本”。

我希望在修复或知识的任何帮助，我似乎丢失。

感谢

表

/****** Object: Table [dbo].[Audit] Script Date: 08/14/2009 09:49:01 ******/ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
CREATE TABLE [dbo].[Audit](
    [ID] [int] IDENTITY(1,1) NOT NULL, 
    [ParoleeID] [int] NOT NULL, 
    [Page] [int] NOT NULL, 
    [ObjectID] [int] NOT NULL, 
    [Data] [xml] NOT NULL, 
    [Created] [datetime] NULL, 
CONSTRAINT [PK_Audit] PRIMARY KEY CLUSTERED 
(
    [ID] 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

DECLARE @ID int 
SET @ID = NULL 
DECLARE @ParoleeID int 
SET @ParoleeID = 158  
DECLARE @Page int 
SET @Page = 2 
DECLARE @ObjectID int 
SET @ObjectID = 93 
DECLARE @Created datetime 
SET @Created = NULL 
SET NOCOUNT ON; 
Select TOP 1 [Audit].* from [Audit] 
where 
    (@ID IS NULL OR Audit.ID = @ID) AND 
    (@ParoleeID IS NULL OR Audit.ParoleeID = @ParoleeID) AND 
    (@Page IS NULL OR Audit.Page = @Page) AND 
    (@ObjectID IS NULL OR Audit.ObjectID = @ObjectID) AND 
    (@Created is null or(Audit.Created > @Created and Audit.Created < DATEADD (d, 1, @Created)) ) 

Answer 1

您需要在该列上创建主XML索引。高于其他任何事情将有助于您所有的疑问。

一旦你有了这个，你可以在xml数据的XML列中创建索引。

从经验看不过，如果你可以存储在关系表的一些信息，SQL是在搜索比XML更好和索引这一点。即任何关键列和通常搜索的数据都应在可能的情况下进行关联存储。

Answer 2

像你运行查询，表结构，XML内容等肯定会帮助一些信息。很多...

没有任何信息，我会猜测。选择仅ID时，查询运行缓慢，因为您没有ID中的索引。

更新

至少有一些严重的问题，您的查询。

• 除非提供一个ID，所述表只能被扫描的端至端，因为没有索引
• 即使设置一个ID，条件（@ID是NULL OR ID = @ID ）不保证是SARGable，因此它可能仍然会导致表扫描。
• 最重要的是：该查询将产生第一套它看到参数的优化“的计划。它将在任何参数组合上重复使用此计划，而不管其是否为NULL。如果存在可供选择的访问路径上的一些变化（即，索引），那么这将会产生影响，但是现在，如果存在@id，查询只能选择使用扫描还是查找。由于构造方式的原因，它总是选择一个扫描，因为OR。

使用此表格设计您的查询今天会​​运行缓慢，明天变慢，下周不可能随着尺寸的增加而变慢。您必须回头看看您的要求，确定哪些字段需要查询，索引它们并为它们提供单独的查询。将所有可能的过滤器组合在一起不会起作用。

你试图检索XML具有绝对无关的性能问题。你只是粗暴地强制进行表扫描，并希望SQL能够神奇地找到你想要的记录。

所以，如果你想要检索特定ParoleeID，页面和对象ID，你索引你搜索上跑一跑一个查询的领域，只有那些：

CREATE INDEX idx_Audit_ParoleeID ON Audit(ParoleeID); 
CREATE INDEX idx_Audit_Page ON Audit(Page); 
CREATE INDEX idx_Audit_ObjectID ON Audit(ObjectID); 
GO 

DECLARE @ParoleeID int 
SET @ParoleeID = 158  
DECLARE @Page int 
SET @Page = 2 
DECLARE @ObjectID int 
SET @ObjectID = 93  

SET NOCOUNT ON; 
Select TOP 1 [Audit].* from [Audit] 
where Audit.ParoleeID = @ParoleeID 
    AND Audit.Page = @Page 
    AND Audit.ObjectID = @ObjectID; 

Answer 3

SQL Server 2005中 - 十二个技巧对于由托尼·赖特优化查询性能

1. 打开执行计划和统计
2. 使用聚集索引
3. 使用索引视图
个
4. 利用了覆盖索引
5. 让您的聚集索引小。
6. 避免光标
7. 归档旧数据
8. 分区中的数据正确
9. 删除用户定义的内联标量函数
10. 用途适用
11. 使用计算列
12. 使用正确的事务隔离级别

http://tonesdotnetblog.wordpress.com/2008/05/26/twelve-tips-for-optimising-sql-server-2005-queries/

Answer 4

我有相同的情况 - 我们的解决方案是计算列。

对于您需要从XML中频繁获取的那些信息位，我们在“托管”表上创建了一个计算列，该列基本上伸入XML中，并使用XPath从XML中提取必要的值。在大多数情况下，我们甚至可以坚持这个计算列，以便它成为表的一部分，并且可以查询甚至索引，并且查询速度不再是问题（在这些列上）。

我们也尝试在开始XML索引，但是它们的缺点是，他们是在磁盘上绝对是巨大的事实 - 这可能会或可能不会是一个问题。由于我们需要频繁地运送整个数据库（作为SQL备份），所以我们最终放弃了它们。

好的，要设置一个计算列以从您的XML中的信息位中检索，首先需要创建一个存储函数，它将XML作为参数，提取您需要的任何信息，然后传回 - 是这样的：

CREATE FUNCTION dbo.GetShopOrderID(@ShopOrder XML) 
RETURNS VARCHAR(100) 
AS BEGIN 
    DECLARE @ShopOrderID VARCHAR(100) 

    SELECT 
    @ShopOrderID = @ShopOrder.value('(ActivateOrderRequest/ActivateOrder/OrderHead/OrderNumber)[1]', 'varchar(100)') 

    RETURN @ShopOrderID 
END 

然后，你需要计算列添加到您的表，并将其连接到该存储功能：

ALTER TABLE dbo.YourTable 
ADD ShopOrderID AS dbo.GetShipOrderID(ShopOrderXML) PERSISTED 

现在，您可以轻松地从表中选择数据使用这个新的专栏，就好像它是一个正常的专栏N：

SELECT (fields) FROM dbo.YourTable 
WHERE ShopOrderID LIKE 'OSA%' 

最重要的是 - 当你更新你的XML，所有的计算列被更新，以及 - 他们总是同步的，没有触发器或其他黑魔法需要！

Marc