SQL select optimieren

[Squicky]

Hallo


Beide folgende sql select Befehle liefert das richtige und gleiche Ergebnis.
Die Tabellen t1 und t2 haben die gleichen Spalten (id, a, b, …, g).
Gesucht sind die Zeilen in t1, die nicht in t2 vorhanden sind.
Das erste Beispiel vergleich die zwei Tabellen nur mit der Spalte a und braucht ca. 1 bis 2 Sekunden ; das Ergebnis sind 0 Zeilen.
Das zweite Beispiel vergleich die zwei Tabellen mit allen Spalte und braucht mind. 20 Sekunden ; das Ergebnis sind 0 Zeilen.

SELECT t1.*
FROM t1
LEFT OUTER JOIN t2
ON t1.a = t2.a
WHERE t2.id IS NULL

SELECT t1.*
FROM t1
LEFT OUTER JOIN t2
ON t1.a = t2.a
AND t1.b = t2.b
AND t1.c = t2.c
AND t1.d = t2.d
AND t1.e = t2.e
AND t1.f = t2.f
AND t1.g = t2.g
WHERE t2.id IS NULL

Das zweite Beispiel braucht wahrschein so viel länger, weil alle Spalten vergleichen werden.
Oder was könnte der Grund für die längere Laufzeit von Beispiel 2 sein?

Wenn dies so ist, warum werden überhaupt Spalten b bis g im zweitem Beispiel verglichen? Denn Spalte a ist immer ungleich, somit sollten die weiteren Spalten gar nicht erst verglichen werden müssen.

Optimierungsversuch:
Wie kann man das zweite Beispiel ändern, so dass Spalten b nur verglichen wird, wenn Spalten a gleich sind?

Danke

P.S. Benutzt wird Microsoft SQL 2016

 

[violentviper]

Sollen wir jetzt hier deine Hausaufgaben machen?

 

[Yuuri]

Setz ein EXPLAIN davor und sieh dir den Ausführungsplan an. Dann weißt du ob bspw. ein Full Table Scan (Worst Case) notwendig ist um aufs Ergebnis zu kommen.

 

[xmarsx]

EXPLAIN ANALYSE SELECT...

Durch das ANALYSE wird nicht nur geschätzt was passiert (basierend auf den Statistiken die die Datenbank intern pflegt) sondern die Query wird tatsächlich ausgeführt.

Außerdem kann man auch die Mengenoperatoren EXCEPT, INTERSECT und UNION verwenden (MySQL kann es nicht abgesehen von UNION).
Andere aber sehr wohl...:

• PostgreSQL: https://www.postgresql.org/docs/10/static/sql-select.html
• SQL Server: https://docs.microsoft.com/de-de/sq...d-intersect-transact-sql?view=sql-server-2017
• ...

Beispiel (Alle Einträge aus t1, die nicht in t2 enthalten sind):
SELECT t1.*
FROM t1
EXCEPT
SELECT t2.*
FROM t2

Außerdem "Use the index Luke" (https://use-the-index-luke.com/de)!
Auf welchen Spalten ist ein ein Index? Am besten das komplette CREATE Statement mitliefern und wieviele Daten in den Tabellen sind, weil es sonst nur ein Blick in die Glaskugel ist.

 

[Corto]

ich würde so aus dem bauch raus auch sagen das spalte a indexiert ist und daher ein reines select auf spalte a schneller ist als ein join über alle attribute.

ich schreib für sowas ein not-exists aber ich hab auch nur oracle-dbs

 

[mental.dIseASe]

Irgendwie verstehe ich die Frage nicht. Bei dem einen wird viel mehr gemacht als bei dem anderen. Da ist doch klar, dass das länger dauert. Das zweite Beispiel ist auch komisch: in der Regel verdängelt man 'ne einzelne ID mit ner FK-Spalte und vergleicht da nicht noch Schrott und Schrauben miteinander.

Sieht sehr akademisch/nach Hausaufgaben aus.

 

[Squicky]

Auf beiden Tabellen ist und soll kein Index vorhanden sein.

In welcher Reihenfolge werden die 7 Filter-Bedingungen der Reihe nach pro Tabellenzeile geprüft?
Wie kann mann die Reihenfolge nachschauen?

Falls, wie angegeben, zuerst a, dann b, dann c, ... geprüft wird: Warum braucht Beispiel 2 so viel länger?
Denn der SQL Server sollte doch schon nach der Prüfung von a erkennen, dass die das Gesamtergebnis nur negativ ist ( false and ... = false ).
Bzw: Denn der SQL Server sollte doch schon nach der Prüfung von a erkennen, dass die weiteren Prüfungen von b bis g unnötig sind.
Da die Laufzeit von Beispiel 2 aber sehr viel schlechter ist, glaube ist, dass immer alle 7 Filter-Prüfungen durchgeführt werden.

Kann man dies ändern? Wie?

P.S.
Dies ist keine Hausaufgaben.
Mit diesem Beispiel und dieser Frage möchte ich nur verstehen, wie die MS SQL Server arbeitet und wie man diese Arbeit beeinflussen (optimieren) kann.

 

[Serious_Sam]

Der WHERE Ausdruck wird immer zuerst ausgewertet, ist dieser komplex, so braucht es viel Zeit.

Ergänzung (1. Juni 2018)

Sowas wie ne lazy evaluation von AND-Verknüpfumgen klappt hier wohl nicht, sonst würde es beim ersten false beendet sein.

 

[Squicky]

Der WHERE Ausdruck wird immer zuerst ausgewertet, ist dieser komplex, so braucht es viel Zeit.

Deine Meinung ist also, dass der SQL Server die Abfrage nicht optimiert?
(Optimieren: WHERE Ausdruck hat mehrere AND-Teile, wenn der erste AND-Teil false ist, dann werden die weiteren AND-Teile nicht geprüft)

 

[Serious_Sam]

Jo genau.

 

[Squicky]

Jo genau.

Ist der MS SQL Server wirklich so dumm ?

Kann man die beeinflussen?

 

[ayngush]

Nein ist er nicht.
Bei einem Outer Join mit mehreren Bedingungen müssen immer alle Bedingungen geprüft werden, da das Ergebnis sonst inkonsistent sein könnte. Das DBMS spielt dabei keine Rolle.

Zur Optimierung schau dir den Beitrag von @xmarsx an.

Pack da Indizes drauf und du beschleunigst das Ganze um den Faktor n.
Benutze EXCEPT dafür, weil das wurde extra für diese Aufgabe geschaffen!
(LEFT/RIGHT) OUTER JOIN ... ON ... WHERE dbo.n IS NULL war schon immer eine Performancebremse und eher eine Hilfskrücke. Anders ging es aber nicht...

Frage des Interesses halber: Warum soll kein Index vorhanden sein?