SQL optimieren

[Dumpfbacke]

Hallo Leute ich könnte ein wenig Unterstützng von Euch gebrauchen. Ich habe hier ein Select welcher mehrer Minuten beötigt. Kann mir hier jemand beim Optimieren helfen ?
Ich habe hier zwei Tabelle in der Daten Liegen

Tabelle 1: 148192 Datensätze
Tabelle 2: 1768162 Datensätze

Als Ergebnis möchte ich alle Felder1 der Tabelle1 bei denen es in der Tabelle2 nicht einen Datenstz gibt mit dem selben Daten in den Felder1 und 2 wie in der Tabelle 1. Als Ergbnis kommen hier z.B. 10.000 Werte raus welche ich dann in eine Textdatei schreibe. Das ganze funktioniert ja, jeodch kann es schon mal 10 Minuten dauern bis das Ergebnis vorliegt.

Ich benutze hierzu diesen SQL

Delphi-Quellcode:

Select Tabelle1.Feld1
from Tabelle1
Left Outer Join Tabelle2 on  Tabelle1.Feld2 = Tabelle2.Feld2
 and Tabelle1.Feld1 = Tabelle2.Feld1
where Tabelle2.Feld2 is null

Es liegt jewals ein Index auf Tabelle1.Feld1, Tabelle1.Feld2, Tabelle2.Feld1, Tabelle2.Field2

Kennt hier jemand eine Lösung damit es schneller geht :idea: ? 10 Minuten sind etwas viel aus meiner sicht oder ?

Danke Tanja :stupid:

[Furtbichler]

Hast Du auch einen kombinierten Index (Feld1, Feld2) in beiden Tabellen? Das sollte dann optimal sein, denke ich.

[jobo]

Unique Index wäre vlt auch toll, wenn möglich.
Die unique - oder kombi - Indizierung könnte allerdings negative Auswirkungen auf andere Statements haben.
Mal den Ausführungsplan fragen.

Die Where Bedingung in Deinem Statement enthält wahrscheinlich ein Tipfehler oder (Feld2 ist Teil des Joins)?

[Furtbichler]

Zitat:

Zitat von jobo (Beitrag 1216256)

Die Where Bedingung in Deinem Statement enthält wahrscheinlich ein Tipfehler oder (Feld2 ist Teil des Joins)?

Nein, das ist korrekt. Was bring 'Unique' für einen Geschwindigkeitsvorteil?

[jensw_2000]

Ich denke es müsste schon mal etwas schneller gehen, wenn Du die "is NULL" Prüfung für Tabelle2.Feld2 mit als JOIN Bedingung setzt und Tabelle1.Feld2 in der Where Klausel auf NULL Werte prüfst.

Code:

Select 
  Tabelle1.Feld1
from 
  Tabelle1
Left Outer Join Tabelle2 
  on  Tabelle2.Feld1 = Tabelle1.Feld1
  and Tabelle2.Feld2 is null
Where 
  Tabelle1.Feld2 IS NULL

Der Ausführungsplan (ohne Indizes) sollte bei dieser Abfrage wie folgt aussehen:
1. Hole alle Datensätze aus Tabelle 1 bei denen Feld 2 einen NULL Wert enthält
2. Joine alle Datensätze aus Tabelle 2, in denen Feld 2 Null ist und Feld1 eine Entsprechung in der (im Schritt 1 reduzierten Datenmenge von) Tabelle1 hat.

Damit wird die "logische Datenmenge" während der Ausführung deutlich kleiner.
Indizes auf Tabelle1.Feld2, Tabelle2.Feld1 und Tabelle2.Feld2 sind aber dennoch wichtig.

Bei deiner originalen Abfrage muss der SQL Server wegen der "Tabelle2" Where Bedingung erst den inneren Teil abarbeiten, bevor das Where greifen kann. Im Join werden die NULL Werte in Tabelle2.Feld2 nicht als Bedingung gesetzt. Daher muss der SQL Server hier sehr viele Datensätze aus Tabelle2 "pauschal" mit Tabelle1 joinen um sie dann später wieder reduzieren zu können.

[Furtbichler]

