Sql-Hardcore: Kalender mit 5 Min. Kompilierung

Jürgen Auer

Jürgen Auer

Legendäres Mitglied
Innerhalb von Server-Daten gibt es die Möglichkeit, Kalenderdaten 'quer' anzuzeigen, wie das in der Kopfzeile von diesem Online-Kalender gezeigt ist: Ein Block von acht Wochen, darunter in Form einer Balkendarstellung bsp. diverse mehrtägige Veranstaltungen, die nach Kriterien der ersten Spalte gegliedert sind.

Man denke bsp. an einen Pool von Ressourcen (Personen, Tools wie Beamer o.ä.), die alle in der ersten Spalte links stehen. Die Balken zeigen dann Einsatzzeiten o.ä. an. Man sieht also auf einen Blick, daß eine Ressource für einen bestimmten Zeitraum bereits blockiert / im Einsatz ist.


Grundsätzliches Problem: Man will blättern, also ändern sich die Spaltenüberschriften. Ferner ist die Zahl der Zeilen variabel - neue Ressourcen kommen hinzu.

Grundsätzliches Prinzip: Eine Abfrage, die zu jedem Datum innerhalb dieser acht Wochen eine lange Zeile ausgibt, pro Ressource eine oder mehrere Spalten. Diese Abfrage wird bei der Ausgabe 'gekippt', so daß die Datumsangaben oben, die Ressourcen links und dann eben - in Abhängigkeit von den Ergebnissen - Zellen per Colspan zusammengefaßt werden, eingefärbt sind und bsp. einen Link auf Detailinformationen enthalten.

CODE Select A._tbl_CalendarId, A.thisDay,
X1.Spalte1 As X1_1, X1.Spalte2 As X1_2,
X2.Spalte1 As X2_1, X2.Spalte2 As X2_2,

usw. für jede Ressource

From _tbl_Calendar As A

Left Join (Select alle Informationen zur Ressource 1) As X1
On A.thisDay Between X1.Beginn And X1.Ende

Left Join (Select alle Informationen zur Ressource 2) As X2
On A.thisDay Between X2.Beginn And X2.Ende

... analoge LeftJoins für jede Ressource

Where A.thisDay Between @start_date And @end_date



Also eine interne Tabelle (ist für alle SD-Kunden dieselbe), die eine Spalte mit allen Tagen innerhalb des Intervalls liefert, dann zu jeder Ressource einen LeftJoin (mit den Ressourcen-Ids als eindeutigen Nummern für den Alias), oben werden die Spalten ausgegeben.

Das lief seit Monaten problemlos mit einem Typ von Ressourcen.

Eigentlich einfacher Wunsch: Einen zweiten Ressourcentyp unten ergänzen. Also fröhlich gebaut, im Prinzip mußte 'fast nur' diese automatisch generierte Abfrage angepaßt werden.

Und was passiert: Der ursprüngliche Code benötigte beim Erstaufruf vielleicht 5 - 10 Sekunden zur Kompilierung (Erstellung eines Ausführungsplans, der dann gecacht wird und wiederholt verwendet werden kann). Zwar lang, das ließ sich aber über einen nächtlichen Aufruf abfangen. Nun dagegen (etwa 25 weitere Ressourcen, also 25 Left Joins, noch ohne Spalten) - dauert das reine Kompilieren mehr als 5 Minuten (ein Serverkern ist da über 5 Minuten zu 100 % ausgelastet).

Bei den Unterabfragen gab es Inner Joins -> Left Joins draus gemacht, keine Änderung. Indices ergänzt, keine Änderung, eher Verschlechterung.


Worin besteht das Problem und wie läßt es sich lösen? Die Lösung poste ich hier.

PS: Es ist eine Winzigkeit, welche die Kompilierungszeit in den Bereich weit unter einer Sekunde befördert.
 
Und nur das Erstellen des Ausführungsplans dauerte so lange?
Ich bin ja mal gespannt auf deine Erklärung. Ich hab den von dir referenzierten Artikel kurz überflogen, kann jedoch auf die Schnelle nichts finden, was mich so auf die Schnelle auf eine Lösung zu dem Rätsel führen würde.
 
QUOTE (polonius @ Di 18.08.2009, 20:48)Und nur das Erstellen des Ausführungsplans dauerte so lange?

Genau das ist ja die merkwürdige Beobachtung gewesen.

Die per Left Join eingebundene Tabelle war noch fast leer (enthielt grade mal ein paar Testdatensätze).

Es gab bereits stapelweise Left Joins aus einer Tabelle mit diversen Datensätzen.

Und dann ging die Erstellung des Ausführungsplans so dramatisch hoch.

Die eigentliche Ausführung der Abfrage lag auch da unter einer Sekunde - wenn es den Plan erst einmal gab.


Auf die 'richtige Idee' (bzw. zumindest die Idee, die das Problem gelöst hat) kam ich über den Hinweis, daß die Nutzung von Variablen anstelle von Konstanten dazu führt, daß der Optimierer (den es ja auch noch dazwischen gibt) sich womöglich schwer tut, dann die Kardinalität einer Abfrage korrekt einzuschätzen.


Die eigentlichen Werkzeuge, die es auf dem MS-SqlServer gibt (ShowPlan), hatte ich nicht genutzt - da man bei so vielen Unterabfragen ohnehin nicht mehr allzuviel sieht.
 
