Abfrageoptimierung

[Nersgatt]

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

[baumina]

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

[Jumpy]

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)

[p80286]

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

[Blup]

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.

[Sir Rufo]

Zitat von Jumpy:

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

[Nersgatt]

Zitat von Sir Rufo:

Zitat von Jumpy:

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!

[Blup]

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.