Du scheinst nicht verstanden zu haben, was der Fragesteller möchte und wie das mit dem 'WHERE X is null' funktioniert, auf jeden Fall sind deine Ausführungen falsch.

Also: Zwei Tabellen A und B, jeweils eine Spalte ID. A=(1,2,3) B=(2,3,4).

SQL-Code:

select * from A left join B on A.ID=B.ID

liefert:

Code:

A.ID B.ID
1    NULL
2    2
3    3

Warum? Der SELECT-Befehl lässt sich übersetzen mit: Alles aus A (LEFT JOIN) und falls B passt (ON A.ID=B.ID), auch von B.
Nun möchte er nur die '1' sehen, denn die ist in B nicht vorhanden: Also

SQL-Code:

WHERE B.ID is NULL

.

[jobo]

Korrekt, ich hab nicht richtig hingeschaut.
Ja und Unique könnte eine höhere Selektivität bringen bzw. den Optimizer dazu bringen den Ausführungsplan zu ändern.
Kein Range Index Scan halt, wie gesagt, der Ausführungsplan würde es verraten.

[jensw_2000]

Ich denke du solltest dich ggf. mit den Basics von SQL Ausführungsplänen auseinandersetzen, bevor du anderen unterstellst, dass sie irgend etwas nicht verstanden haben.

Code:

WHERE B.ID is NULL

funktioniert. Ganz klar.
Allerdings ist es suboptimal, weil der SQL Server weder die innere noch die äußere Abfrage optimiert ausführen kann.
Wenn B.ID NULL sein soll, dann kann man dies um ein vielfaches effektiver als Condition für den Join festlegen.
Schau Dir mal SQL Profiling Tools an und vergleiche die Ausführungspläne beider Varianten.

PS:

Zitat:

Zitat von Furtbichler (Beitrag 1216275)

Du scheinst nicht verstanden zu haben, was der Fragesteller möchte und wie das mit dem 'WHERE X is null' funktioniert, auf jeden Fall sind deine Ausführungen falsch.

Oh Sorry. Ich dachte das war auf mich gemünzt. :oops:

[Furtbichler]

Jens, meine Antwort unter Deiner war mißverständlich, aber Du warst schon gemeint. Hach ich bin eben etwas ruppig, manchmal. Und kann nicht richtig lesen. Ich hab deine Ausführungen noch nicht einmal richtig gelesen, aber schon verteufelt.

Aber bei mir funktioniert dein Vorschlag nicht. Ich habe zwar nur einen SQL-Server (TM von MS), aber dieses Skript geht nicht:

SQL-Code:

--- Temporäre Tabellen anlegen
select 1 as ID1, 1 as ID2 into #A
union select 1,2
union select 1,3
union select 2,1
union select 2,2

select 1 as ID1, 1 as ID2 into #B
union select 1,2
union select 1,3
union select 2,1
union select 2,3

--- Vorschlag #1 (funktioniert)
select * 
from #A a 
     left join #B b 
       on a.ID1=b.ID1 
      and a.ID2=b.ID2 
where b.ID2 is null

--- Vorschlag #2 (deiner?)
Select * 
from #A a 
  left join #B b 
    on a.ID1=b.ID1
   and b.ID2 is null
where b.ID2 is null

Wenn man nicht weiter weiß (bei Performanceproblemen) hilft mir manchmal, einfach einen anderen ansatz zu nehmen.

Code:

select * from Tabelle1
where not exists (
  select * from Tabelle2 
   where Tabelle1.Feld1=Tabelle2.Feld1 
     and Tabelle1.Feld2=Tabelle2.Feld2
   )

Das kann zum identischen Queryplan führen, oder etwas komplett anderes bewirken. Wichtig hier scheint mir, einen kombinierten Index auf Tabelle2 (Feld1+Feld2) zu haben, oder?

[jensw_2000]

Zitat:

Zitat von Furtbichler (Beitrag 1216281)

Jens, meine Antwort unter Deiner war mißverständlich, aber Du warst schon gemeint. Hach ich bin eben etwas ruppig, manchmal.

Du hast mir beim Frühstückskäffchen einen kalten Lappen ins Gesicht geklatscht. Schuft! :wink:

SQL-Code:

where b. ...