Upps, sorry - vor lauter Arbeiten ganz vergessen, daß ich das ja noch auflösen sollte. Dabei gab es inzwischen schon wieder neue interessante Dinge in bezug auf die Sql-Performance.


Der obige Artikel brachte mich an der Stelle auf die 'richtige Idee', bei der es um den Einsatz von Variablen geht.

Denn bei Variablen ist es für den Optimierer unklar, welche Werte diese annehmen können, so daß die Kardinalität eines Abschnitts (per Where auf einer Tabelle oder eine Unterabfrage) schlecht bzw. falsch eingeschätzt werden kann.

Beispiel - @dT sei eine Datums/Uhrzeitvariable:

QUOTE Where A.Beginn = @dT


Wenn für die Spalte A.Beginn eine brauchbare interne Statistik existiert und dem Optimierer anhand dieser Statistik bekannt ist, daß die Spalte A.Beginn stark diskriminiert (die Werte also im besten Fall eindeutig sind) oder es sogar einen eindeutigen Index gibt, dann ist klar, daß dieser Where-Abschnitt nur wenige bzw. genau eine Zeile zurückliefert. Noch besser wäre es natürlich, wenn statt @dT eine Konstante eingesetzt wäre - aber das hieße ein Kompilieren bei jedem Aufruf mit entsprechenden Kosten, das will ich innerhalb von Server-Daten natürlich vermeiden (inzwischen diverse parallel arbeitende Kunden, pro Kunde teilweise 30 - 60 Nutzer).


QUOTE Where A.Beginn Between @dT_start And @dT_end


Hier können die Werte @dT_start und @dT_end beliebige Datumsausdrücke annehmen, dementsprechend ist gänzlich unklar, wieviele Zeilen dieser Ausdruck zurückgibt.

In Abhängigkeit von der Kardinalität solcher Unterabfragen wählt der Optimierer verschiedene Verknüpfungsstrategien zwischen Tabellen aus. Wenn es bsp. eine Abfrage der folgenden Logik gibt


QUOTE From TabelleA As A Left Join (Select ... Unterabfrage unbekannter Kardinalität) As B
On A.Spalte = B.Spalte


dann kann zu jeder Zeile der linken Tabelle nach der passenden Zeile rechts gesucht werden (Left Join - Operator) oder es kann das Merge-Verfahren genutzt werden: Beide Spalten A.Spalte / B.Spalte werden in einer virtuellen zweispaltigen Tabelle aufsteigend sortiert und daraus der Join gebildet.

Wenn klar ist, daß eine der Tabellen sehr wenige Zeilen zurückliefert, dann liegt es nahe, per direkter Suche zu arbeiten. Die Merge-Technik bleibt als Ausweg bei sehr großen bzw. unbestimmten Tabellen.

Praktisch brachte mich das auf die Idee, die Kardinalität der linken Tabelle per Top-Klausel einzuschränken. Also einfach


QUOTE Select Top 60 A._tbl_CalendarId, A.thisDay


Denn da es sich um eine Tabelle handelte, die bei der Ausgabe 'gedreht wird, so daß die Tage oben nebeneinander stehen, war die Gesamtbreite aufgrund der Browser ohnehin auf 8 Wochen = 56 Tage beschränkt. Sprich: Die @dT_start / @dT_end liegen immer 56 Tage auseinander, also kann man dem Optimierer auch mitteilen, daß von der linken Tabelle (die derzeit 40.000 Zeilen umfasst) maximal 60 Zeilen benötigt werden.

Ergebnis: Die Kompilierungszeit ging auf einen Wert unterhalb einer Sekunde zurück, die tatsächliche Ausführungszeit lag ohnehin in diesem Bereich.


Wahrscheinlich hat der Optimierer zu jeder Unterabfrage eine Merge-Verknüpfung mit entsprechendem internen Aufwand generiert. Bei 9 * 2 + 25 * 2 = 68 Unterabfragen kam dann schon einiges zusammen.
 
QUOTE (Jürgen Auer @ Mo 24.08.2009, 21:23)Wahrscheinlich hat der Optimierer zu jeder Unterabfrage eine Merge-Verknüpfung mit entsprechendem internen Aufwand generiert. Bei 9 * 2 + 25 * 2 = 68 Unterabfragen kam dann schon einiges zusammen.

Oder mit anderen Worten: der Optimierer hat, aufgrund der nicht verfügbaren Statistik (wegen Verwendung von Variablen), eine solche Anzahl von verschiedenen und/oder grossen Ausführungsplänen generiert, dass zur Suche des optimalen Planes sehr viel Zeit verbraucht wurde.

Das dürfte doch zu den "interessanteren" Erfahrungen gehören, die man bei der Arbeit mit Datenbanken macht. Da sieht man, dass DB-Systeme zwar mächtig sind, aber manchmal dennoch sehr detailliertes Wissen des Benutzers nötig ist, um solche Performance-Probleme in den Griff zu bekommen - bzw. dass vertiefte Kenntnisse über die Implementierung von DB-Systemen hilfreich sind, um solche Probleme zu antizipieren.
 
Sie müssen sich einloggen oder registrieren, um hier antworten zu können.
Zurück
Oben