SQL Query Optimierung

OnDemand

OnDemand

Top Contributor
Hallo zusammen,
ich versuche grad verzweifelt folgenden Query irgendwie zu optimieren. Der braucht 1 min zum Ausführen. Mit InnerJoin bekomme ich es nicht hin. Hat jemand ne Idee?

SQL: 
SELECT Count(id) FROM tableA ta WHERE NOT EXISTS(SELECT id FROM tableB WHERE p_id=ta.id)
 
Outer Join wäre hier vermutlich das angebrachte

SQL: 
SELECT COUNT(DISTINCT a.ID) FROM tableA a LEFT OUTER JOIN tableB b ON a.ID = b.p_id WHERE b.ID IS NULL
 
Zuletzt bearbeitet von einem Moderator:
Danke! Der ist auch nicht eikrlich schneller, dass muss ich wohl das DB Design mal überdenken oder die Daten lazy laden, damit das Frontend nicht hängt

Edit ist ca 50% schneller dein Query, danke muss reichen 😀
 
SQL: 
    SELECT COUNT ( DISTINCT a.ID )
    FROM table a, table b
    WHERE a.ID = b.P_id
    AND b.id IS NULL
joins sind nie gut...


EDIT : was mir jetzt erst auffällt .. warum hast du in Tabelle b zwei IDS?!
 
Zuletzt bearbeitet von einem Moderator:
Also grundsaetzlich lass' dir von der Datenbank mal erklaeren wie die Query ausgefuehrt wird, in den meisten geht das mit einen vorangestellen "EXPLAIN", also einfach das SQL ausfuehren "EXPLAIN SELECT Count(id) ...". Und dann kann man sich fragen:

1. Kann ich einen Index legen auf die Felder welche im "where" stehen" um das zu beschleunigen?
2. Kann ich die Datenbank-Struktur aendern um einen Index zu ermoeglichen?
3. Hardware aendern? Server-Setup aendern?

In dem Fall bin ich mir offengestanden nicht sicher, ich glaube dass dir hier sogar ein Index nicht helfen wird am Ende, dass wird immer auf einen Full-Table-Scan hinauslaufen. Weil du willst wissen welche von "A" nicht in "B" vorkommen, dafuer muss man zumindest alle von "A" durchlaufen. Ein Index auf "tableB.p_id" sollte helfen, falls dieser noch nicht existiert, aber ich glaube dass wird immer ein Full-Table-Scan werden von zumindest von "A".

Du koenntest die Datenbank-Struktur aendern, und in "A" eine Spalte haben welche angibt ob es "B" gibt. Das wuerde erlauben einen Index darauf zu setzen, und muesste blitzeschnell sein. Nachteil ist, irgendein Teil der Geschaeftslogik muss die Spalte warten (oder ein Trigger in der Datenbank zum Beispiel).

Was eventuell auch geht ist dass du mal schaust ob die Datenbank "optimal" eingerichtet ist. Laeuft das Ding auf einer Kruecke mit 6GB RAM und du hast 12GB Daten? Dann mehr Speicher. Ist die Konfiguration vielleicht sub-optimal? Sowas.

Aber eventuell, ist die 1 MInute auch das schnellste was du bekommst (glaube ich aber ehrlich gesagt nicht, das muessten schon echt viele Daten sein dafuer).
 
Mart hat gesagt.:
Java: 
    SELECT COUNT ( DISTINCT a.ID )
    FROM table a, table b
    WHERE a.ID = b.P_id
    AND b.id IS NULL
joins sind nie gut...


EDIT : was mir jetzt erst auffällt .. warum hast du in Tabelle b zwei IDS?!
Zum Vergrößern anklicken....

Der Query liefert mir 0 obwohl ich 7 erwarte. hmm
In der Tabelle B ist die id und die foreign a_id
 
sorry doppel post. Das Forum kommt mit Safari nicht klar. Logged einen ständig aus obwohl man eingelogged ist. Oder Safari spackt
 
NicoDeluxe hat gesagt.:
Der Query liefert mir 0 obwohl ich 7 erwarte. hmm
In der Tabelle B ist die id und die foreign a_id
Zum Vergrößern anklicken....
ich weis ja nicht genau den aufbau was du willst... du musst das schon noch so modifiizieren dass es passt nur so hast du keinen join mehr
was besser ist