führt im besten Fall zu einen Full Index Scan in A und B. Im schlimmsten Fall wird die Abfrage intern als Cross Join ausgeführt.

Nein. Ich schrieb ...

SQL-Code:

--- Vorschlag #2 (meiner!)
Select * 
from #A a 
  left join #B b 
    on b.ID1=a.ID1
   and b.ID2 is null
-- nicht von mir where b.ID2 is null
where a.ID2 is null

Der Code wird in der Regel optimierter ausgeführt, wenn die Where Klausel den äußeren "Select" filtert und die Datenmenge der Joins mit Conditions dezimiert wird.


Dein MSSQL testet versucht mal den inneren, mal den äußeren Teil als erstes auszuführen und merkt sich den optimaleren Weg für alle weiteren Abfragen. Ob der Firebird des Fragestellers dies auch kann weiß ich leider nicht.

[Blup]

Zitat:

Zitat von Furtbichler (Beitrag 1216252)

Hast Du auch einen kombinierten Index (Feld1, Feld2) in beiden Tabellen? Das sollte dann optimal sein, denke ich.

In Tabelle1 wird zumindest in dieser Abfrage überhaupt kein Index benötigt, es werden sowieso alle Datensätze berücksichtigt.

Nach meiner Erfahrung ist der kombinierte Index in Tabelle2 (Feld1, Feld2) die einzige sinnvolle Lösung.
Der Index, der nur Feld1 berücksichtigt, kann dann aber entfallen.

Wahrscheinlich müssen die Felder auch in der Join-Bedingung der Abfrage in der selben Reihenfolge wie im Index auftauchen.

Code:

  Select          Tabelle1.Feld1
  from            Tabelle1
  Left Outer Join Tabelle2 on Tabelle1.Feld1 = Tabelle2.Feld1
                          and Tabelle1.Feld2 = Tabelle2.Feld2
  where Tabelle2.Feld2 is null

Im Plan darf dann nur noch dieser kombinierte Index auftauchen.

[tgvoelker]

EDIT: Furtbichler hat recht.

Code:

Select Tabelle1.Feld1
from Tabelle1
Left Outer Join Tabelle2 on Tabelle1.Feld2 = Tabelle2.Feld2
 and Tabelle1.Feld1 = Tabelle2.Feld1
where Tabelle2.Feld2 is null

Zitat:

Als Ergebnis möchte ich alle Felder1 der Tabelle1 bei denen es in der Tabelle2 nicht einen Datenstz gibt mit dem selben Daten in den Felder1 und 2 wie in der Tabelle 1.

Kombinierten Index machen in beiden Tabellen und das müßte es sein. Ggf. kannst Du auch einen referentiellen Constraint erstellen, könnte einen Zacken schneller sein.

Die Query kombiniert alle Tupel aus Tabelle 1 mit allen Tupeln aus Tabelle 2, bei denen die Kombination der Felder Feld1 und Feld2 in beiden Tabellen die gleichen Daten haben. Damit nun die WHERE-Klausel alle Tupel aus Tabelle 1 liefert, für die mit dieser Bildungsvorschrift kein Datensatz in Tabelle 2 zugeordnet werden kann, darf Tabelle2.Feld2 keine Nullwerte akzeptieren.

Jensw_2000:

Das hier:

Code:

Select
  Tabelle1.Feld1
from
  Tabelle1
Left Outer Join Tabelle2 
  on Tabelle2.Feld1 = Tabelle1.Feld1
  and Tabelle2.Feld2 is null
Where
  Tabelle1.Feld2 IS NULL

macht was anderes. Es kombiniert alle Tupel aus Tabelle 1 mit allen Tupeln aus Tabelle 2, für die Feld1 jeweils die gleichen Daten enthält und Feld2 in Tabelle 2 NULL ist. Die Where-Klausel filtert lediglich die Tupel aus Tabelle 1, für die Feld2 NULL ist.

Deine Bildungsvorschrift verletzt also die implizite Voraussetzung, daß Feld2 in Tabelle2 keine Nullwerte akzeptiert. Stattdessen gehst Du davon aus, daß Nullwerte zugelassen sind - denn mit Deiner WHERE-Klausel schmeißt Du ja alle Tupel aus Tabelle 1 weg, die in Feld2 keinen NULL stehen haben.

