in-Klausel in großen Tabellen

[Jumpy]

Aber muss nicht bei der Distinct + Join Variante der Distinct nur 1x gemacht werden, während beid er exists Variante für jeden Datensatz ein Subselect gemacht werden muss?

[ConnorMcLeod]

Ich mag auch mal

Delphi-Quellcode:

select
    A.AuftragsNr
  , A.Arbeitsgang
from
  Auftrag A
join
  (
  select 
    *
  from
    (
    select 
        Sub.AuftragsNr  
      , count(Sub.Arbeitsgang) Anzahl
    from
      Auftrag Sub
    where
      (Sub.FertigDatum between 'ANFANG' and 'ENDE')
    group by
      Sub.AuftragsNr
    ) AufAnz
  where 
    (0 < AufAnz.Anzahl)
  ) OnlyNotZero
on
  (OnlyNotZero.AuftragsNr = A.AuftragsNr)

[p80286]

Zitat von Jumpy:

Aber muss nicht bei der Distinct + Join Variante der Distinct nur 1x gemacht werden, während beid er exists Variante für jeden Datensatz ein Subselect gemacht werden muss?

Für das exists spricht, das es der menschlichen Denke engegenkommt. Ich habe aber die Erfahrung gemacht, das ein Join mit einem check auf NULL wesentlich schneller ist.

Gruß
K-H

[Delphi.Narium]

Zitat von Jumpy:

Aber muss nicht bei der Distinct + Join Variante der Distinct nur 1x gemacht werden, während beid er exists Variante für jeden Datensatz ein Subselect gemacht werden muss?

Warum sollte das so sein?

Dort ist eine recht gute Erklärung zu finden: https://stackoverflow.com/questions/...-exists-clause

Zitat von JNK:

EXISTS is used to return a boolean value, JOIN returns a whole other table

EXISTS is only used to test if a subquery returns results, and short circuits as soon as it does. JOIN is used to extend a result set by combining it with additional fields from another table to which there is a relation.

In your example, the queries are symantically equivalent.

In general, use EXISTS when:

You don't need to return data from the related table
You have dupes in the related table (JOIN can cause duplicate rows if values are repeated)
You want to check existence (use instead of LEFT OUTER JOIN...NULL condition)

If you have proper indexes, most of the time the EXISTS will perform identically to the JOIN. The exception is on very complicated subqueries, where it is normally quicker to use EXISTS.

Beide Varianten prüfen, ob es zu der Bedingung ein "Gegenstück" gibt. Während Exists nur sagt: "Ja gibt es" bzw. "Nein, gibt es nicht", liefert Join das Gegenstück. Bei großen Datenmengen kann das schon einen großen Unterschied bezüglich des Speicherbedarfes geben.
Unter Oracle war es zu Zeiten, als ich mich damit noch beruflich befasste, deutlich schneller, mit Exists zu arbeiten. Spätestens, wenn die Datenbank den temporary tablespace nutzen musste, konnte man da Unterschiede bemerken, weil irgendwann die Auslagerung des Speichers auf die Festplatte(n) sich im Laufzeitverhalten deutlich bemerkbar machte.

Hier im konkreten Fall müssen wir nur wissen, ob es zu 'nem Auftrag mindestens einen Satz im gewünschten Jahr gibt, um dann alle Datensätze zu der Auftragsnummer auszugeben.

Mit Exists erhalten wir nur ein Ja oder ein Nein, bei 'nem Join erhalten wir alle Datensätze aus dem gewünschten Jahr. Die werden dann (erstmal) mit allen Datensätzen der Auftragsnummer verbunden. Es kann also (auf Auftragsnummerebene) einen CROSS JOIN geben. (DeddyHs diesbezügliche Anmerkung ist daher angebracht.) Bei einem Join muss man also auch noch sicherstellen, dass keine (partiziellen) Dubletten entstehen. Das ist aufwändiger, als eine "simple" Existenzprüfung. Daher ist (meiner Meinung nach) im vorliegenden Fall Exists vorzuziehen.

[peterbelow]

Zitat:

select * from tabelle
where AuftragNr in (
select AuftragNr from Tabelle where Year(FertigDatum) = 2018 group by AuftragNr)

