Moje pytanie opiera się na tym: https://stackoverflow.com/q/35575990/5089204
Aby udzielić odpowiedzi, wykonałem następujący scenariusz testowy.
Scenariusz testowy
Najpierw tworzę tabelę testową i wypełniam ją 100 000 wierszy. Liczba losowa (od 0 do 1000) powinna prowadzić do ~ 100 wierszy dla każdej liczby losowej. Liczba ta jest umieszczana w kolumnie varchar i jako wartość w pliku XML.
Następnie wykonuję wywołanie takie jak OP, które jest potrzebne przy użyciu .exist () i .nodes () z niewielką przewagą na sekundę, ale oba zajmują od 5 do 6 sekund. W rzeczywistości wykonuję połączenia dwa razy: drugi raz w kolejności zamienianej i z nieznacznie zmienionymi parametrami wyszukiwania oraz z „// item” zamiast pełnej ścieżki, aby uniknąć fałszywych wyników pozytywnych poprzez buforowane wyniki lub plany.
Następnie tworzę indeks XML i wykonuję te same wywołania
Teraz - co naprawdę mnie zaskoczyło! - .nodesz pełną ścieżką jest znacznie wolniejszy niż wcześniej (9 sekund), ale jest krótszy .exist()do pół sekundy, z pełną ścieżką nawet do około 0,10 sekundy. (podczas gdy .nodes()z krótka droga jest lepsza, ale nadal daleko w tyle .exist())
Pytania:
Moje własne testy są w skrócie: indeksy XML mogą bardzo wysadzić bazę danych. Mogą bardzo przyspieszyć rzeczy (s. Edycja 2), ale mogą również spowolnić twoje zapytania. Chciałbym zrozumieć, jak one działają ... Kiedy należy utworzyć indeks XML? Dlaczego .nodes()z indeksem może być gorzej niż bez? Jak można uniknąć negatywnego wpływu?
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;EDYCJA 1 - Wyniki
To jeden wynik z SQL Server 2012 zainstalowanym lokalnie na średnim laptopie. W tym teście nie mogłem odtworzyć wyjątkowo negatywnego wpływu NodesFullPath_with_index, chociaż jest on wolniejszy niż bez indeksu ...
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.410EDYCJA 2 Test z większym XML
Zgodnie z sugestią TT użyłem powyższego kodu XML, ale skopiowałem item-nodes, aby osiągnąć około 450 pozycji. Pozwoliłem, aby węzeł trafienia znajdował się bardzo wysoko w pliku XML (ponieważ myślę, .exist()że zatrzymałoby się to przy pierwszym trafieniu, a .nodes()kontynuowałoby)
Utworzenie indeksu XML wysadziło plik mdf do ~ 21 GB, ~ 18 GB wydaje się należeć do indeksu (!!!)
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 !!!
.nodes()i.exist()są przekonujące. Również fakt, że indeks zfull path searchjest szybszy, wydaje się łatwy do zrozumienia. Oznaczałoby to: Jeśli utworzyć indeks XML, należy zawsze mieć świadomość negatywnego wpływu każdej z XPath generycznego (//lub*lub..lub[filter]lub cokolwiek nie po prostu XPath ...). W rzeczywistości powinieneś korzystać tylko z pełnej ścieżki - całkiem niezły remis ...