SQL Script optimieren damit es schneller geht

[Dumpfbacke]

Hallo Leute,
ich habe hier ein Problem mit einem Select und bekomme es einfach nicht hin bzw. es dauert zu lange.

Ich habe zwei Tabellen

Tabelle1 mit 477.379 Datensätzen
Tabelle2 mit 7.087.230 Datensätzen

In beiden Tabelle sind zwei Felder dessen Inhalt in beiden gleich ist. Ich möchte einfach alle Datensätze aus der Tabelle1 bei den in der Tabelle2 kein Datensatz vorhanden ist bei dem die beiden Feler übereinstimmen. Es geht zwar jedoch dauert es ca. 17 - 20 Minuten bis das Ergebnis vorliegt. Auf beiden Felden liegt natürlich ein Index.


Ich habe schon alles möglich versucht. Sogar schon mitteles Left Outer Join aus reiner Verzweifelung. Das ganze bringt nichts es dauert immer gleich lange. Ich gebe zu es sind einige Datensätze in der Datenbank. Das Ergebnis ist jedoch < 1000 beistens sogar < 300


Hier mal einer meiner vielen Select (Der mit Left Outer Join was nicht unbedingt Sinn macht)

Delphi-Quellcode:

Select Tabelle1.MasterNummer
from Tabelle1
Left Outer Join Tabelle2 on  Tabelle1.Staus = Tabelle2.Status
 and Tabelle1.CoNummer = Tabelle2.CoNummer
where Tabelle2.Status is null and 
Tabelle1.Status <> 'N' and Tabelle1.Storno is Null

Altuelle Daten : 160 Datensätze wurde gefunden und es hat etwas über 18 Minuten gedauert.

Kann mir jemadn hier helfen bzw gibt es überhaupt eine Lösung für mein Problem

Tanja

[hoika]

Hallo,

Tabelle1.Status <> 'N'

kann das ersetzt werden durch ein Tabelle1.Status = 'J'

Tabelle2 on Tabelle1.Status = Tabelle2.Status
and Tabelle1.CoNummer = Tabelle2.CoNummer
Tabelle1.Storno is Null

Damit hätten wir also 5 Felder und 5 Indizes, ja?

Mal die Indizes neu erzeugt (Alter Index I_X Inactive/Active)

