Performance bei Join mit Order by

[Rossi]

Hi!<br>
Ich habe ein problem mit folgendem Szenario:<br>
Aus zwei Tabellen (master, detail) werden per Join Sätze selektiert.<br>
Die Verknüpfung findet über inizierte Felder statt.<br>
SQL sieht also ungefähr so aus:<br>
<br>
select * from master, detail<br>
where master.id = detail.id<br>
plan join (master natural, detail index (detail_id_index))<br>
<br>
Das klappt soweit auch prima. Master hat ca. 1000 Sätze, Detail ca. 120000.<br>
Die Performance ist bis dahin super.<br>
Danach soll aber das ergebnis sortiert werden.<br>
Sobald ich aber nun ein <br>
order by sortfield<br>
hinzufüge, wird das ganze UNDENDLICH langsam.<br>
Auf sortfield liegt natürlich auch ein Index!<br>
Kann es sein, dass die Indexinformationen durch das Join bei dem<br>
order by nicht mehr beruecksichtigt werden? <br>
Ein order by auf jede einzelne Tabelle ist naemlich wieder rasend schnell!<br>
Hilefe?<br>

[Andreas Kosch]

Hallo,

die Frage, ob ein Index vom InterBase-Optimizer bei Ermitteln des Zugriffspfades verwendet wird, kann man sich vom InterBase beantworten lassen: <br>
1. InterBase-Tool <i>InterBase Interactive SQL</i> starten. <br>
2. Über <i>Session | Basic Settings</i> wird die Checkbox <b>Display Query Plan</b> aktiviert. <br>
3. Im Ausgabefenster zeigt das Tool dann den vom Optimizer gewählten Zugriffspfad an.

Um die Ausführungsgeschwindigkeit zu optimieren, würde ich folgendermassen vorgehen: <br>
1. Eigene PLAN-Anweisung streichen <br>
2. Den JOIN in der SQL92-Syntax formulieren (wenn eine aktuelle InterBase-Version verwendet wird).

Alte SQL89-Syntax:
<pre>
select *
from master, detail
where master.id = detail.id
</pre>

Neue SQL92-Syntax:
<pre>
select *
from master JOIN detail ON master.id = detail.id
</pre>
Auch wenn es lt. Theorie keine Rolle spielen sollte, hilft unter bestimmten Umständen das "Vertauschen" der Tabellenreihenfolge für die JOIN-Deklaration

[Rossi]

Tja, diese Antwort geht ein bischen an meinem Problem vorbei<br>
Es geht ja nicht primaer darum, das select schnell zu machen.<br>
Das habe ich mit dem expliziten PLAN ja hingekriget.<br>
Natuerlich habe ich den klassischen join, den "modernen" join<br>
und den "geplanten" join verglichen. Die version mit PLAN war <br>
definitiv die schnellste.<br>
Aber die Frage, bezog sich ja mehr auf das anschliessende order by!<br>
Wenn ich ein select * from detail order by id absetze, ist das<br>
superschnell. Das gleiche bei select * from master.<br>
Sobald ich aber die per join erzeugte ergebnismenge sortiere, wird<br>
das ganze unverhältnismässig langsam. Fast so, als wuerde ich die<br>
einzelnen Tabellen OHNE index sortieren. Das macht bei meinen 120000<br>
Datensätzen dann ca. 15 sekunden auf einem PII366/128Mb<br>
Der reine select (mit join) dauert aber nur einen Bruchteil einer Sekunde<br>
Und ein sortieren auf jeweils nur eine Tabelle geht halt auch in sekundenbruchteilen<br&gt

[Andreas Tengicki]

Wie sieht denn konkret dein Sortfield aus, oder sind es Sortfelder (je eins aus Master und eins aus Detail)?

Was passiert wenn Du eine View einsetzt

[Rossi]

In Master und Detail existiert je ein Feld für die Relation<br>
Also z.B. das Feld Master.MASTERID bzw. Detail.MASTERID, auf dem <br>
auch ein Index liegt.<br>
Zusätzlich gibt es in Detail ein Feld DetailSort, auf dem auch ein<br>
Index liegt. Genauer gesagt ist der Index fuer Detail zusammengesetzt <br>
aus Deatil.Masterid UND Detail.DetailSort.<br>
Ich habe aber auch schon versucht, einen Index (zusätzlich) auf<br>
jede einzelne (relevante) Spalte zu setzten. Gleiches Ergebnis.<br>
<br>
Das mit dem View schien mir dann auch die letzte Möglichkeit.<br>
Krankt aber auch an dem Problem der Sortierung. Der Select ist<br>
beim ersten Zugriff sehr langsam. Der View wird da halt erst aufgebaut<br>
Danach sind alle folgezugriffe sehr schnell. Aber eben nur unsortiert.<br>
Sobald ich ein select * from viewname order by sortfield absetze<br>
ist das ganze genau so langsam wie ohne view.<br>
Leider ist es halt nicht möglich auf einem view einen index zu erzeugen!<br&gt

