In SQL server löschen Sie die Knoten mit .modify () XQuery, der 38 Minuten ausführt

In SQL server habe ich eine gespeicherte Prozedur mit XML-Typ temporäre Variable, und ich mache eine Löschoperation auf dieser Variable. Wenn ich diese gespeicherte Prozedur in meiner lokalen VM läuft, die 4 coree und 6 GB RAM hat, dauert es 24 Sekunden, um auszuführen. Aber wenn ich die gleiche gespeicherte Prozedur im server mit 40 coree und 128 GB RAM betreibe, nimmt diese Löschanweisung mehr als 38 Minuten aus, um sie auszuführen. Die gesamte gespeicherte Prozedur wird in dieser Delete-statement für 38 Minuten gehängt. Nach der Kommentierung der Delete-statement wird die gespeicherte Prozedur in 8 Sekunden auf dem server ausgeführt. Wie kann ich dieses performancesproblem beheben? Gibt es etwas falsch in der SQL-server-configuration?

DECLARE @PaymentData AS XML SET @PaymentData = .....(Main XML Query) SET @PaymentData.modify('delete //*[not(node())]') 

@Mikael: Unten ist der Ausführungsplan für das Zerkleinern in die Reihenlösung auf dem server (mit 40 coree und 128 GB RAM) Bildbeschreibung hier eingebenBildbeschreibung hier eingeben Und unten ist der Excecution-Plan in meiner lokalen VM (mit 4 coree und 6 GB RAM): Bildbeschreibung hier eingebenBildbeschreibung hier eingeben

Auf meiner Maschine nahm die Löschung 1 Stunde 25 Minuten und gab mir diese nicht so schöne Abfrage Plan.

Bildbeschreibung hier eingeben

Dieser Plan findet alle leeren Knoten (die zu löschen) und speichert die in einer Tabellenspule. Dann gibt es für jeden Knoten im gesamten Dokument einen Scheck, ob dieser Knoten in der Spule vorhanden ist (Nested Loops (Left Semi Join)) und wenn dieser Knoten aus dem Endergebnis ausgeschlossen ist (Merge join (Left Anti Semi Join) ). Die XML wird dann von den Knoten im UDX-Operator wieder aufgebaut und der variables zugeordnet. Die Tabellenspule ist nicht indiziert, so dass für jeden Knoten, der überprüft werden muss, ein Scan der gesamten Spule (oder bis eine Übereinstimmung gefunden wird).

Das bedeutet im Wesentlichen, dass die performance dieses Algorithmus O(n*d) wobei n die Gesamtzahl der Knoten ist und d die Gesamtzahl oder die gelöschten Knoten ist.

Es gibt ein paar mögliche Workarounds.

Zuerst und vielleicht das Beste ist, wenn du deine XML-Abfrage ändern kannst, um die leeren Knoten nicht an erster Stelle zu produzieren. Völlig möglich, wenn du das XML mit for xml hast und vielleicht nicht möglich bist, wenn du bereits Teile der in einer Tabelle gespeicherten XML hast.

Eine weitere Möglichkeit besteht darin, das XML on Row zu Row (siehe Beispiel-XML unten), das Ergebnis in eine Tabellenvariable setzen, das XML in der Tabellenvariable ändern und dann das kombinierte XML neu erstellen.

 declare @T table(PaymentData xml); insert into @T select TXquery('.') from @PaymentData.nodes('Row') as T(X); update @T set PaymentData.modify('delete //*[not(node())]'); select T.PaymentData as '*' from @T as T for xml path(''); 

Bildbeschreibung hier eingeben

Dies gibt Ihnen die performancesmerkmale von O(n*s*d) wobei n die Anzahl der Zeilenknoten ist, s die Anzahl der Subknoten pro Zeilenknoten und d die Anzahl der gelöschten Zeilen pro Zeilenknoten ist.

Eine dritte Option, die ich wirklich nicht empfehlen kann, ist, eine undokumentierte Trace-Flagge zu verwenden, die die Verwendung einer Spule im Plan entfernt. Sie können es ausprobieren im Test oder Sie können vielleicht erfassen den Plan generiert und verwenden Sie es in einem Plan-Guide.

 declare @T table(PaymentData xml); insert into @T values(@PaymentData); update @T set PaymentData.modify('delete //*[not(node())]') option (querytraceon 8690); select @PaymentData = PaymentData from @T; 

Abfrageplan mit Trace-Flag:

Bildbeschreibung hier eingeben

Statt 1 Stunde 25 Minuten dauerte diese Version 4 Sekunden auf meinem Computer.

Das Zerkleinern des XML auf mehrere Zeilen auf die Tabellenvariable hat insgesamt 6 Sekunden ausgeführt.

Ohne irgendwelche Zeilen zu löschen, ist natürlich die schnellste.

Beispieldaten, 12000 Knoten mit 32 Subnodes, wobei 2 leer ist, wenn man das zu Hause ausprobieren möchte.

 declare @PaymentData as xml; set @PaymentData = ( select top(12000) 1 as N1, 1 as N2, 1 as N3, 1 as N4, 1 as N5, 1 as N6, 1 as N7, 1 as N8, 1 as N9, 1 as N10, 1 as N11, 1 as N12, 1 as N13, 1 as N14, 1 as N15, 1 as N16, 1 as N17, 1 as N18, 1 as N19, 1 as N20, 1 as N21, 1 as N22, 1 as N23, 1 as N24, 1 as N25, 1 as N26, 1 as N27, 1 as N28, 1 as N29, 1 as N30, '' as N31, '' as N32 from sys.columns as c1, sys.columns as c2 for xml path('Row') ); 

Anmerkung: Ich habe keine Ahnung, warum es nur 24 Sekunden dauert, um auf einem deiner server auszuführen. Ich würde Ihnen raten, zu überprüfen, dass das XML tatsächlich identisch ist. Oder warum nicht mit dem XML-Beispiel testing, das ich dir zur Verfügung gestellt habe

Aktualisieren:

Für die Zerkleinerungsversion könnte das Problem mit der Spule in der Löschabfrage in die Zerkleinerungsabfrage verschoben werden, wobei du dich mit der gleichen schlechten performance verlässt. Das ist aber nicht immer wahr Ich habe Pläne gesehen, wo es keine Spule und Pläne gibt, wo es eine Spule gibt und ich weiß nicht, warum es dort manchmal ist und warum es nicht zu anderen timeen ist.

Ich habe auch festgestellt, dass, wenn Sie einen Temp-Tisch verwenden statt mit insert ... into Ich bekomme nicht die Spule in der Zerkleinerung Abfrage.

 select TXquery('.') as PaymentData into #T from @PaymentData.nodes('Row') as T(X); update #T set PaymentData.modify('delete //*[not(node())]');