in-Klausel in großen Tabellen

[Jumpy]

@Jobo: Wird der Join nicht zu groß (Kreuzprodukt)?

Sinngemäß ginge vllt. sowas:

SQL-Code:

Select B.* From
(Select Distinct Auftragsnummer From Tabelle Where Datum between 010118 und 311218) A
Left Join Tabelle B
On B.Auftragsnummer=A.Auftragsnummer

[DeddyH]

In allen mir bekannten DBMS ist ein JOIN ohne weitere Angaben automatisch ein INNER JOIN. Das Kreuzprodukt erhält man mit CROSS JOIN (wobei mir dafür bislang noch kein Anwendungsfall begegnet ist).

[Delphi.Narium]

Oder doch eher dashier?

SQL-Code:

select a.*
  from Auftragsdaten A
  join Auftragsdaten A2018
    on A.AuftragsNr = A2018.AuftragsNr
 where A2018.Fertigdatum between :anfang and :ende

[Uwe Raabe]

Ich bedaure bei solchen SQL-Fragen immer wieder, daß offenbar niemand auf die Idee kommt, ein kurzes Script zum Erzeugen einer Test-DB mitzuliefern. Bei Delphi-Fragen hilft ein Code-Beispiel eine eventuelle Antwort vorher zu testen. Bei SQL-Fragen sind die Antworten in der Regel ungetestet und fallen manchmal eher in die Kategorie "geraten". Die tabellarische Auflistung im ersten Post verdeutlicht zwar das Problem, ist aber zum Austesten nicht wirklich ausreichend.

Am liebsten wäre mir ein kompletter Unit-Test (DUnit oder DUnitX), der lediglich einen entsprechenden "Datenbankserver" voraussetzt, und wo man allenfalls bei der Connection und Authentifizierung etwas anpassen muss. Das käme dann ja auch direkt der Testabdeckung des eigentlichen Projekts zugute.

[Ykcim]

Hallo Zusammen,

vielen Dank für das toll Feedback!!!

Ich habe verschiedene Vorschläge geprüft, aber ich glaube, die richtige Lösung war noch nicht dabei.

Meine Schwierigkeit liegt darin, dass ich alle Arbeitsgänge von allen Aufträgen aus einem Jahr aufgelistet haben möchte, wo mindestens 1 Arbeitsgang in dem ausgewählten Jahr fertig geworden ist. Die Arbeitsgänge, die durch einen Jahreswechsel im vorherigen oder nachfolgenden Jahr fertig wurden, möchte ich auch haben. Sobald ein Arbeitsgang in dem ausgewählten Jahr fertig wurde möchte ich immer alle Arbeitsgänge, die zu dem Auftrag gehören ausgewiesen bekommen.

Es geht also nicht nur um die Arbeitsgänge, die im ausgewählten Jahr fertig wurden, sondern auch um die "Geschwister-Arbeitsgänge" des Aufrags, egal wann die fertig wurden. Deshalb hatte ich es mit der IN-Funktion probiert...

Vielen Dank!
Patrick

[Delphi.Narium]

Was stimmt den jetzt bei den Vorschlägen nicht: Die Geschwindigkeit oder das Ergebnis?

Alle AuftragsNr aus 2018 werden gesucht:
select distinct AuftragsNr from tabelle where Fertigdatum between 20180000 and 20181231 und nun aus der Tabelle alle die, deren AuftragsNr im obigen Ergebnis enthalten ist:

SQL-Code:

select * from tabelle where AuftragsNr in (
  select distinct AuftragsNr from tabelle where Fertigdatum between 20180000 and 20181231
)

Soweit so richtig so langsam?

Von welchem Datentyp ist eigentlich Fertigdatum?
Und auf welchen der für die Abfrage benötigten Spalten gibt es einen Index bzw. keinen Index?
Und wie sehen die entsprechenden Indexdefinitionen aus?
Um was für eine Datenmenge handelt es sich?
Ein paar Dutzend, ein paar hundert, ein paar Tausend, etliche Millionen?

Und ja: Uwe Raabe hat recht: Eigentlich sind die Infos vieeeeeeeel zu dürftig, um sinnvolle Hilfestellung geben zu können.

Vollständiges Createstatement für die Tabelle und Mengenangaben (incl. erwarteter Ergebnismenge und ggfls. erwarteten Zuwächsen für die "restliche Lebenszeit des Programmes") wären hilfreich.

[jobo]

@jumpy, DeddyH
Ich denke, jumpy meint die Mehrfachausgabe der AuftrNr.

So ist es sicher und vielleicht auch etwas abgehärtet gegen Optimizer "Irrtümer":

Code:

select A.*
  from Auftragsdaten A
  join (select distinct AuftragsNr 
          from Auftragsdaten 
         where A2018.Fertigdatum between :anfang and :ende) A2018
    on A.AuftragsNr = A2018.AuftragsNr

@uwe: Ich geb Dir Recht, ein Unit Test ist aber vielleicht eher was für die harten Fälle?

[Ykcim]

Zitat:

