Sql-Hardcore: Richtig sortieren

[Jürgen Auer]

Vor ein paar Tagen hatte ich mir mal gesagt, ich müsse mal endlich in Server-Daten eine Funktion einbauen, die mir lang laufende Abfragen direkt meldet.

Nicht auf jede Abfrage, bsp. lohnt sich das nicht bei Abfragen, die einzelne Zeilen holen. Ebenso lohnt es sich üblicherweise nicht bei einigen typischen Suchabfragen und beim Holen von Auswahllisten.

Aber es gibt einen Aufruf, der alle 'richtigen Suchen' in einer Seite zusammenfaßt - und alles, was da über 0,1 Sekunden ist, soll mal gemeldet werden.

Im Alltag werde ich den Wert hochsetzen - aber so am Wochenende ist das mal ganz interessant.


Das heute mittag eingebaut, technisch ist das ein banaler Timer.

Prompt eine 'Uralt-Abfrage' bei einem Kunden entdeckt. Eigentlich mit 0,28 Sekunden noch harmlos, die gesamte Seite war in 0,5 Sekunden geladen. Trotzdem natürlich die Neugier: Läßt sich das beschleunigen?

Die Logik ist eigentlich ziemlich simpel:

QUOTE Select A.Id,
'Textkonstante' As Basis,
A.Bezeichnung_soft + '.' + Cast(A.ID as nvarchar(15)) As Bezeichnung_soft,
'/' As Trenner_1,
B.Aufrufname_soft + '.' + Cast(B.andereId as nvarchar(15)) As Aufrufname_soft,
'TextKonstante' + A.Bezeichnung_soft + '.' + Cast(A.Id as nvarchar(15)) + '/' + B.Aufrufname_soft + '.' + Cast(B.andereId as nvarchar(15)) + ''' ' +
Case When A.neues_Fenster = 1 Then ' target=''_new''' Else '' End +
Case When A.nofollow = 1 Then ' rel=''nofollow''' Else '' End +
Case When A.Titel Is Null Then '' Else ' title=''' + A.Titel + '''' End As Ergebnis_long

From ersteTabelle As A, zweiteTabelle As B
Where ... noch ein paar mögliche Filter per Parameter, die auch Null sein können

Order By Ergebnis_long



Also hier eine absichtliche Komma-Verknüpfung zweier Tabellen, weil jede Zeile mit jeder anderen Zeile kombiniert werden soll (normalerweise bevorzuge ich dezidierte Join-Anweisungen). Dann ein zusammengestrickter langer String, nach dem sortiert wird. Technisch sollen da Links zusammengestrickt werden, die sich nun leicht per Copy+Paste anderswo einfügen lassen.

Ok, das ganze wird nochmals maskiert, in eine Unterabfrage gepackt - aber das war nicht das Problem.

Wie läßt sich die Abfrage dramatisch beschleunigen?

PS: Das Beispiel zeigt schön, wie man - durchaus 'gedankenlos' - schnell eine Abfrage runtertippt, diese funktioniert auch - und erst sehr viel später merkt man, daß das ja auch ganz anders gehen könnte.

 

[Jürgen Auer]

Ein paar Hinweise:

(1) Als ich die Abfrage damals geschrieben habe (war im Rahmen der Ersteinrichtung, vor fast zwei Jahren), da gab es in den beiden Tabellen jeweils vielleicht fünf Zeilen, also 5 * 5 maximal 25 Zeilen bei der Komma-Verknüpfung.

Inzwischen haben beide Tabellen zwischen 170 und 200 Zeilen, so daß sich insgesamt ungefähr

180 * 200 = 36.000 Zeilen

zum Sortieren ergeben. Das ist natürlich ein ganz anderer Aufwand, da Sortieralgorithmen im besten Fall eine Laufzeit n haben - 100 Zeilen zu sortieren dauert 10 mal so lange wie 10 Zeilen zu sortieren.

Bei 25 -> 25000 ist also auf jeden Fall mit einer Verlängerung zu rechnen.

(2) In dem Fall verlängert sich die Seitenverarbeitung auf etwa 0,3 - 0,5 Sekunden. Gut, nichts dramatisches, kaum merkbar. Aber man läuft natürlich Gefahr, daß solche Abfragen unbemerkt existieren - und die Seite irgendwann doch mal unerträglich langsam wird. Der Kunde schiebt das auf die wachsende Zahl der Datensätze, in Wirklichkeit wurde bei der Erstellung der Abfrage geschlampt

(3) Praktisch wird die Seite natürlich mit einer Top 100 - Klausel genutzt, natürlich werden nicht 36.000 Datensätze ausgegeben. Aber das ändert nur begrenzt etwas am Problem, da für die Sortierung alle 36.000 Strings zusammengebaut und sortiert werden müssen, um den ersten Eintrag zu ermitteln.

 

[Jürgen Auer]

So, ich muß das Rätsel mal noch auflösen:

Alt:

QUOTE Select ...
'TextKonstante' + A.Bezeichnung_soft + '.' + Cast(A.Id as nvarchar(15)) + '/' + B.Aufrufname_soft + '.' + Cast(B.andereId as nvarchar(15)) + ''' ' +
Case When A.neues_Fenster = 1 Then ' target=''_new''' Else '' End +
Case When A.nofollow = 1 Then ' rel=''nofollow''' Else '' End +
Case When A.Titel Is Null Then '' Else ' title=''' + A.Titel + '''' End As Ergebnis_long

From ...

Order By Ergebnis_long


Der Nachteil dieser Lösung ist rückblickend eigentlich offenkundig: Erst werden alle Zeilen paarweise miteinander verknüpft, es müssen über 30.000 Strings konstruiert werden - dann wird nach dieser Stringverknüpfung sortiert.

Nur: 'Textkonstante' ist immer gleich - also raus. Das Sortierergebnis hängt doch offensichtlich 1:1 von der Sortierung nach A.Bezeichnung_soft, A.Id, B.Aufrufname_soft, B.andereId ab.

Also kann man doch gleich nach diesen Spalten sortieren:


Folglich:


QUOTE Select ...
'TextKonstante' + A.Bezeichnung_soft + '.' + Cast(A.Id as nvarchar(15)) + '/' + B.Aufrufname_soft + '.' + Cast(B.andereId as nvarchar(15)) + ''' ' +
Case When A.neues_Fenster = 1 Then ' target=''_new''' Else '' End +
Case When A.nofollow = 1 Then ' rel=''nofollow''' Else '' End +
Case When A.Titel Is Null Then '' Else ' title=''' + A.Titel + '''' End As Ergebnis_long

From ...

Order By A.Bezeichnung_soft, A.Id, B.Aufrufname_soft, B.andereId


Nur die Sortierung austauschen.

Ergebnis: Ursprüngliche Abfrage bei interner Ausführung (nur einmalig) ~ 0,1 Sekunden. Wenn man das ohne TOP-Klausel intern ausführt, etwa 0,7 Sekunden, bei etwa 35.000 Datensätzen.

Neue Abfrage: 0,01 Sekunden mit TOP 100, etwa 0,45 Sekunden ohne TOP.

Man sieht also: Über sämtliche 35.000 Zeilen bringt das 'nur' etwa eine Verbesserung von 0,7 auf 0,45 Sekunden. Die Top 100 - Einschränkung benötigt überdurchschnittlich viel an Zeit - 100 Datensätze von 35000 dürften theoretisch nur 0,002 Sekunden brauchen, stattdessen etwa 0,1 Sekunden. Wenn man das von außen her ausführt, muß die Zahl mindestens verdoppelt werden, deshalb die 0,28 Sekunden im Einstiegsbeitrag.

Die neue Sortierung ist dagegen bei der TOP-Einschränkung doch erheblich schneller - von 0,1 auf 0,01, also 90 %.

Sieht man sich die generierten Ablaufpläne an, dann sieht man die Wirkung:

Bei Abfrage 1 wird erst zusammengefügt und ganz am Ende sortiert.

Bei Abfrage 2 werden die Tabellen intern sortiert und erst dann zeilenweise zusammengefügt, so daß das Gesamtergebnis sofort sortiert ist. Bei TOP 100 kann dieser Prozess des Zusammenfügens beider Tabellen nach 100 Zeilen abgebrochen werden.


Analog wird ja auch ein Join zwischen zwei Tabellen ausgeführt, falls es sich um eine Basis-Detailtabelle handelt und falls die Bezugsspalte in der Detailtabelle indiziert ist. Dann werden die beiden Indices sortiert miteinander kombiniert, so daß auch das Endergebnis in dieser Reihenfolge sortiert ist.

Bei dem obigen Beispiel 'sieht' der MS-Abfrageanalysierer, daß es günstiger ist, die Sortierungen zuerst, vor dem Zusammenfügen, auszuführen.