SQL Server和XML数据类型列的性能问题

I am encountering performance issues using an XML datatype column in SQL Server 2016. It looks like my query is looping through the xml over and over. Here is my setup:

我在SQL Server 2016中使用XML数据类型列遇到了性能问题。看起来我的查询一遍又一遍地循环遍历xml。这是我的设置：

• Create a table with an ID int column (PrimaryKey) and an XML column CREATE TABLE [dbo].[MYTABLE]([ID] [int] IDENTITY(1,1) NOT NULL,[XMLDATA] [xml] NULL,CONSTRAINT [PK_MYTABLE] 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] TEXTIMAGE_ON [PRIMARY]
• 创建一个ID为int int（PrimaryKey）的表和一个XML列CREATE TABLE [dbo]。[MYTABLE]（[ID] [int] IDENTITY（1,1）NOT NULL，[XMLDATA] [xml] NULL，CONSTRAINT [ PK_MYTABLE] 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] TEXTIMAGE_ON [PRIMARY]

• Import around 3,000 xml files (~200KB in size) which look something similar to this: <root> <item> <transaction> <prop1></prop1> <prop2></prop2> </transaction> <load> <loaditem> <loadprop1></loadprop1> <loadprop2> <loadsubprop1></loadsubprop1> </loadprop2> </loaditem> </load> </item> </root> where there can be 400 to 500 "loaditems".

  导入大约3,000个xml文件（大小约200KB），看起来类似于： 其中可以有400到500个“loaditems”。

• I added 2 indexes using this article

  我使用这篇文章添加了2个索引

• I create views to retrieve the xml as tabular data and I've tried both the .query() and the .nodes/.value method.
• 我创建视图以检索xml作为表格数据，我已经尝试了.query（）和.nodes / .value方法。

Performance of a simple query over a total of 3300 records is dreadful; it takes 45 seconds to query just the transaction/prop1 and prop2 part...

在总共3300条记录上执行简单查询是可怕的;只查询事务/ prop1和prop2部分需要45秒...

1 个解决方案

#1

It seems that my queries performed MUCH better without the indexes. I removed them and instead of 45 seconds it's down to 2 seconds...

看来我的查询在没有索引的情况下表现得更好。我删除了它们，而不是45秒，它减少到2秒......