Wozu das group by in der inneren query? Eventuell funktioniert das hier besser:

Code:

select distinct <feldliste> from tabelle
where exists (
select AuftragNr from Tabelle where Year(FertigDatum) = 2018)
order by auftragnr, arbeitsgang

Ich hatte allerdings auch schon Fälle, wo es nicht anders ging als ein Zwischenergebnis in einer temporären Tabelle zu speichern. In deinem Fall würde man da
das Resultat von

Code:

select distinct AuftragNr from Tabelle where Year(FertigDatum) = 2018)

ablegen. Diese Query gibt halt deutlich weniger Zeilen zurück als ohne "distinct", ist aber aufwendiger in der Verarbeitung.

[freejay]

Also ich würde folgendes tun:

1. Ein Feld "Jahr" einführen und entsprechend füllen
2. Einen Index für das Feld "Jahr" anlegen
3. Anschließend folgende Abfrage ausprobieren:

Code:

SELECT * FROM tabelle a
INNER JOIN (
  SELECT DISTINCT AuftragNr FROM Tabelle where Jahr = 2018) i ON a.AuftragNr = i.AuftragNr
ORDER BY auftragnr, arbeitsgang

4. Falls das nicht performant ist, dann das Ergebnis der inneren Abfrage vorher in einer (temporären) Tabelle zwischenspeichern (in dieser Tabelle den Index auf AuftragNr nicht vergessen!) und dann die obengenannte Abfrage auf die temporäre Tabelle machen. Das würde dann ungefähr so aussehen:

Code:

CREATE TEMPORARY TABLE `temptable` (
  `AuftragNr` int(11),
  PRIMARY KEY (`AuftragNr`)
) ENGINE=InnoDB;
                 
INSERT INTO `temptable` 
  SELECT DISTINCT AuftragNr FROM Tabelle where Jahr = 2018;

SELECT * FROM `tabelle` a
INNER JOIN `temptable` i ON a.AuftragNr = i.AuftragNr
ORDER BY AuftragNr, arbeitsgang;

DROP TEMPORARY TABLE `temptable`;

Wenn diese Statements zusammen nicht innerhalb eines Fingeschnippsens durch sind, fresse ich einen Besen...

PS1: Ich habe nicht alle vorherigen Antworten komplett durchgelesen, vielleicht wiederhole ich auch nur bereits Gesagtes.
PS2: Ich habe die Statements nicht ausprobiert.

[freejay]

PS: Ich gehe doch mal davon aus, dass der Themenersteller in seiner Tabelle auf jeden Fall einen Index auf `AuftragsNr` hat...

[Delphi.Narium]

Die Auftragsnummer wird über concat(waaunr,'-', waaupo) gebildet. Sie ist ein Konstrukt mehrerer Werte, die zusammen zu einem gewünschten Jahr existieren müssen. Ist diese Existenz gegeben, müssen alle Sätze, die über dieses Konstrukt verfügen, von der Datenbank herausgesucht werden.

[freejay]

Zitat von Delphi.Narium:

Die Auftragsnummer wird über concat(waaunr,'-', waaupo) gebildet. Sie ist ein Konstrukt mehrerer Werte, die zusammen zu einem gewünschten Jahr existieren müssen. Ist diese Existenz gegeben, müssen alle Sätze, die über dieses Konstrukt verfügen, von der Datenbank herausgesucht werden.

Ok, das habe ich übersehen. Das ist ja aber für die Performance ganz schlecht. Ich würde daher dringend empfehlen, diesen Wert als echtes Tabellenfeld einzuführen. Ja, das ist irgendwie redundant, aber manchmal, wenn's um die Performance geht ist das die einzige wirklich gut funktionierende Lösung.

[Delphi.Narium]

Das ist nur dann nicht performant, wenn man für die Abfrage zuerst die beiden Felder zusammenfasst und diese Zusammenfassung für die Wherebedingung nutzt. Da man die Wherebedingung aber über die beiden Felder separat machen kann, sollte das durchaus schnell mit einer Exists-Prüfung im SQL zu realisieren sein. Klar, der Index muss auf diese Abfrage optimiert sein, dass sollte aber mit 'ner modernen Datenbank im Rahmen des Möglichen sein.