SQL Script optimieren damit es schneller geht

[Dumpfbacke]

Zitat von 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.

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 von 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
)

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 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 von nahpets:

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 von p80286:

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.
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)
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 von himitsu:

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