Sql-Hardcore: Flexible Sortierungen / Performance

[Jürgen Auer]

Vor einigen Monaten tauchte bei einem Kunden so allmählich ein Problem auf, das sich nach den letzten Ergänzungen nochmals verschärfte.

Innerhalb von Server-Daten gibt es sehr flexible Möglichkeiten, (1) eine Tabelle mit anderen Tabellen zu verknüpfen, so daß die Pulldown-Listen automatisiert generiert werden, (2) bei jeder Tabelle kreuzweise über alle Spalten zu suchen, (3) dynamisch per Klick nach einer Spalte zu sortieren, (4) voreingestellt nach bis zu zwei Spalten zu sortieren. Natürlich soll bei der Sortierung nach einer verknüpften Spalte nicht nur nach den internen IDs, sondern auch nach der Beschriftung in der Randtabelle sortiert werden.

Normalerweise klappt das ziemlich gut, die Performance liegt im Bereich von 0,1 Sekunden oder darunter.

Bei einem Kunden war das bei einer Tabelle schon vor einigen Monaten bei etwa 0,5 Sekunden. In den letzten Wochen kamen bei dieser Tabelle nochmals diverse Spalten und Randtabellen dazu (Zahl der Spalten > 100, etwa 10 Randtabellen, diverse zusätzliche Filter und einige berechnete Spalten). Ferner sind das inzwischen über 25.000 Datensätze. Die Tabelle hat eine reine Größe von etwa 30 MB. Ergebnis nun: Die Abfrage dauerte inzwischen mehr als eine Sekunde.

Diese Standardzugriffe setzen sich aus drei Teilen zusammen:
(1) Eine Prozedur, die zählt, wieviele Zeilen insgesamt von dieser Filterung zurückgegeben werden.
(2) Eine Funktion, die eine Tabellenvariable mit zwei Spalten zurückgibt - ID und Position.

Diese zwei Elemente werden pro Tabelle automatisch generiert.

(3) Zusätzlich können beliebig viele Views erstellt werden. Diese rufen zunächst (1) auf, anschließend (2) und kombinieren den Output (eine temporäre zweispaltige Tabelle) mit den eigentlichen auszugebenden Spalten und können zusätzliche berechnete Spalten ergänzen.


(1) hat kein Performance-Problem produziert. (3) ist immer vernachlässigbar, weil (2) nur die tatsächlichen Zeilen (meist 10 - 50 Zeilen) zurückgibt. Arbeitsspeicher / Festplatte ist auch kein Problem, alle Daten (aller Kunden) liegen komplett im Arbeitsspeicher, so daß gar nicht auf die Festplatte zugegriffen wird.


Beispiel für den Kern von (2):

CODE Insert Into @_TV(Id, _pos_intern)
Select A.TestDatenId, A._pos
From (Select A.TestDatenId, row_number() Over (
Order By
Case @Column_Name
When N'TestDatenId' Then Cast(A.TestDatenId as sql_variant)
When N'Nachname' Then A.Nachname
When N'Vorname' Then A.Vorname
When N'DsStatusId' Then R__9.Bezeichnung
When N'Maildatum' Then A.Maildatum
When N'_Owner' Then A._Owner
When N'_Group' Then A._Group
When N'_DsStatusId' Then A.DsStatusId
End Asc, Case When @sc2 Is Not Null Then Case @sc2
When N'TestDatenId' Then Cast(A.TestDatenId as sql_variant)
When N'Nachname' Then A.Nachname
When N'Vorname' Then A.Vorname
When N'DsStatusId' Then R__9.Bezeichnung
When N'Maildatum' Then A.Maildatum
When N'_Owner' Then A._Owner
When N'_Group' Then A._Group
When N'_DsStatusId' Then A.DsStatusId
End Else A.TestDatenId End Asc) As _pos
From dbname.dbo.TestDaten As A Left Join DsStatus As R__9
On A.DsStatusId = R__9.DsStatusId
Where ((@s_TestDatenId Is Null) Or
(A.TestDatenId = @s_TestDatenId)) And
((@s_Nachname Is Null) Or
(A.Nachname Like @s_Nachname + N'%')) And
((@s_Vorname Is Null) Or
(A.Vorname Like @s_Vorname + N'%')) And
((@s_DsStatusId Is Null) Or
(A.DsStatusId = @s_DsStatusId)) And
((@s_Maildatum Is Null) Or
(A.Maildatum Like @s_Maildatum + N'%')) And
((@s__Owner Is Null) Or
(A._Owner = @s__Owner)) And
((CoalEsce(@s__Group, 0) In (-2, 0)) Or (A._Group = @s__Group))

) As A
Where (@_end_pos = -1) Or (A._pos Between @_start_pos And @_start_pos + @_end_pos - 1)


