AGB  ·  Datenschutz  ·  Impressum  







Anmelden
Nützliche Links
Registrieren
Zurück Delphi-PRAXiS Programmierung allgemein Datenbanken in-Klausel in großen Tabellen

in-Klausel in großen Tabellen

Ein Thema von Ykcim · begonnen am 8. Feb 2019 · letzter Beitrag vom 11. Feb 2019
Antwort Antwort
Seite 3 von 5     123 45   
Delphi.Narium

Registriert seit: 27. Nov 2017
1.094 Beiträge
 
Delphi 7 Professional
 
#21

AW: in-Klausel in großen Tabellen

  Alt 8. Feb 2019, 14:08
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.
  Mit Zitat antworten Zitat
jobo

Registriert seit: 29. Nov 2010
2.670 Beiträge
 
Delphi 2010 Enterprise
 
#22

AW: in-Klausel in großen Tabellen

  Alt 8. Feb 2019, 14:15
@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?
Gruß, Jo
  Mit Zitat antworten Zitat
Ykcim

Registriert seit: 29. Dez 2006
Ort: NRW
667 Beiträge
 
Delphi 10.3 Rio
 
#23

AW: in-Klausel in großen Tabellen

  Alt 8. Feb 2019, 14:39
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
Patrick
  Mit Zitat antworten Zitat
jobo

Registriert seit: 29. Nov 2010
2.670 Beiträge
 
Delphi 2010 Enterprise
 
#24

AW: in-Klausel in großen Tabellen

  Alt 8. Feb 2019, 14:46
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.
Gruß, Jo
  Mit Zitat antworten Zitat
Delphi.Narium

Registriert seit: 27. Nov 2017
1.094 Beiträge
 
Delphi 7 Professional
 
#25

