I've set up a test to benchmark xml performance in SQL server.
- One Million rows of data
- XML defined Column with primary key
- A primary XML index
- A secondary XML index on the path
- XML data is similar in format but with variable tag names in each document
Table and Index Design
CREATE TABLE [dbo].[xml_Test] ( [ID] [int] IDENTITY(1,1) NOT NULL, [GUID] [varchar](50) NULL, [JSON_Data] [varchar](max) NULL, [XML_Data] [xml] NULL, CONSTRAINT [PK_xml_Test] PRIMARY KEY CLUSTERED ([ID] ASC) ); ALTER TABLE [dbo].[xml_Test] ADD CONSTRAINT [DF_xml_Test_GUID] DEFAULT (newid()) FOR [GUID]; ALTER TABLE [dbo].[xml_Test] ADD CONSTRAINT [PK_xml_Test] PRIMARY KEY CLUSTERED ([ID] ASC); CREATE PRIMARY XML INDEX [PK_xml] ON [dbo].[xml_Test] ( [XML_Data]); CREATE XML INDEX [IX_xml_Path] ON [dbo].[xml_Test] ( [XML_Data]) USING XML INDEX [PK_xml] FOR PATH;
Sample XML Schema
<data> <id>3812</id> <guid>E3735046-1183-4A79-B8EE-806312B533D6",</guid> <firstName>John</firstName> <lastName>Doe</lastName> <tel>123-123-1234</tel> <city>Toronto</city> <prov>Ontario</prov> <Q.49.R.47>14325</Q.49.R.47> <Q.1>14326</Q.1> <Q.9>143257</Q.9> <Q.25>14328</Q.25> <Q.50>14329</Q.50> <Q.51>14330</Q.51> <Q.30>14331</Q.30> <Q.22>14332</Q.22> <Q.100>14333</Q.100> <Q.70.R.4>1</Q.70.R.4> <Q.43>14335</Q.43> <Q.3>14336</Q.3> <Q.84.R.21.L.19>1</Q.84.R.21.L.19> <done>1</done> </data>
When I go to query the table with two different values, I get a very efficient seek for one result and a scan for the other that takes orders of magnitude longer to complete.
I'm unsure as to why the second query is not able to do a seek on the index. I think it may have something to do with the element not being in the index to start with but even then I don't know why a scan would be necessary unless the index really didn't index all paths.
select ID, 'Query A' from xml_test where XML_Data.exist('(/data/Q.70.R.4)')=1
select id, 'Query B' from xml_test where XML_Data.exist('(/data/Q.61.R.15)')=1
As you can see, Query A takes 5ms to execute compared to almost 15s for query B.
What are the reasons that the same query format but with different input would yield such an obviously inefficient execution plan?
- I was also using this to test against JSON with identically formatted JSON data and my results show that the identical JSON query as XML Query A took 22,344 ms (22 seconds!) of cpu time (3,419 ms clock time) to run.
- If the XML index can work reliably with a seek operation, it is clearly the more performant read option contrary to what I have researched and read online.
- When querying over a large amount of data, I would not recommend JSON at all inside SQL server other than for blob storage or where you have a small dataset, which is to say, don't use it in production for searchable data that should be indexed. There are other good services designed for that.