Das ist die Variante ASC/ASC (aufsteigend / aufsteigend) innerhalb von (2). @Column_Name ist ursprünglich entweder ein Spaltenname oder enthält zwei Spaltennamen, durch '|' getrennt und wird davor auf @Column_Name und @sc2 aufgespaltet. (2) enthält vier solche Varianten ASC/ASC, ASC/DESC, DESC/ASC und DESC/DESC, um alle Sortiervarianten zu erfassen.

Die Spalte DsStatusId verweist auf die Randtabelle DsStatus. Sortiert werden kann entweder nach der dortigen Spalte 'Bezeichnung' (Sortierung 'DsStatusId&#39 oder nach der internen ID (Sortierung _DsStatusId). Die tatsächliche Tabelle enthält mehr als 10 solcher Verknüpfungen, die im From-Abschnitt per Left Join angefügt werden.

Die obige Unterabfrage gibt zunächst Daten mit der Zeilenposition bezüglich der dynamischen Sortierung aus. Daraus wird der gewünschte Block zwischen @_start_pos und @_start_pos + @_end_pos - 1 tatsächlich zurückgegeben.

Bei der kritischen Tabelle umfaßte die Spaltensortierung mehr als 2 * 100 Zeilen, ebenso die Where-Filterungen.


Soweit zunächst die Grunddaten.

-----------------------------

Vermutungen?
Wie nähert man sich so einem Problem an?
Was würdet Ihr machen, um das Problem zu verstehen / einzugrenzen?

Anmerkungen: Eine ad-hoc - Generierung des Sql-Codes ist keine Lösung. Zum einen zu heikel wegen Sql-Injektionen und wegen unnötig hohen Berechtigungen, zum zweiten sieht man ja, daß alleine das Auslesen der Metadaten für die Generierung aberwitzig aufwendig wäre. Die Routinen (1), (2) sowie alle definierten Routinen vom Typ (3) werden jedesmal neu generiert, wenn eine Tabelle erstellt / geändert wird. Ferner kann es zusätzlich Sprachverzweigungen geben, dann gibt es zu (1)/(2) zusätzliche Sprachvarianten, die von (3) in Abhängigkeit von der gewünschten Sprache aufgerufen werden. So werden die Abfragepläne gecacht und pro Tag vielfach ohne Neukompilierung ausgeführt.

 

[Jürgen Auer]

QUOTE (Jürgen Auer @ Sa 20.02.2010, 22:13)Wie nähert man sich so einem Problem an?

Indem man Indizien, Bausteine sammelt, den Code vereinfacht und testet, was dann passiert.

Beobachtungen:

(i) Die reine Zählabfrage (1) mit Select Count(*), allen LeftJoin-Konstrukten und allen Where-Parametern benötigt etwa 0,05 Sekunden und wird mit einem parallelen Plan ausgeführt. Der Datenbank-Server hat zwei Prozessoren mit je vier Kernen, also 8 Kernen insgesamt. Bei einer Ausführung mit Set Statistics IO sieht man


CODE Scan count 4


was heißt, daß die Tabelle in vier Teile zerlegt wird, die je von einem Kern bearbeitet werden, anschließend wird das Ergebnis wieder zusammengefügt. Offenbar sieht der Abfrageoptimierer keinen Bedarf, noch mehr Kerne zu beschäftigen. Technisch können auch Kerne verschiedener Prozessoren eine Abfrage gemeinsam bearbeiten - das war bei entsprechenden Testläufen sichtbar.

(ii) Die interne Abfrage (2) wird dagegen sequentiell ausgeführt. Damit ist klar, daß das natürlich erheblich langsamer ist.

(iii) Kopiert man sich die Abfrage (2) raus und ersetzt man die dynamische Sortierung durch eine statische Sortierung


CODE Order By A.DsStatusId Asc, A.Maildatum Desc


dann sackt der Zeitbedarf auf einen Wert um 0,07 Sekunden, ebenfalls mit paralleler Ausführung. Hier sogar mit Scan Count 9, alle Kerne sind daran beteiligt, einer zweimal.

(iv) Es gibt externe Nutzer, denen diese Datensätze zugewiesen sind. Diese sehen nur 'ihre Daten', dafür wird nach der MitarbeiterId gefiltert. Übergibt man so einen Filter, dann gibt es bsp. nur noch 200 Datensätze, die sortiert werden müssen. Zeitbedarf um 0,05 Sekunden.

(v) Fügt man einen 'künstlichen Filter' ein, der berechnet, ob der Datensatz höchstens drei Monate ist (eine Art Archivfunktion), dann sackt der Zeitbedarf auch auf einen Wert um 0,1 Sekunden. Nur ist das unpraktikabel, da oft auch ältere Datensätze benötigt werden und man dann diesen voreingestellten Filter ständig rausnehmen müßte.

 

[Jürgen Auer]

So, ich muß dieses Problem mal noch auflösen.


Die obigen Bausteine lieferten die folgenden Hinweise:

(A) Mit hartcodierter Sortierung parallele Ausführung über viele Kerne gleichzeitig, sehr hohe Performance.
(B) Mit maximal flexibler Sortierung sequentielle Ausführung, Dauer alleine für (ii) mehr als eine Sekunde.

Dann zwei entscheidende, aufgrund dieser Daten naheliegende Tests:

© Eine flexible Sortierung, aber zunächst nur testweise über sehr wenige Spalten - parallele Ausführung, sehr hohe Performance

(D) Ausgehend vom Original (mit mehr als 100 Sortierspalten, damit etwa 101 * 100 ~ 10.000 theoretisch denkbaren Spaltenkombinationen) wurden Zeilen im Sortierausdruck entfernt. Zunächst diverse, die über nvarchar(Max) - Felder gehen, dann auch andere: Immer, wenn man einige Sortierspalten entfernt hatte, wurde die Performance etwas besser, ging runter auf 0,9 - 0,8 Sekunden.


Und bei etwa 50 Sortierspalten (also ungefähr 50 * 50 = 2500 Möglichkeiten) war die Ausführung parallel, ging damit auf etwa 0,20 Sekunden runter.


Folglich wurde das Gesamtsystem derart ergänzt, daß man nun pro Spalte festlegen kann, ob nach dieser Spalte sortierbar sein soll. Falls nicht (was bei sehr vielen Spalten dieser Tabelle einfach keinen Sinn macht), wird die Spalte gar nicht in den Sortierabschnitt aufgenommen.

Ergebnis beim Kunden: Ursprünglich dauerte die Seite etwa 2 Sekunden, bis sie komplett geliefert war, der Aufbau im lokalen Browser kam dann noch dazu. Die Seite wird von einigen Nutzern sehr häufig verwendet. Nun Dauer von etwa 0,6 Sekunden plus lokaler Aufbau.


Ferner: Indices spielen bei diesem Problem überhaupt keine Rolle, sie führen praktisch durchweg zu einer Verschlechterung, weil zunächst der Index durchsucht wird, dann zu jedem dort gefundenen Eintrag (im Zweifelsfall 25.000 mal) nach dem Hauptdatensatz gesucht wird.

Damit ist das ein schönes Beispiel, wo blindes Draufknallen von Indices kontraproduktiv wäre.

PS: Was ist denn das? C eingeklammert wird als © ausgegeben