[Andreas Kosch]

Hallo,

meine Antwort vom 14. sollte nur darauf hinweisen, das eine eigene PLAN-Anweisung immer dann nachgeprüft werden muss, wenn man am SELECT-Statement auch nur eine Zeile geändert/hinzugefügt hat. Wie sieht denn der vom InterBase gewählte Query Plan aus, <b>nachdem</b> die ORDER BY-Anweisung hinzugefügt wurde

[Rossi]

Tja, also ich bin jetzt zwar schneller, aber nicht glücklicher als vorher!<br>
Der Hinweis auf den PLAN war sehr sinnvoll. Damit habe ich nämlich<br>
herausbekommen, dass mein Statement nicht optimal ist. Ich habe nämlich<br>
im order by nicht angegeben, WOHER die spalten kommen. Also in der Form:<br>
order by idfeld, sortfeld<br>
Wichtig ist aber (damit der index benutzt wird) die Variante<br>
order by master.idfeld, master.sortfeld<br>
Erst so ist auch der Plan optimal. Schade nur, dass das Statement<br>
nur schnell ist, wenn ich den plan explizit mit angebe.<br>
Ohne plan, aber mit gleicher SQL-Syntax ist die Abfrage langsamer!<br>
Das ist zwar für den Fall, daß nur Interbase zum Einsatz kommt zu<br>
tollerieren, aber sobald sich etwas an der Struktur der Datnebank <br>
ändert oder gar ein Umstieg auf einen anderen SQL-Server geplant ist, <br>
muß wieder Hand an den Code gelegt werden. Und das ist bisweilen<br>
Fehlerträchtig...<br>
Trotzdem erst mal ein dickes Danke schön!!!<br&gt

[Andreas Tengicki]

Das mit der explizieten Angabe ist interessant und stützt die von mir intuitv verwendete Form von SQL. Danke.

Das zweite ist logisch. Denn ein jeder SQL-Server braucht für die Analyse und Optimierung des Statements immer deutlich mehr Zeit als für die eigentliche Ergebnisermittlung. Und durch die Plan Angabe nimmtst Du ihm viel Arbeit ab.

Optimales kompatibles SQL gibt es eigentlich nicht, nur zum Beispiel InterBase wertet where-Bedingungen von links nach rechts und Racle von rechts nach links aus. Schnell wird es, wenn jeweils die am meisten ausschließende Bedingung zu erst kommt. Und nun ...

[Andreas Kosch]

Hallo,

erschwerend kommt hinzu, das sich die Entscheidungen des Optimizers je nach Datenbankgrösse und Kardinalität/Selektivität der Indexspalten ständig ändern können. Dabei besteht bei der expliziten Angabe einer PLAN-Anweisung die Gefahr, das mit wachsender Datensatzanzahl der sich daraus ergebende Vorteil in einen Nachteil kippen kann. Man sollte in regelmässigen Abständen nachprüfen, ob die explizit übergebene PLAN-Anweisung beim aktuellen Datenbankinhalt noch wirksam sind.

Auf der Webseite von INTERBASE lag vor einiger Zeit eine TechDoc rum, in der die Vorgehensweise des Optimizers grob beschrieben wurde. Allein diese Grobbeschreibung war schon beeindruckend, hat aber auch nicht die Probleme bei der Entscheidungsfindung verschwiegen. Die Multigenerationen-Architektur des InterBase hat nicht nur Vorteile, sondern in Teilbereichen leider auch Nebenwirkungen (Stichwort SELECT MAX(Spalte1)...)

[Rossi]

Tja, da bleibt natürlich die Frage, wie erzähl ich's dem Kinde, resp. dem Kunden?<br>
Ich kann ja nicht einmal alle paar Wochen rausfahren und bei allen Kunden schauen, ob die Anwendung noch performant ist.<br>
Also bleibt einem nur eine grobe Schätzung des maximal zu erwartenden Volumens zu tätigen, und in diesem Intervall<br>
ein paar Messreihen zu machen, um dann einen SQL-Plan Plan aufzusetllen.<br>
Das ist dann aber wirklich kein RAD mehr!<br&gt