AW: in-Klausel in großen Tabellen

  Alt 8. Feb 2019, 15:21
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-01AS DATE) and CAST('2018-12-31AS 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.
  Mit Zitat antworten Zitat
Benutzerbild von Uwe Raabe
Uwe Raabe

Registriert seit: 20. Jan 2006
Ort: Lübbecke
7.165 Beiträge
 
Delphi 10.3 Rio
 
#26

AW: in-Klausel in großen Tabellen

  Alt 8. Feb 2019, 15:27
@uwe: Ich geb Dir Recht, ein Unit Test ist aber vielleicht eher was für die harten Fälle?
Die Vielzahl der Vorschläge hier, die nicht das gewünschte Ergebnis liefern, rechtfertigt meiner Meinung nach einen Unit Test in jedem Fall. Ich bin mir nicht sicher, ob man als neuer Entwickler oder selbst nach einer gewissen Zeit noch anhand der SQL-Anweisung das beabsichtigte Ergebnis ersehen kann. Sollten dann Anpassungen oder Refactorings (z.B. auf einen anderen SQL-Server-Typ) notwendig sein, wird man für einen solchen Test sicher dankbar sein.
Uwe Raabe
Certified Delphi Master Developer
Embarcadero MVP
Blog: The Art of Delphi Programming
  Mit Zitat antworten Zitat
hoika

Registriert seit: 5. Jul 2006
Ort: Magdeburg
7.344 Beiträge
 
Delphi XE4 Professional
 
#27

AW: in-Klausel in großen Tabellen

  Alt 8. Feb 2019, 15:38
Hallo,
und am besten noch einen Link auf diesen Thread in den Quellcode
Heiko
  Mit Zitat antworten Zitat
stifflersmom

Registriert seit: 8. Dez 2005
Ort: 24994 Holt
213 Beiträge
 
Delphi 2006 Professional
 
#28

AW: in-Klausel in großen Tabellen

  Alt 8. Feb 2019, 17:46
Moin,

ich erschlage solche Schwierigkeiten immer, in dem ich den Abfrageweg vorgebe. Bei manchen Abfragen mit select in, bin ich mir ob der Schlauigkeit von z.B. mysql nicht so sicher.
Vorraussetzung für en solches Vorgehen ist natürlich, dass man die Rechte hat, (temporäre) Tabellen zu erzeugen

Code:
/* Die relevanten Datensätze separieren */
DROP TABLE IF EXISTS tempt;
CREATE TEMPORARY TABLe tempt(select distinct AuftragsNr from Auftragstable where Fertigdatum >= '2018-01-01' and Fertigdatum <= '2018-12-31');
ALTER TABLE tempt ADD index(AuftragsNr);

/* Ursprungstabelle mit separierter Datenmenge joinen und voila (jedenfalls voila, wenn es einen Index gibt, der für die AuftragsNr zuständig ist*/
select a.* from Auftragstable a, tempt t
where a.AuftragsNr=t.AuftragsNr
Gruß
  Mit Zitat antworten Zitat
Benutzerbild von p80286
p80286

Registriert seit: 28. Apr 2008
Ort: Stolberg (Rhl)
6.469 Beiträge
 
Delphi 7 Personal
 
#29

AW: in-Klausel in großen Tabellen

  Alt 8. Feb 2019, 21:39
Warum eigentlich ein "in"?

SQL-Code:
select auftragsdaten.irgendwas
from auftragsdaten join (select distinct auftragsnr from auftragsdaten where fertigdate between 20180101 and 20181231) a on (auftragsdaten.auftragsnr=a.auftrgsnr)
so ungefähr, die korrekte Syntax und die between-Grenzen müßtest Du noch überprüfen.

Gruß
K-H

P.S.
Zumndest Oracle begrenzt die "in-Menge" auf 2000 Einträge.
Programme gehorchen nicht Deinen Absichten sondern Deinen Anweisungen
R.E.D retired error detector
  Mit Zitat antworten Zitat
Delphi.Narium

Registriert seit: 27. Nov 2017
1.094 Beiträge
 
Delphi 7 Professional
 
#30

AW: in-Klausel in großen Tabellen

  Alt 8. Feb 2019, 23:42
Zumndest Oracle begrenzt die "in-Menge" auf 2000 Einträge.
Wenn ich mich recht erinnere aber nur, wenn man sie im SQL auflistet, nicht jedoch als Ergebnismenge einer Abfrage.

select * from tabelle where Spalte in ('1','2', ... ,'2000') mit hier aufgeführen 2000 Werten müsste funktionieren, nicht jedoch select * from tabelle where Spalte in ('1','2', ... ,'2001') mit hier aufgeführten mehr als 2000 Werten. select * from tabelle where Spalte in (select anderespalte from anderetabelle) sollte auch noch bei ein paar millionen Datensätzen funktionieren, wenn auch nicht unbedingt sehr schnell.

Von den hier bisher aufgeführen Lösungsvorschlägen müssten eigentlich einige zum richtigen Ergebnis führen. Einziger Unterschied dürfte die Laufzeit der jeweiligen Abfrage sein.

Unter Oracle ware bisher eigentlich meist die Variante mit dem ... where exists (select 1 from ... am Schnellsten, da hier nur eine Existenzprüfung nötig ist. Ein Distinct muss die gesamte Datenmenge "durchgehen", sortieren und damit dann eine Eindeutigkeit sicherstellen. Das dauert meist länger, als "nur" nachzuschauen, ob es da überhaupt einen passenden Satz gibt.
  Mit Zitat antworten Zitat
Themen-Optionen Thema durchsuchen
Thema durchsuchen:

Erweiterte Suche
Ansicht

Forumregeln

Es ist dir nicht erlaubt, neue Themen zu verfassen.
Es ist dir nicht erlaubt, auf Beiträge zu antworten.
Es ist dir nicht erlaubt, Anhänge hochzuladen.
Es ist dir nicht erlaubt, deine Beiträge zu bearbeiten.

BB-Code ist an.
Smileys sind an.
[IMG] Code ist an.
HTML-Code ist aus.
Trackbacks are an
Pingbacks are an
Refbacks are aus

Gehe zu:

Impressum · AGB · Datenschutz · Nach oben
Alle Zeitangaben in WEZ +1. Es ist jetzt 13:51 Uhr.
Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
LinkBacks Enabled by vBSEO © 2011, Crawlability, Inc.
Delphi-PRAXiS (c) 2002 - 2019 by Daniel R. Wolf