Wie sieht der Query-Plan aus (z.B. mit IBExpert-Personal ermitteln?

[Dumpfbacke]

Zitat von hoika:

Hallo,

Tabelle1.Status <> 'N'

kann das ersetzt werden durch ein Tabelle1.Status = 'J'

Tabelle2 on Tabelle1.Status = Tabelle2.Status
and Tabelle1.CoNummer = Tabelle2.CoNummer
Tabelle1.Storno is Null

Damit hätten wir also 5 Felder und 5 Indizes, ja?

Mal die Indizes neu erzeugt (Alter Index I_X Inactive/Active)

Wie sieht der Query-Plan aus (z.B. mit IBExpert-Personal ermitteln?

Hallo es werden leider nicht alle 5 Indizes benutzt trotz das welche vorhanden sind. Ich habe Sie auch schon mal neu erzeugt mittels Recompute

Ein Status = J kann ich nicht setzte da es hier verschieden Inhalte gibt. Ich mache hier auch nun ein N um die Anzahl einzugrenzen da bei dem Status N kein Datensatz in der zweiten Tebelle sein kann.

Hier der Query-Plan

Plan
PLAN JOIN (Tabelle1 INDEX (IDX_Tabelle1_STORNO), Tabelle2 INDEX (IDX_STATUS))

------ Performance info ------
Prepare time = 592ms
Execute time = 18m 38s 933ms
Avg fetch time = 186.488,83 ms
Current memory = 15.854.640
Max memory = 15.867.656
Memory buffers = 800
Reads from disk to cache = 353.755
Writes from cache to disk = 0

[p80286]

ich hab einmal etwas umformatiert:

SQL-Code:

Select Tabelle1.MasterNummer
from Tabelle1
   Left Outer Join Tabelle2 on Tabelle1.Staus = Tabelle2.Status
                           and Tabelle1.CoNummer = Tabelle2.CoNummer
 where 1=1
  and Tabelle2.Status is null
  and Tabelle1.Status <> 'N' 
  and Tabelle1.Storno is Null

bis zum Join ist ja noch alles in Ordnung aber dann
Wenn Tabelle2.Status is null wahr ist, dann viel Spaß beim Joinen!
Wenn Tabelle1.Status <> 'N' wahr ist, dann kannst Du die vorherige Bedingung vergessen.

Das Da überhaupt etwas heraus kommt wundert mich.

Gruß
K-H

[Dumpfbacke]

Zitat von p80286:

ich hab einmal etwas umformatiert:
[CODE=SQL]

Das Da überhaupt etwas heraus kommt wundert mich.

Gruß
K-H

Ja es funktioniert genau so wie es soll. Es dauert halt recht lange. Hast du eventuell eine bessere Lösung für mein Problem. Wie gesagt ich suche alle Datensätze in der Tabelle1 wo kein Datensetz in der Tebelle2 vorhanden ist. Der Vergleich muss über zwei Felder erfolgen.

Danke Tanja

[nahpets]

Hätte da eine etwas schwerer lesbare Alternative:

SQL-Code:

select a.MasterNummer from
(
  select MasterNummer, Status, CoNummer from Tabelle1
  where Tabelle1.Status <> 'N'
    and Tabelle1.Storno is Null
) a,
(
  select Status, CoNummer from Tabelle2
  where Status is null
) b
where a.Status   = b.Status
  and a.CoNummer = b.CoNummer

Unter Oracle haben solche Konstrukte schonmal die eine oder andere Stunde eingespart.
Ist das eventuell bei FireBird ähnlich?

Warum dieser Weg?

Zuerst soll die Datenbank nur die Sätze aus Tabelle 1 holen, die wir benötigen.
Dann dito. für Tabelle 2.

Damit sind diese beiden Ergebnismengen erstmal auf das benötigte Mindestmaß "eingestampft".

Mit dem "außendrum" liegenden Statement müssen jetzt "nur noch" diese beiden Restmengen nebeneinander gelegt werden.
D. H.: Die Menge der miteinander zu vergleichenden Daten wird deutlich verkleinert gegenüber einem Left Outer Join über die Gesamtmenge beider Tabellen.

Es kann sein, dass das schneller wird, muss aber nicht so sein.

'nen Versuch sollte es aber wert sein.

Alternative:

SQL-Code:

select a.MasterNummer
from
(
  select MasterNummer, Status, Conummer from Tabelle1
  where Status <> 'N'
  and   Storno is Null
)
a
Left Outer Join
(
  select Status, CoNummer from Tabelle2 where Status is null
) b
on  a.Status   = b.Status
and a.CoNummer = b.CoNummer

Gleicher Grundgedanke:

Zuerst die Tabellen auf die jeweils höchstens benötigte Teilmenge reduzieren und dann nur noch diese beiden Teilmengen miteinander verknüpfen.

Da die erwartete Ergebnismenge sehr klein ist (im Vergleich zur Ausgangsmenge) dürfte es beim Zusammenfassen der Teilmengen nicht mehr relevant sein, dass dort kein Index vorhanden ist und damit "indexlos" verglichen werden muss.

Du schreibst

Zitat:

Tabelle1 mit 477.379 Datensätzen
Tabelle2 mit 7.087.230 Datensätzen

Wieviel Sätze ergibt

SQL-Code:

select count(*) from Tabelle1 
where Status <> 'N'
  and Storno is Null

bzw.

SQL-Code:

select Count(*) from Tabelle2 
where Status is null

Eventuell kann man aus den Mengenangaben noch weitere Rückschlüsse für eine alternative Abfragemöglichkeit ziehen.

[stifflersmom]

Moin,

ich würde es ähnlich machen.
Erst die Datenmenge reduzieren und aus beiden tabellen die in Frage kommenden Daten in temporäre Tabllen kopieren.
Anschließend dort en paar Indexe neu bauen und dann die Tabellen vergleichen.
Bei diesem Szenario könnte man in beiden temp.Tabellen auch enie zusätzliche Spalte anlegen, die die Werte aus Status und CoNummer "concated".
Diese Spalten dann mit einem Index versehen dann einfach joinen bz. left join und auf NULL in Tabelle 2 vergeichen.

Sollte dann deutlich schneller gehen...

[hoika]

Hallo,

Zitat:

Erst die Datenmenge reduzieren und aus beiden tabellen die in Frage kommenden Daten in temporäre Tabllen kopieren.

Genau das macht der Query-Optimizer ja schon selbst (eigentlich ...)

Aber mal zum Thema zurück:

Select Tabelle1.MasterNummer
from Tabelle1
Left Outer Join Tabelle2 on Tabelle1.Staus = Tabelle2.Status
and Tabelle1.CoNummer = Tabelle2.CoNummer
where Tabelle2.Status is null and
Tabelle1.Status <> 'N' and Tabelle1.Storno is Null

Nur der Form halber
Tabelle1.Staus = Tabelle2.Status
Das soll Tabelle1.Status heissen, oder ?

on Tabelle1.Staus = Tabelle2.Status
where Tabelle2.Status is null
Tabelle1.Status <> 'N'

Das Tabelle2.Status is null macht doch keinen Sinn.
Vorher einen Wert vergleichen Tabelle1.Staus = Tabelle2.Status
und dann is null ???
Vielleicht kommt der Optimizer dadurch durcheinander?

Ich hätte mal gern das komplette DDL der beiden Tabellen (incl. der Indizes) gesehen.


Tabelle1.Status <> 'N'
Falls die anderen Werte bekannt sind, würde auch ein in Tabelle1.Status in ('J','1') den Index benutzen.

[jobo]

Ich sehe da einen Widerspruch zwischen dem Statement aus #1 und der Anforderung " Ich möchte einfach alle Datensätze aus der Tabelle1 bei den in der Tabelle2 kein Datensatz vorhanden ist bei dem die beiden Feler übereinstimmen."
Der Ansatz mit left outer join ist dabei richtig, aber die Prüfung nur auf "Tabelle2.Status is null " ist unsicher, weil sie -ohne Kenntnis des Datenmodells- auch von sich aus null sein kann. (Außer das ist per constraint verboten)
Sicher wäre die Prüfung auf dem/den Join Feldern selbst, also

Code:

 Left Outer Join Tabelle2 on 
      Tabelle1.Staus = Tabelle2.Status
  and Tabelle1.CoNummer = Tabelle2.CoNummer
where Tabelle2.CoNummer is null and Tabelle2.Status is null

Wenn ich mit meinen Vermutung richtig liege, sind auch die beiden Vorschläge aus #6 diesbezüglich nicht ausreichend. Die Null Prüfung auf T2.Status sollte eigentlich auch auf CoNummer laufen und ergibt ihren Sinn erst nach dem Join

Die Fragen nach den Einzelmengen inkl. der einschränkenden Kriterien halte ich auch für sehr wichtig. Sie bieten wichtige Anhaltspunkte, dem Optimizer einen kleinen Tritt zu geben. (Die verwendeten Indizes sehen jedenfalls nicht sehr geschickt gewählt aus)

In der Hoffnung, dass die Menge allein durch den Join stark reduziert wird, würde ich das auch zuerst versuchen. (Nebenbei, keines der anderen gefilterten Felder klingt so, als ob selbst ein vorhandener und verwendeter Index viel nützt. 500T Datensätze mit einer handvoll Statuswerte, da lohnt kein Index)

Also Step 1 sowas:

Code:

SELECT Tabelle1.MasterNummer FROM Tabelle1
  LEFT OUTER JOIN Tabelle2 ON 
       Tabelle1.CoNummer = Tabelle2.CoNummer

eigentlich nur, um zu wissen wieviel das wird, also schneller

Code:

SELECT count(*) FROM Tabelle1
  LEFT OUTER JOIN Tabelle2 ON 
       Tabelle1.CoNummer = Tabelle2.CoNummer

Step 2 (nun wenigstens mit vollständigem Join Kriterium)

Code:

SELECT Tabelle1.MasterNummer FROM Tabelle1
  LEFT OUTER JOIN Tabelle2 ON 
       Tabelle1.CoNummer = Tabelle2.CoNummer
WHERE Tabelle1.Staus = Tabelle2.Status
  and Tabelle2.CoNummer is null
  and Tabelle2.Status is null

Status ins where, um den Optimizer zu lotsen. (Macht besonders Sinn, wenn CoNummer ein (Primär-)Schlüsselfeld ist oder wenigstens Fremdschlüssel und beidseitig indiziert.
Dieses Statement enthält logisch die korrekte Prüfung der Anforderung "alle Datensätze aus der Tabelle1 bei den in der Tabelle2 kein Datensatz vorhanden ist". Dabei beziehe ich mich auf das in #1 verwendete Joinkriterium über 2 Felder.

Wenn das jetzt schneller sein sollte, wird das endgültige Statement hoffentlich nicht langsamer:

Code:

select * from (
       SELECT Tabelle1.MasterNummer, Tabelle1.Status, Tabelle1.Storno  FROM Tabelle1
         LEFT OUTER JOIN Tabelle2 ON 
              Tabelle1.CoNummer = Tabelle2.CoNummer
        WHERE Tabelle1.Staus = Tabelle2.Status
          and Tabelle2.CoNummer is null
          and Tabelle2.Status is null) x
 where x Status <> 'N' and x.Storno is Null

Die Unterteilung ist des Statements ist geraten hat nur einen Sinn, Optimizer Hilfestellung.

Optimizer Hilfestellung kann man aber auch ganz anders machen.
a) Selber einen Plan angeben
b) Unnütze Indizes löschen, idealerweise bleiben nur die übrig, die verwendet werden sollen

Beides ist ohne Angaben zu Werteverteilung in den Filterfeldern, Datenmodell und zur Verwendung der Felder in anderen Abfragen nicht sinnvoll.

[hoika]

Hallo,
was passiert, wenn Du die letzten beiden where's weglässt?

<>"N" hattest du ja selber schon ausgeschlossen.
Das Storno kannst Du selber im Code prüfen.