AGB  ·  Datenschutz  ·  Impressum  







Anmelden
Nützliche Links
Registrieren

Abfrageoptimierung

Ein Thema von Nersgatt · begonnen am 16. Okt 2014 · letzter Beitrag vom 16. Okt 2014
Antwort Antwort
Benutzerbild von Nersgatt
Nersgatt

Registriert seit: 12. Sep 2008
Ort: Emlichheim
693 Beiträge
 
Delphi 10.1 Berlin Professional
 
#1

Abfrageoptimierung

  Alt 16. Okt 2014, 11:32
Datenbank: MySql • Version: 5.5 • Zugriff über: egal
Moin,

ich versuche mal, mein Problem vereinfacht darzustellen.
Ausgangssituation:

Code:
Haupttabelle
  ID int,
  bezeichnung varchar(40)

Untertabelle
  id int,
  haupttabelle_id int,
  zahl int,
  nochEineZahl int
Nun erstelle ich eine Abfrage die ungefähr so aussieht:
Code:
select h.id, h.bezeichnung, sum(u.zahl) as summe
from haupttabelle
join untertabelle u on (u.haupttabelle_id = h.id);
Bis hier her hoffentlich alles klar.
Nun möchte ich die Abfrage erweitern. Und zwar so, dass nur die Datensätze der Haupttabelle ausgegeben werden, wo mindestens ein Datensatz in der Untertabelle mit einem bestimmten Kriterium existiert. Die Summe muss aber unverändert bleiben, also immer über alle Datensätze der Untertabelle gezogen werden.

Was also nicht geht ist das:
Code:
select h.id, h.bezeichnung, sum(u.zahl) as summe
from haupttabelle
join untertabelle u on (u.haupttabelle_id = h.id);
where u.nochEineZahl > 10
Das würde die ausgegebene Summe verändern, weil die Datensätze aus der Untertabelle eingeschränkt würden. Das Abfrageergebnis wäre falsch.

Funktionieren würde folgendes:
Code:
select h.id, h.bezeichnung, sum(u.zahl) as summe
from haupttabelle
join untertabelle u on (u.haupttabelle_id = h.id);
where exists(select id from untertabelle x where x.haupttabelle_id = h.id and x.nochEineZahl > 10)
Das würde das richtige Ergebnis ausgeben. Jedoch ist diese Variant Performancemäßig so ziemlich das Schlimmste was man machen kann. Die Subquery würde für jeden Datensatz der Haupttabelle einmal ausgeführt werden. Wenn ich in der Haupttabelle 1000 Datensätze habe, würde das also in der Ausführung von 1000x der Subquery resultieren. Außerdem noch ein Full Table Scan der Haupttabelle. Das kann verheerend sein.

Eine weitere Möglichkeit, die mir einfällt, wäre es, auf der Untertabelle Trigger zu erstellen. Außerdem in der Haupttabelle ein weiteres (redundantes) Feld erzeugen, welches angibt, ob es einen Datensatz gibt, der dem Kriterium entspricht. Das Feld müsste durch den Trigger gepflegt werden. Da mein Kriterium (u.nochEineZahl > 10) nicht variabel ist, könnte man das machen.
So könnte ich dann auf die Haupttabelle filtern:
Code:
select h.id, h.bezeichnung, sum(u.zahl) as summe
from haupttabelle
join untertabelle u on (u.haupttabelle_id = h.id);
where h.DatensatzInUntertabelleMitKriteriumExistiert = 1
Aber irgendwie sperrt sich dagegen mein Datenbankmodellierherz. Ich hätte ja dann redundante Daten, was ich immer versuche zu vermeiden.

Fällt jemandem noch eine Möglichkeit der Abfrage ein, die performant ist, aber keine Trigger benötigt?

Danke!
Jens
Jens
  Mit Zitat antworten Zitat
Benutzerbild von baumina
baumina

Registriert seit: 5. Mai 2008
Ort: Oberschwaben
1.275 Beiträge
 
Delphi 11 Alexandria
 
#2

AW: Abfrageoptimierung

  Alt 16. Okt 2014, 12:00
Ob das "besser" als das exists ist, kann ich dir auch nicht sagen:

Code:
select h.id, h.bezeichnung,
      (select sum(x.zahl) from untertabelle x where x.haupttabelle_id = h.id) summe
 from haupttabelle
 join untertabelle u on (u.haupttabelle_id = h.id)
 where u.nochEineZahl > 10
Hinter dir gehts abwärts und vor dir steil bergauf ! (Wolfgang Ambros)
  Mit Zitat antworten Zitat
Jumpy

Registriert seit: 9. Dez 2010
Ort: Mönchengladbach
1.733 Beiträge
 
Delphi 6 Enterprise
 
#3

