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:

Zitat von hoika (Beitrag 1362574)

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

SQL-Code:

Tabelle2.Status is null

wahr ist, dann viel Spaß beim Joinen!
Wenn

SQL-Code:

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:

Zitat von p80286 (Beitrag 1362577)

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.

[Dumpfbacke]

Zitat:

Zitat von hoika (Beitrag 1362620)

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.

Das geht auch nicht schneller. Ich habe esnur beutzt, um den Anzahl der Datensätze zu veringern.

[Dumpfbacke]

Ich zeige Euch mal wie die Daten in den Tabellen aussehen können. Eventuell wird es dann deutlicher wo mein Fehler leigt.

Also als erstes einmal bei dem Status N kann es keine Zeile in Tabelle2 geben. Es kann auch keine Zeile in Tabelle 2 geben bei dem der Wert des Feldes Status NULL ist.

Es liegt ein Index auf den Felder CoNummer und Status der Teabelle1 sowie CoNummer und Status der Tabelle2

Delphi-Quellcode:

Tabelle1
Masternummer CoNummer Status
1            2514     N
2            2515     Start
3            2516     Warten 
4            2517     Produktion
5            2518     Ausgabe
6            2519     Komplett 



Tabelle2
CoNummer Status
2515     Start
2516     Start
2517     Start
2517     Warten
2518     Start
2518     Warten
2518     Produktion
2518     Ausgabe

Als Ergebnis möchte ich nun haben 1,3,4 und 6. Also alle Datensätze aus Tabelle1 wo kein Datensatz in Tabelle 2 vorhanden ist.

[dataspider]

IMHO ist hier nicht viel zu machen.
Für die Verknüpfung mit Tabelle 2 hilft dir kein Index.
Fakt ist:
Die Indexe greifen nur auf Status und Storno, alle anderen Datensätze von Tabelle 1 werden beim Select "angefasst".
Du kannst nur noch sicherstellen, das zur Prüfung auch nur ein Datensatz in Tabelle 2 "angefasst" wird,
indem du statt einem Join exists verwendest.

Wenn es schnell sein soll, benötigst du IMHO ein zusätzliches indexiertes Feld in Tabelle 1,
welches über "after insert update delete" Trigger der Tabelle 2 aktuell gehalten wird.

Frank

[Valle]

Ich bin neugierig, aber leider erfahrungslos mit Firebird. Im Prinzip würde das hier das Ergebnis liefern, oder?

Code:

SELECT * FROM Tabelle1
WHERE NOT EXISTS (
    SELECT CoNummer FROM Tabelle2 WHERE Tabelle2.CoNummer = Tabelle1.CoNummer
)

[nahpets]

Zitat:

Zitat von Valle (Beitrag 1362624)

Ich bin neugierig, aber leider erfahrungslos mit Firebird. Im Prinzip würde das hier das Ergebnis liefern, oder?

Code:

SELECT * FROM Tabelle1
WHERE NOT EXISTS (
    SELECT CoNummer FROM Tabelle2 WHERE Tabelle2.CoNummer = Tabelle1.CoNummer
)

Das war beim Lesen der Anforderungen jetzt auch gerade mein erster Gedanke.

Will ich nur wissen, ob es etwas gibt oder nicht, dann frage ich mit Exists ab:
Beispiele:

SQL-Code:

/* gibt es da was in Tabelle2 zu Tabelle1? */
select benötigtespalte(n) from tabelle1
where exists (select 1 from tabelle2 where tabelle1.schlüssel = tabelle2.schlüssel)

/* gibt es nichts in Tabelle 2 zu Tabelle 1? */
select benötigtespalte(n) from tabelle1
where not exists (select 1 from tabelle2 where tabelle1.schlüssel = tabelle2.schlüssel)

Das war nach meiner bisherigen Erfahrung immer die schnellste Variante für eine Existenz- bzw. Nichtexistenzprüfung.

Brauche ich sowas nur für kleine Teilmengen der betroffenen Tabellen dann wird daraus sinngemäß:

SQL-Code:

select * from (
  select benötigtespalte(n) from tabelle1
  where EinschränkendeBedingungen zu Tabelle1
) a
where exists /* Wenns was geben soll */
(
  select Schlüsselspalten from 
  (
    select Schlüsselspalten from tabelle2
    where EinschränkendeBedingungen zu Tabelle2
  ) b
)
where a.schlüssel = b.schlüssel

Ja, ich weiß, das wird zuweilen sehr schwer lesbar, aber wenn's hilft, dann ist dem so. Im Zweifelsfalle erstellt man sich für die "innern Selects" halt Views, dann ist der Zugriff wieder wie im ersten Beispiel leicht lesbar, ohne das Laufzeitveränderungen auftreten.

Zitat:

Zitat von hoika2

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

Syntaktisch mag das zutreffen.

Meine Erfahrung hat mich jedoch gelehrt, dass der Optimizer hier bei starken Einschränkungen der Daten auf Teilmengen, die dann zu verknüpfen sind, häufig "daneben liegt".
Er kann bei der Optimierung (vermutlich) die aus den Einschränkungen resultierenden Teilmengen nicht optimal einschätzen, um den laufzeittechnisch und dateninduziert optimalen Plan zu erstellen.