select * from tabelle where AuftragsNr in (
select distinct AuftragsNr from tabelle where Fertigdatum between 20180000 and 20181231
)

Bei diesem Code raucht mir die Datenbank ab - wird und wird nicht fertig....

Zitat:

select concat(waaunr,'-', waaupo) as auftrag,
watenr,
oaagnr,
oamanr,
oarmda,
oatlkz,
oarmmg,
sum(oarmmg*wagewi/1000) as wagewi from as400archiev a
where exists (select 1 from as400archiev b where Year(b.oarmda) = 2015 and a.waaunr = b.waaunr and a.waaupo = b.waaupo and a.oaagnr = b.oaagnr)
group by a.waaunr, a.waaupo, a.oaagnr

Bei dieser Variante scheinen die Arbeitsgänge durch Jahresübergänge verloren zu gehen...

Das Fertigatum ist als Date definiert und ich habe einen Index auf den Spalten AuftragsNr, ArbeitsgangNr, RückmeldeDatum.

Die Tabelle umfasst ca. 1,7 Mio Datensätze und es kommen jährlich ca. 300.0000 dazu.

Ich werde am Montag eine Bereiningung der Daten vornehmen, weil ich bei einigen Datensätzen ein falsches Datumsformat entdeckt habe...
Ich melde mich am Montag wieder!

Vielen Dank!!!

Patrick

[jobo]

Zitat von Ykcim:

Zitat:

select * from tabelle where AuftragsNr in (
select distinct AuftragsNr from tabelle where Fertigdatum between 20180000 and 20181231
)

Bei diesem Code raucht mir die Datenbank ab - wird und wird nicht fertig....
.....snipp...
Ich werde am Montag eine Bereiningung der Daten vornehmen, weil ich bei einigen Datensätzen ein falsches Datumsformat entdeckt habe...
Ich melde mich am Montag wieder!

Ok, das bedeutet, Dein Datumsfeld ist ein Ganzzahltyp?!

Würdest Du ein Datumstyp nehmen, wäre das andere Problem, von dem Du anschließend schreibst, nicht auftreten.

Ändert nichts daran, dass mein letzter Vorschlag helfen soll. Vorausgesetzt es gibt einen Index auf dem Feld und keine anderen Überraschungen.

[Delphi.Narium]

SQL-Code:

select concat(waaunr,'-', waaupo) as auftrag,
  watenr,
  oaagnr,
  oamanr,
  oarmda,
  oatlkz,
  oarmmg,
  sum(oarmmg * wagewi / 1000) as wagewi 
from as400archiev a
where exists (
  select 1 from as400archiev b 
  where Year(b.oarmda) = 2015 
  and a.waaunr = b.waaunr 
  and a.waaupo = b.waaupo 
  -- hier dürfen nur die Sachen abgefragt werden, 
  -- die die AuftragsNr bilden, alles andere führt zu 
  -- ungewünschten Ergebnissen, da es das Abfrageergebnis 
  -- weiter einschränkt, als erforderlich.
  -- wegen :Bei dieser Variante scheinen die Arbeitsgänge durch Jahresübergänge verloren zu gehen...
  -- and a.oaagnr = b.oaagnr -- auskommentiert.
)
group by 
  a.waaunr, 
  a.waaupo, 
  a.oaagnr

Damit wir das Problem noch etwas besser verstehen können, bitte eine "Übersetzung" der Spaltennamen "liefern", die Kürzel erscheinen mir doch eher sehr (fehl)interpretationsanfällig.

Welche Spalteninhalte sind "jahresübergreifend" und welche nicht?
In der Wherebedingung dürfen keine Spalten abgefragt werden, für die die Jahreseinschränkung im Endergebnis nicht relevant ist, sondern nur die für die Ermittlung der Datensätze, die zwingend auch im abgefragten Jahr zu finden sein müssen.
Der Arbeitsgang scheint mir hier in der Abfrage daher fehl am Platz. Ausgehend von Deinem Beispiel im Eingangspost enthält er nur ein Fertigdatum und kann daher nicht "jahresübergreifend" vorkommen. Diese Einschränkung dürfte die Ursache für den Verlust der Jahresübergänge sein. (Aber da kann ich mich auch durchaus irren.)

Und bei der Datenmenge muss man dann wohl doch sehr auf die Feinheiten der DB und deren Umsetzung der Abfragen achten, sonst wird's doch eher laaaaangsam

Achso: Weiß nicht wie das bei MySQL ist, aber dashier Year(b.oarmda) kann Dir eventuell die Nutung des Index kaputtmachen, da ja nur eine Teilmenge des Wertes gebraucht wird. Hier könnte also die Abfrage mit b.oarmda between CAST('2018-01-01' AS DATE) and CAST('2018-12-31' AS DATE) schlimmsten- / bestenfalls so das eine oder andere Stündchen Abfragedauer einsparen.

Wenn möglich Parameter nutzen, keine Funktionen in die Wherebedingung, die eine Spalte in einen anderen Type konvertieren oder nur Teilmengen einer Spalte nutzen. Lieber ein between mit kleinstem und größtem zulässigen Wert nutzen.