Genaugenommen wird die Ergebnismenge von Dir alle die Tupel aus Tabelle 1 enthalten, für die die Kombination Feld1/Feld2 in Tabelle 2 vorhanden ist und Tabelle1.Feld2 NULL ist.

[Furtbichler]

Zitat:

Zitat von Blup (Beitrag 1216285)

Zitat:

Zitat von Furtbichler (Beitrag 1216252)

Hast Du auch einen kombinierten Index (Feld1, Feld2) in beiden Tabellen? Das sollte dann optimal sein, denke ich.

In Tabelle1 wird zumindest in dieser Abfrage überhaupt kein Index benötigt, es werden sowieso alle Datensätze berücksichtigt.

Nun ja, bei manchen RDBMS würde mit einem Index über beide Felder die Tabelle nicht gelesen werden müssen, da die zu liefernden Informationen gänzlich im Index stecken, welcher ja wesentlich effektiver geladen werden kann.

[Dumpfbacke]

[QUOTE=Furtbichler;1216454][QUOTE=Blup;1216285]

Zitat:

Zitat von Furtbichler (Beitrag 1216252)

Hast Du auch einen kombinierten Index (Feld1, Feld2) in beiden Tabellen? Das sollte dann optimal sein, denke ich.

Dieses wollte ich nun versuchen leider kann ich aus mir unbekannten Gründen kein Index mehr anlegen.
Es kommt einen Fehlermeldung:

ISC 335544985
operations system directive CreateFile failed
Das System kann den angegeben Pfad nicht finden.

So etwas habe ich noch nicht gehabt. Ich kommte eigentlich immer einen Index anlegen. Wie kann den so etwas sein ? Was mache ich denn falsch ? Ich versuche es jetzt schon seit einigen Tagen.

Dumpfbacke

[Furtbichler]

Google doch einfach mal.

[Dumpfbacke]

Zitat:

Zitat von Furtbichler (Beitrag 1216759)

Google doch einfach mal.

Dann bekomme ich dieser Ergebnis: isc_out_of_temp_space

Wie kann ich den das beseitigen ?
Fehlt hier Hauptspeicher oder sollte es an der Festplatte liegen ?
Am Rechner wo der Server läuft oder am Client wo ich versuchen den Index zu erstellen ?

Tanja

[HeZa]

Um noch ein paar Abfragevarianten ins Spiel zu bringen:

Code:

SELECT t1.Feld1, t1.Feld2 FROM
  Tabelle1 t1
WHERE
  (t1.Felds1, t1.Feld2) NOT IN (SELECT t2.Feld1, t2.Feld2 FROM Tabelle2 t2)

oder auch

Code:

SELECT t1.Feld1 FROM
  Tabelle1 t1
WHERE
  NOT EXISTS (SELECT * FROM Tabelle2 t2 WHERE t2.Feld1 = t1.Feld1 AND t2.Feld2 = t1.Feld2)

(beide Statements jetzt aus dem Kopf zusammengeschraubt und ungetestet)

Wenn du den WHERE-Teil t2.Feld1 = t1.Feld1 AND t2.Feld2 = t1.Feld2 oft brauchst könnte ein Index auf beide Felder (zumindestens in einer der beiden Tabellen) helfen (abhängig noch von anderen Faktoren wie Häufigkeit von NULL-Werten und Häufigkeiten der verschiedenen Feldwerte).

[tgvoelker]

Zitat:

Zitat von Dumpfbacke (Beitrag 1216789)

Zitat:

Zitat von Furtbichler (Beitrag 1216759)

Google doch einfach mal.

Dann bekomme ich dieser Ergebnis: isc_out_of_temp_space

Wie kann ich den das beseitigen ?
Fehlt hier Hauptspeicher oder sollte es an der Festplatte liegen ?
Am Rechner wo der Server läuft oder am Client wo ich versuchen den Index zu erstellen ?

Tanja

Hallo Tanja,

würde auf den Rechner tippen, auf dem der Firebird Server läuft. Dort die festplatte voll?

Wenn es sich um ein Firebird-spezifisches Problem handelt, dann kann ich Dir schwerlich weiterhelfen, nehme ich nicht.