nur da du gefühlt

tablle a id join on tabelle b id wo b andere id null ist

keine ahnung was da was ist

so sehen die tabellen gefühlt aus
Java: 
A{

    int id PRIMARY EKY
}
B{
    int id PRIMARY KEY
    int id2 FOREIGN KEEY
}
 
Zuletzt bearbeitet von einem Moderator:
Robert Zenz hat gesagt.:
Also grundsaetzlich lass' dir von der Datenbank mal erklaeren wie die Query ausgefuehrt wird, in den meisten geht das mit einen vorangestellen "EXPLAIN", also einfach das SQL ausfuehren "EXPLAIN SELECT Count(id) ...". Und dann kann man sich fragen:

1. Kann ich einen Index legen auf die Felder welche im "where" stehen" um das zu beschleunigen?
2. Kann ich die Datenbank-Struktur aendern um einen Index zu ermoeglichen?
3. Hardware aendern? Server-Setup aendern?

In dem Fall bin ich mir offengestanden nicht sicher, ich glaube dass dir hier sogar ein Index nicht helfen wird am Ende, dass wird immer auf einen Full-Table-Scan hinauslaufen. Weil du willst wissen welche von "A" nicht in "B" vorkommen, dafuer muss man zumindest alle von "A" durchlaufen. Ein Index auf "tableB.p_id" sollte helfen, falls dieser noch nicht existiert, aber ich glaube dass wird immer ein Full-Table-Scan werden von zumindest von "A".

Du koenntest die Datenbank-Struktur aendern, und in "A" eine Spalte haben welche angibt ob es "B" gibt. Das wuerde erlauben einen Index darauf zu setzen, und muesste blitzeschnell sein. Nachteil ist, irgendein Teil der Geschaeftslogik muss die Spalte warten (oder ein Trigger in der Datenbank zum Beispiel).

Was eventuell auch geht ist dass du mal schaust ob die Datenbank "optimal" eingerichtet ist. Laeuft das Ding auf einer Kruecke mit 6GB RAM und du hast 12GB Daten? Dann mehr Speicher. Ist die Konfiguration vielleicht sub-optimal? Sowas.

Aber eventuell, ist die 1 MInute auch das schnellste was du bekommst (glaube ich aber ehrlich gesagt nicht, das muessten schon echt viele Daten sein dafuer).
Zum Vergrößern anklicken....
Danke, Index haben wir schon. Das sind in beiden Tabellen je 1,5 mio Einträge. Dauert halt. Wir werden einfach öfter in den Tabellen aufräumen damit sich nicht so viel ansammelt 🙂 Der Query von @LimDul brachte schon was, das genügt
 
Wenn du ohnehin nicht alle Eintraege wissen willst (weil es alte gibt), koennte ein Datumsfeld ("create_at"?) mit einem Index darauf helfen. Weil dann schraenkst du das einfach anhand dessen ein...oder nach einem anderen Kriterium in der Tabelle nach welchem ihr diese auch aufraeumen wuerdet.
 
Robert Zenz hat gesagt.:
Wenn du ohnehin nicht alle Eintraege wissen willst (weil es alte gibt), koennte ein Datumsfeld ("create_at"?) mit einem Index darauf helfen. Weil dann schraenkst du das einfach anhand dessen ein...oder nach einem anderen Kriterium in der Tabelle nach welchem ihr diese auch aufraeumen wuerdet.
Zum Vergrößern anklicken....
created_at haben wir sogar 😀 so einfach hab ich noch gar nicht gedacht. Da können wir evtl noch einschränken falls das bereinigen nix bringt daaaanke 🙂 Nach dem Bereinigen-Job sind es noch 250k Einträge die benötigt werden, viel besser
 