AW: Abfrageoptimierung

  Alt 16. Okt 2014, 12:13
und noch eine wahrscheinlich nicht wirklich performantere Variante (oder wird der Subselect im where so nur 1x ausgeführt?):

SQL-Code:
select h.id, h.bezeichnung, sum(u.zahl) as summe
from haupttabelle h
join untertabelle u on (u.haupttabelle_id = h.id)
where h.id in (Select Distinct haupttabelle_id From untertabelle where nocheinezahl>10)
Ralph
  Mit Zitat antworten Zitat
Benutzerbild von p80286
p80286

Registriert seit: 28. Apr 2008
Ort: Stolberg (Rhl)
6.659 Beiträge
 
FreePascal / Lazarus
 
#4

AW: Abfrageoptimierung

  Alt 16. Okt 2014, 12:39
wie wäre es mit
Code:
select h.Bezeichnung, sum(u.zahl),u1.info
from haupt h join unter u on (h.id=u.haupt_id)
       left join (select haupt_id, info from unter where ...) u1 on (h.id=u1.haupt_id)
where.......
Damit ist der Datensatz aus der Haupttabelle durch "Info" markiert.

gruß
K-H
Programme gehorchen nicht Deinen Absichten sondern Deinen Anweisungen
R.E.D retired error detector

Geändert von p80286 (16. Okt 2014 um 12:42 Uhr)
  Mit Zitat antworten Zitat
Blup

Registriert seit: 7. Aug 2008
Ort: Brandenburg
1.429 Beiträge
 
Delphi 10.4 Sydney
 
#5

AW: Abfrageoptimierung

  Alt 16. Okt 2014, 12:55
Mal schaun was der Optimierer daraus macht:
Code:
select distinct u1.haupttabelle_id id, h.bezeichnung, sum(u2.zahl) as summe
from     untertabelle u1
left join haupttabelle h  on (u1.haupttabelle_id = h.id)
left join untertabelle u2  on (u2.haupttabelle_id = h.id)
where    (u1.nochEineZahl > 10)
Insbesondere der Ausführungsplan währe interessant.
  Mit Zitat antworten Zitat
Benutzerbild von Sir Rufo
Sir Rufo

Registriert seit: 5. Jan 2005
Ort: Stadthagen
9.454 Beiträge
 
Delphi 10 Seattle Enterprise
 
#6

AW: Abfrageoptimierung

  Alt 16. Okt 2014, 12:58
und noch eine wahrscheinlich nicht wirklich performantere Variante (oder wird der Subselect im where so nur 1x ausgeführt?):

SQL-Code:
select h.id, h.bezeichnung, sum(u.zahl) as summe
from haupttabelle h
join untertabelle u on (u.haupttabelle_id = h.id)
where h.id in (Select Distinct haupttabelle_id From untertabelle where nocheinezahl>10)
AFAIK wird hier die Unterabfrage einmal ausgeführt
Kaum macht man's richtig - schon funktioniert's
Zertifikat: Sir Rufo (Fingerprint: ‎ea 0a 4c 14 0d b6 3a a4 c1 c5 b9 dc 90 9d f0 e9 de 13 da 60)
  Mit Zitat antworten Zitat
Benutzerbild von Nersgatt
Nersgatt

Registriert seit: 12. Sep 2008
Ort: Emlichheim
693 Beiträge
 
Delphi 10.1 Berlin Professional
 
#7

AW: Abfrageoptimierung

  Alt 16. Okt 2014, 13:01
und noch eine wahrscheinlich nicht wirklich performantere Variante (oder wird der Subselect im where so nur 1x ausgeführt?):

SQL-Code:
select h.id, h.bezeichnung, sum(u.zahl) as summe
from haupttabelle h
join untertabelle u on (u.haupttabelle_id = h.id)
where h.id in (Select Distinct haupttabelle_id From untertabelle where nocheinezahl>10)
AFAIK wird hier die Unterabfrage einmal ausgeführt
Ja, das sehe ich auch so. Die Variante gefällt mir spontan erst mal am besten.
Danke!
Jens
  Mit Zitat antworten Zitat
Blup

Registriert seit: 7. Aug 2008
Ort: Brandenburg
1.429 Beiträge
 
Delphi 10.4 Sydney
 
#8

AW: Abfrageoptimierung

  Alt 16. Okt 2014, 13:49
Abfragen mit "in" sind bei sehr vielen Werten häufig sehr teuer (Laufzeit und Speicherverbrauch).
Auf jeden Fall mit realen Daten und Anwendungsfällen testen.
  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 18:29 Uhr.
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
LinkBacks Enabled by vBSEO © 2011, Crawlability, Inc.
Delphi-PRAXiS (c) 2002 - 2023 by Daniel R. Wolf, 2024 by Thomas Breitkreuz