Performance issue with SQL Server and XML datatype columns

Question

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:

• 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]

• 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".

• I added 2 indexes using this article

• I create views to retrieve the xml as tabular data and I've tried both the .query() and the .nodes/.value method.

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...

Answer 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...