Mart hat gesagt.:
joins sind nie gut...
Zum Vergrößern anklicken....
Aber das ist doch auch ein Join, nur eben ohne JOIN. 😉
Mart hat gesagt.:
ich weis ja nicht genau den aufbau was du willst...
Zum Vergrößern anklicken....
Dem Muster könnte zum Beispiel die Aufgabe entsprechen, alle Artikel aufzufinden, die in keiner Bestellposition vorkommen.
NicoDeluxe hat gesagt.:
Der Query liefert mir 0 obwohl ich 7 erwarte. hmm
Zum Vergrößern anklicken....
Ich denke, der Ansatz von @Mart kann so auch nicht funktionieren. Da wird einfach das kartesische Produkt gebildet. Du interessierst dich aber ja gerade für die Zeilen, die in der anderen Tabelle nicht enthalten sind und die kommen im kartesischen Produkt überhaupt nicht vor.
 
SQL: 
WHERE  NOT EXISTS (SELECT 1 FROM transactions t WHERE t.product_id = p.id);
das noch von stack overflow geklaut
 
Zuletzt bearbeitet von einem Moderator:
Mart hat gesagt.:
wenn du join machst ises immer eine katastrophe...
Zum Vergrößern anklicken....
Kannst Du das bitte etwas erläutern? Zumal Du dann ein kartesisches Produkt als Alternative angibst. Die Queries dürften bei den meisten Datenbanken auf das gleichen Execution Plan hinaus laufen, daher ist es eher unkritisch. Aber bei einem Join ist die Vernüpfung wichtig und daher würde dieses immer direkt am Join halten wollen.

