My question is based on this: https://stackoverflow.com/q/35575990/5089204
To give an answer there I did the following test-scenario.
Test scenario
First I create a test table and fill it with 100.000 rows. A random number (0 to 1000) should lead to ~100 rows for each random number. This number is put into a varchar col and as a value into your XML.
Then I do a call like the OP there needs it with .exist() and with .nodes() with a small advantage for the second, but both take 5 to 6 seconds. In fact I do the calls twice: a second time in swapped order and with slightly changed search params and with "//item" instead of the full path to avoid false positives via cached results or plans.
Then I create an XML index and do the same calls
Now - what really did surprise me! - the .nodes
with full path is much slower than before (9 secs) but the .exist()
is down to half a second, with full path even down to about 0.10 sec. (while .nodes()
with short path is better, but still far behind .exist()
)
Questions:
My own tests bring up in short: XML indexes can blow up a database extremely. They can speed up things extremely (s. edit 2), but can slow your queries down too. I'd like to understand how they work... When should one create an XML index? Why can .nodes()
with an index be worse than without? How could one avoid the negativ impact?
CREATE TABLE #testTbl(ID INT IDENTITY PRIMARY KEY, SomeData VARCHAR(100),XmlColumn XML);
GO
DECLARE @RndNumber VARCHAR(100)=(SELECT CAST(CAST(RAND()*1000 AS INT) AS VARCHAR(100)));
INSERT INTO #testTbl VALUES('Data_' + @RndNumber,
'<error application="application" host="host" type="exception" message="message" >
<serverVariables>
<item name="name1">
<value string="text" />
</item>
<item name="name2">
<value string="text2" />
</item>
<item name="name3">
<value string="text3" />
</item>
<item name="name4">
<value string="text4" />
</item>
<item name="name5">
<value string="My test ' + @RndNumber + '" />
</item>
<item name="name6">
<value string="text6" />
</item>
<item name="name7">
<value string="text7" />
</item>
</serverVariables>
</error>');
GO 100000
DECLARE @d DATETIME=GETDATE()
SELECT #testTbl.*
FROM #testTbl
CROSS APPLY XmlColumn.nodes('/error/serverVariables/item[@name="name5" and value/@string="My test 600"]') AS a(b);
SELECT CAST(GETDATE()-@d AS TIME) AS NodesFullPath_no_index;
GO
DECLARE @d DATETIME=GETDATE();
SELECT *
FROM #testTbl
WHERE XmlColumn.exist('/error/serverVariables/item[@name="name5" and value/@string="My test 600"]') = 1;
SELECT CAST(GETDATE()-@d AS TIME) AS ExistFullPath_no_index;
GO
DECLARE @d DATETIME=GETDATE();
SELECT *
FROM #testTbl
WHERE XmlColumn.exist('//item[@name="name5" and value/@string="My test 500"]') = 1;
SELECT CAST(GETDATE()-@d AS TIME) AS ExistShortPath_no_index;
GO
DECLARE @d DATETIME=GETDATE()
SELECT #testTbl.*
FROM #testTbl
CROSS APPLY XmlColumn.nodes('//item[@name="name5" and value/@string="My test 500"]') AS a(b);
SELECT CAST(GETDATE()-@d AS TIME) AS NodesShortPath_no_index;
GO
CREATE PRIMARY XML INDEX PXML_test_XmlColum1 ON #testTbl(XmlColumn);
CREATE XML INDEX IXML_test_XmlColumn2 ON #testTbl(XmlColumn) USING XML INDEX PXML_test_XmlColum1 FOR PATH;
GO
DECLARE @d DATETIME=GETDATE()
SELECT #testTbl.*
FROM #testTbl
CROSS APPLY XmlColumn.nodes('/error/serverVariables/item[@name="name5" and value/@string="My test 600"]') AS a(b);
SELECT CAST(GETDATE()-@d AS TIME) AS NodesFullPath_with_index;
GO
DECLARE @d DATETIME=GETDATE();
SELECT *
FROM #testTbl
WHERE XmlColumn.exist('/error/serverVariables/item[@name="name5" and value/@string="My test 600"]') = 1;
SELECT CAST(GETDATE()-@d AS TIME) AS ExistFullPath_with_index;
GO
DECLARE @d DATETIME=GETDATE();
SELECT *
FROM #testTbl
WHERE XmlColumn.exist('//item[@name="name5" and value/@string="My test 500"]') = 1;
SELECT CAST(GETDATE()-@d AS TIME) AS ExistShortPath_with_index;
GO
DECLARE @d DATETIME=GETDATE()
SELECT #testTbl.*
FROM #testTbl
CROSS APPLY XmlColumn.nodes('//item[@name="name5" and value/@string="My test 500"]') AS a(b);
SELECT CAST(GETDATE()-@d AS TIME) AS NodesShortPath_with_index;
GO
DROP TABLE #testTbl;
EDIT 1 - Results
This is one result with SQL Server 2012 locally installed on a medium laptop
In this test I could not reproduce the extremly negativ impact on NodesFullPath_with_index
, although it is slower than without the index...
NodesFullPath_no_index 6.067
ExistFullPath_no_index 6.223
ExistShortPath_no_index 8.373
NodesShortPath_no_index 6.733
NodesFullPath_with_index 7.247
ExistFullPath_with_index 0.217
ExistShortPath_with_index 0.500
NodesShortPath_with_index 2.410
EDIT 2 Test with bigger XML
According to TT's suggestion I used the XML above, but copied the item
-nodes to reach about 450 items. I let the hit-node be very high up in the XML ('cause I think that .exist()
would stop on the first hit, while .nodes()
would continue)
Creating the XML-index blew up the mdf-file to ~21GB, ~18GB seem to belong to the index (!!!)
NodesFullPath_no_index 3min44
ExistFullPath_no_index 3min39
ExistShortPath_no_index 3min49
NodesShortPath_no_index 4min00
NodesFullPath_with_index 8min20
ExistFullPath_with_index 8,5 seconds !!!
ExistShortPath_with_index 1min21
NodesShortPath_with_index 13min41 !!!