XPath-in T-SQL-Abfrage
Ich habe zwei Tabellen, XMLtable
und filterTable
.
Ich brauche alle XMLtable.ID
Werte von XMLtable
wo die Daten in Col_X
enthält MyElement
, der Inhalt entspricht filterColumn
im filterTable
.
Den XML-Code für jede Zeile in Col_X
enthält möglicherweise mehrere MyElement
's, und ich will, dass ID
im Falle JEDES dieser Elemente Stimmen die Werte in filterColumn
.
Das problem ist, dass die Spalten, die tatsächlich von varchar(max)
Datentyp, und die Tabelle selbst ist riesig (wie 50 GB groß). Also diese Abfrage muss so optimiert wie möglich.
Hier ist ein Beispiel für, wo ich jetzt bin, die nur gibt die Zeile, wo das erste übereinstimmende element entspricht einer von denen, die ich Suche. Durch eine Vielzahl von unterschiedlichen Fehlermeldungen, ich kann nicht scheinen, um in der Lage sein, dies zu ändern, zu vergleichen, alle gleichnamigen Elemente, wie ich will.
SELECT ID,
CAST(Col_X AS XML).value('(//*[local-name()=''MyElement''])', N'varchar(25)')
FROM XMLtable
...und dann die Ergebnisse vergleichen zu filterTable
. Dies nimmt schon 5+ Minuten.
Was ich versuche zu erreichen, ist so etwas wie:
SELECT ID
FROM XMLtable
WHERE CAST(Col_X AS XML).query('(//*[local-name()=''MyElement''])')
IN (SELECT filterColumn FROM filterTable)
Nur so kann ich derzeit erzielen, ist die Nutzung des LIKE-operator, die nimmt, wie tausend mal länger.
Nun, offensichtlich ist es nicht eine option, um die änderung der Datentypen von Spalten oder sonst was. Dies ist, was ich habe, mit zu arbeiten. 🙂
Würde ich machen, da ich natürlich nicht geben kann, die echte XML hier. Es ist überall Dutzende, Hunderte von Zeilen von verschiedenen Elementen, wo MyElement kann zwischen 0 und X-mal. Ich möchte zu finden der ID ' s für die Zeilen, in denen es erscheint, und einer von die, die Instanzen enthält, die Werte in der anderen Tabelle.
InformationsquelleAutor Kahn | 2012-09-28
Du musst angemeldet sein, um einen Kommentar abzugeben.
Versuchen Sie dies:
und so:
Update
Ich denke, dass Sie erleben, was hier beschrieben Berechnen Skalare Ausdrücke und Ausführungsplan Leistung. Die Besetzung in XML latente jedem Aufruf der
value
Funktion. Der test, den Sie machen sollten ist, ändern Sie den DatentypCol_X
zuXML
.Wenn das nicht eine option, die Sie könnte die Abfrage der Zeilen, die Sie brauchen, von XMLTable in eine temporäre Tabelle, die eine XML-Spalte, und führen Sie dann die obige Abfrage gegen die temporäre Tabelle ohne die Notwendigkeit zu werfen, um XML.
Die wichtigste Veränderung ist, dass ich
(./text())[1]
zum abrufen der Werte statt nur.
. Es hat einen großen Einfluss auf die geschätzten Kosten der Abfrage-plan. Sie haben, um es zu testen, ob es die gleiche Wirkung auf den Zeitpunkt der Ausführung. Die Umstrukturierung der Abfrage könnte ein wenig helfen, da diese nur holt sich der Wert fürMyElementValue
einmal statt zweimal.Ach, wusste nicht, dass. Ich bin neu in xpath-also hatte ich keine Ahnung von den Feinheiten, wie es funktioniert. Also die Erklärung ist geschätzt. 🙂
Wieder dieser wenn nun wir hatten eine Notwendigkeit für mehrere XML - /Xpath-Abfragen in SQL Server. Leider weiß ich nicht scheinen, um mehr Leistung aus als die andere Variante unten.
InformationsquelleAutor Mikael Eriksson
Könnten Sie versuchen, so etwas wie dieses. Es ist zumindest funktionell tun, was Sie wollen, glaube ich. Sie müssen zu erkunden, seine Leistung mit dem Datensatz empirisch.
Ich fand, dass das hinzufügen einer zusätzlichen JOIN-Bedingung beruht auf der Verwendung von CHARINDEX, um einen einfachen string-Suche für den filter-Wert (in der original-varchar-Spalte) mit Hilfe der Optimierer beschränken, XML-parsing auf eine viel kleinere Bevölkerung von Zeilen. Dies führte zu einer Größenordnung, die Beschleunigung der Abfrage in meinen tests.
Ah ok, ich werde in diesem Blick, danke!
InformationsquelleAutor Chris Dickson
Habe ich endlich das arbeiten, und mit viel bessere Leistung als ich erwartet habe. Unten ist das Skript, das schließlich erzeugt das richtige Ergebnis in 5 - 6 Minuten.
Danke für die Hilfe, tho Menschen!
InformationsquelleAutor Kahn