Jürgen Auer
Legendäres Mitglied
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' 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.
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' 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.