Probleme bei der Laufzeit sind oft auf Dinge zurück zu führen:
- Schlechte Bedingungen (Also z.B. wenn etwas berechnet werden muss oder ähnliches)
- Fehlende Indices (Hier können Datenbanken in der Regel auch Auskunft geben, welche Indices benutzt würden, wenn diese da wären)
- Unsinnige Abfragen (das wäre aber als Fehler abzustempeln - sowas wie Kartesische Produkte und so sind da z.B. übliche Fehler. Sowas findet man oft in Zusammenhang mit DISTINCT.
 
Mart hat gesagt.:
wenn du join machst ises immer eine katastrophe...
Zum Vergrößern anklicken....
Nein. Das ist ja gerade der Sinn und Zweck von RDBMS.

Bei solchen Dingen kenne ich - neben ungünstigen Anfragen - in der Regel drei Probleme:
  1. Ungünstiger Datentyp: wenn Du über VARCHAR joinst, bekommst Du ggf. schimmlige Füße. Das fällt bei wenigen Sätzen kaum auf, aber wenn Du größere Tabellen joinst merkst Du ganz deutlichen einen Unterschied. Der Grund ist ganz einfach: es muss viel mehr verglichen werden. Das kann auch zu Nebeneffekten (wie z. B. sortieren auf der Platte) führen.
  2. Um zu ermitteln, was nicht vorhanden ist, muss - wenn keine anderen Kriterien vorliegen - alles vorhandene betrachtet werden -> worst case. Viele Datensätze = viel Zeit. Das ist ein ganz grundsätzliches Problem.
  3. Der Query Optimizer erzeugt einen schlechten Ausführungsplan. Wenn z. B. für jeden Eintrag aus A ein Lookup auf B gemacht wird, ist das nicht optimal. Das beschriebene Problem könnte relativ gut mit zwei nebenherlaufenden Full-Index-Scans gelöst werden, wenn die Indizes eine Reihenfolge kennen.
 
mihe7 hat gesagt.:
1. Ungünstiger Datentyp: wenn Du über VARCHAR joinst, bekommst Du ggf. schimmlige Füße. Das fällt bei wenigen Sätzen kaum auf, aber wenn Du größere Tabellen joinst merkst Du ganz deutlichen einen Unterschied. Der Grund ist ganz einfach: es muss viel mehr verglichen werden. Das kann auch zu Nebeneffekten (wie z. B. sortieren auf der Platte) führen.
Zum Vergrößern anklicken....
Wobei ich hier noch anmerken würde, dass die Datenbanksysteme, die ich so kenne, selbst hier noch sehr brauchbare Ergebnisse liefern. Hier dürfte dann einmalig für jeden Key ein Hash berechnet werden (das kostet natürlich Zeit und Speicher, aber man ist dann bei einer relativ fixen Operation und das in O(n+m)). Wirklich schlimm wird es erst, wenn man etwas hat, das in O(n*m) verarbeitet wird ... und das am Besten mit noch mehr Tabellen.
 
mihe7 hat gesagt.:
Das dachte ich seinerzeit auch aber MySQL hat mich eines besseren belehrt
Zum Vergrößern anklicken....
Ja gut, aber das ist halt MySQL, von dem Ding darfst du nicht erwarten dass es irgendwo Leistung bringt wenn es 100.000 Datensaetze durchsuchen muss, oder du mit einer View eine View absuchst. Die ist in Ordnung fuer deine Homepage oder deine kleine Web-Anwendung, aber wenn du Leistung suchst musst ohnehin was anderes nehmen. Ich hatte mal bei einem Kunden eine OracleDB die war jenseits von 60GB wenn ich mich richtig erinnere (nur Plattenplatz), und die war trotzdem noch brauchbar schnell bei komplexen Abfragen. Das selbe auf PostgreSQL, denen sind komplexe Abfragen einfach egal wenn man die Daten richtig strukturiert hat. MySQL hier als Masz der Dinge zu nehmen ist so als wuerde ich Wordpad als Masz fuer das erstellen und gestalten eines Buches (fuer den Druck) nehmen. Klar, kannst du machen, aber du redest besser nie mit einem TeX-Typen darueber (weil der braucht hinten nach einen Psychiater).
 
Ja, das MySQL bashing hatte ich mir eigentlich sparen wollen ... aber ich habe mich da immer gewundert, dass sich das Teil überhaupt halten konnte. Es ist ja nicht so, dass es keine Alternativen wie PostgreSQL geben würde / gegeben hätte. Das hat zwar auch ganz schön eine Entwicklung mitmachen müssen, aber die waren damals dem mysql immer weit vorraus.

Mein schlimmstes Erlebnis war, als ich einmal eine mysql Datenbank übernehmen musste und da keine Foreign Keys waren. Dachte zuerst, dass der Entwickler ein DAU war, aber nein: Die Datenbank entstand zu einer Zeit, als das default Format keine Foreign Keys unterstützte und das Format, das diese (schon) konnte, nicht empfohlen war da nicht performant ... Und da gibt es sogar eine Enterprise Edition. Ist vielleicht ein Fehler von uns allen: Wir sind auch dumm, dass wir das "Ergebnis" unseres "Latrinen-Ganges" nicht verkaufen .... Enterprise Schei..... - gut und billig auf Ebay!

Und die Durchsetzung kam doch eigentlich durch diese Linux - Apache - MySQL - PHP Kombination, oder? Irgendwelche DAUs haben mit Linux vServern mit Apache, MySQL und PHP irgendwas gefrickelt, so dass Andere da sich gute, große Botnetze aufbauen konnten. Botnetz-Installer gab es damals dann auch genug in Form von PHP Content Management Systemen.

(Nein, nichts gegen PHP heute. Aber damals war das wirklich heftig - zumindest was ich so mitbekommen habe. Ist mit heutigen PHP Entwicklungen nicht vergleichbar. Da haben sich dann ja auch nach und nach alle "üblichen" Dinge integriert die für eine Software Entwicklung wichtig sind ...)
 
Um hier jetzt etwas vom Thema abzuschweifen...

kneitzel hat gesagt.:
Ja, das MySQL bashing hatte ich mir eigentlich sparen wollen ... aber ich habe mich da immer gewundert, dass sich das Teil überhaupt halten konnte. Es ist ja nicht so, dass es keine Alternativen wie PostgreSQL geben würde / gegeben hätte. Das hat zwar auch ganz schön eine Entwicklung mitmachen müssen, aber die waren damals dem mysql immer weit vorraus.
Zum Vergrößern anklicken....
Es ist halt so wie in vielen anderen Bereichen, die waren die Ersten so ziemlich, und dabei sind dann alle geblieben weil es irgendwie "Standard" ist. Sehen wir ja in sehr, sehr vielen Bereichen im Leben ("wos der Bauer net kennt frisst a net"). MySQL hat sich halt quasi als "die Web-Datenbank" etabliert, da kann man jetzt nur schwer etwas aendern. Damit hat jeder der sich mal eine kleine Webseite erstellt hat im Kopf "ah, ich brauche fuer mein Projekt eine Datenbank, ich kenne bereits MySQL, dann wird es MySQL werden". Mit der Zeit vermutlich weniger, aber unwahrscheinlich. Daher muss man Leuten halt immer sagen dass MySQL nicht unbedingt das Gelbe vom Ei ist. PostgreSQL und OracleDB spielen da halt in einer komplett anderen Liga (PostgreSQL kann Strukturaenderungen in Transaktionen! Du kannst ein "alter table" einfach zurueckrollen.). Als Anmerkung dazu, ich hatte ein paar mal die Gelegenheit Vortraege von Hans-Jürgen Schönig zu hoeren, der ist PostgreSQL Consultant, und die kann ich nur empfehlen, unterhaltsam und sehr informativ zu dem Thema. Wenn man die Gelegenheit dazu hat, unbedingt anhoeren.

Aber ich bin ja nur froh dass hier im Forum keiner mit MS Access ankommt...

kneitzel hat gesagt.:
Mein schlimmstes Erlebnis war, als ich einmal eine mysql Datenbank übernehmen musste und da keine Foreign Keys waren.
Zum Vergrößern anklicken....
Ja, das tut weh. Ich habe mal gelernt dass eine Datenbank-Model auch "alleine" funktionieren koennen sollte, also ein haendisches Insert sollte nie einen inkonsistenten Zustand erzeugen koennen. Aber das ist halt Luxus.

kneitzel hat gesagt.:
(Nein, nichts gegen PHP heute. Aber damals war das wirklich heftig - zumindest was ich so mitbekommen habe. Ist mit heutigen PHP Entwicklungen nicht vergleichbar. Da haben sich dann ja auch nach und nach alle "üblichen" Dinge integriert die für eine Software Entwicklung wichtig sind ...)
Zum Vergrößern anklicken....
Gefuehlt hat die gesamte Web-Schiene verzweifelt versucht die Welt neu zu erfinden, und merkt erst jetzt langsam dass alles was die sich in den letzten 20 Jahren muehsam erarbeitet haben, in der restlichen Welt seit 40 Jahren geloeste Probleme waren und sind. Oder das alles in JavaScript zu schreiben doch keine so tolle Idee waren (einige Tools migrieren nach Rust oder Go weil sie einfach um das zwanzigfache schneller sein koennen). Etwas schade halt, so im Nachhinein gesehen.
 
Nach Hans-Jürgen Schönig werde ich mal suchen - das hört sich interessant an.

Ansonsten kann ich Dir nur zustimmen - Du sprichst mir teilweise aus der Seele. Bezüglich Geschichte ist PostgreSQL deutlich älter - so man die Ingres und Post Ingres Zeiten dazu rechnet. Aber die Hauptentwicklung war sonst relativ parallel (war beides so ab 94 ...)
 
Ach ja - ganz vergessen: MS Access ist genial ... SQL Server dahinter ... echt super! ... Das war damals eine RAD Entwicklung für Business Applikationen 🙂

Und so um 96 herum habe ich damit auch mein Studium verdient. MS Access Applikationen mit einiger Logik drin wie Server Datenbank auf einem Netzwerkshare finden und Tabellen verknüpfen und so ... Und wenn beim Start die verknüpften Tabellen nicht zur Verfügung stehen, dann konnte der Benutzer die Server Datenbank auswählen ....

Aber nervig war damals, dass meine Hauptaufgabe war, die desaströsen Access Datenbanken anderer Studenten zu überarbeiten. Was da Informatik Studenten für ein Unverständnis von doch eigentlich trivialen Datenbank-Grundlagen hatten ... Aber ok: Datenbanken war damals ein Vertiefungsfach im Hauptstudium ... Das hatten manche noch nicht und andere sind dem aus dem Weg gegangen ....
 

Aktuelle Jobs

Neue Themen

Ähnliche Themen

Zeit Optimierung - bzw. ms Optimierung
Antworten: 35
JPA - Query.setFirstResult, Query.setMaxResults - intern?
Antworten: 10
PostgreSQL-Query in eine MicrosoftSQL-Query konvertieren - chatGPT hilft nur bedingt.
Antworten: 3
Fehlerhafte SQL Query
Antworten: 4
sql query in methode mit rückgabetyp
Antworten: 14

Zurück
Oben