Würde dashier (sinngemäß) den Anforderungen entsprechen?

SQL-Code:

select a.Masternummer from
(
  select Masternummer from tabelle1
  where status <> 'N' and status is not null
) a
where not exists (
  select 1 from tabelle2 b 
  where a.CoNummer = b.CoNummer
)

Und ist es auch akzeptabel schnell?

[p80286]

Zitat:

Zitat von nahpets (Beitrag 1362625)

Das war nach meiner bisherigen Erfahrung immer die schnellste Variante für eine Existenz- bzw. Nichtexistenzprüfung.

Ist leider nicht allgemeingültig!

SQL-Code:

select *
from t1 left join t2 on (t1.id=t2id)
where t2.id is null

Ist meist etwas schneller, kommt aber wohl vor allem auf die konkreten Daten (und die DB) an.
Man sollte immer beide Wege probieren.

Gruß
K-H

[nahpets]

Zitat:

Zitat von p80286 (Beitrag 1362629)

Ist leider nicht allgemeingültig!

Das Schlimme daran ist ja, dass es ja nicht nur bei unterschiedlichen Datenbankherstellern unterschiedlich ist, sondern je nach Datenbank eines Herstellers datenabhängig nicht zwingend den "richtigen" Weg gibt, sondern auch noch abhängig von der darunterliegenden Hardware (z. B. identische Maschinen mit lediglich unterschiedlicher Speicherbestückung) durchaus unterschiedliche Verhaltensweisen geben kann.

Im Zweifel gilt bei mir immer:

Ausprobieren, bis die (hoffentlich) beste Lösung gefunden wurde.

"Die beste Lösung" gibt es nicht.

[himitsu]

In Firebird kann man sich doch bestimmt auch den EXPLAIN PLAN/ANALYSE ausgeben lassen und sieht ob und welcher Index verwendet wird.

Mal probiert den JOIN umzudrehen?
Im Beispiel hat Tabelle2 mehr Einträge.

Delphi-Quellcode:

FROM Tabelle1 JOIN Tabelle2

> für jeden Eintrag in Tabelle 1 alle vielen Einträge in Tabelle 2 durchsuchen (NULL steht nicht im Index, also FullScan und Index ignoriert)

Delphi-Quellcode:

FROM Tabelle2 JOIN Tabelle1

> für jeden Eintrag in Tabelle 2 die wenigen Einträge in Tabelle 2 durchsuchen

Oder halt die Variante ohne JOIN.

SQL-Code:

SELECT *
FROM Tabelle1
WHERE NOT exists(
  SELECT true
  FROM Tabelle2
  WHERE Tabelle1.CoNummer = Tabelle2.CoNummer
    AND Tabelle1.Status = Tabelle2.Status
  LIMIT 1
)

Das SubSelect kann nun einen Index gut ausnutzen, wenn der über CoNummer+Status geht.
Keine Ahnung, ob EXISTS schon so schlau ist und die Felder (hier nur ein True) ignoriert, weil sie niemals genutzt werden,
und bereits ein LIMIT verwendet, da ja schon ab dem ersten Fund das Ergebnis feststeht.

[Dumpfbacke]

Zitat:

Zitat von himitsu (Beitrag 1362634)

In Firebird kann man sich doch bestimmt auch den EXPLAIN PLAN/ANALYSE ausgeben lassen und sieht ob und welcher Index verwendet wird.



Oder halt die Variante ohne JOIN.

SQL-Code:

SELECT *
FROM Tabelle1
WHERE NOT exists(
  SELECT true
  FROM Tabelle2
  WHERE Tabelle1.CoNummer = Tabelle2.CoNummer
    AND Tabelle1.Status = Tabelle2.Status
  LIMIT 1
)

Firebird kennt kein true und auch kein LIMIT 1

[nahpets]

Würde das in etwa so übersetzen:

Statt

SQL-Code:

SELECT *
FROM Tabelle1
WHERE NOT exists(
  SELECT true
  FROM Tabelle2
  WHERE Tabelle1.CoNummer = Tabelle2.CoNummer
    AND Tabelle1.Status = Tabelle2.Status
  LIMIT 1
)

könnte dashier gehen:

SQL-Code:

SELECT *
FROM Tabelle1
WHERE NOT exists(
  SELECT first 1 1
  FROM Tabelle2
  WHERE Tabelle1.CoNummer = Tabelle2.CoNummer
    AND Tabelle1.Status = Tabelle2.Status
)

Syntaktisch geht das so unter FireBird (grade mal ausprobiert), ob's auch sinnvolle Ergebnisse liefert, kann ich nicht sagen, first 1 1 sieht jedenfalls seltsam aus ;-)

[dataspider]

Da exists sowieso nur ein read macht (sofern die Indexe in Tabelle2 auf status und conummer existieren,
ist IMHO first 1 überflüssig.

SQL-Code:

select t1.masternummer
       from tabelle1 t1
       where t1.status <> 'N' and t1.storno is null and not exists
         (
             select * from tabelle2 t2 where t2.status = t1.status and
               t2.conummer = t1.conummer
         )

Frank