Access SQL Query optimieren?

[Delphi.Narium]

In meinem obigen SQL ist / war ein Fehler, den ich oben korrigiert habe. Es muss IIF(Count_ABFPosAuftragID > 1,Max_ABFPosMenge - Min_ABFPosMenge, Max_ABFPosMenge) as offen statt IIF(ABFPosAuftragID > 1,Max_ABFPosMenge - Min_ABFPosMenge, Max_ABFPosMenge) as offen heißen.

Bleiben wir einfach bei dem obigen SQL. Für die Tabelle ABFDoc:

SQL-Code:

create view v_ABFDoc as
   select
      ABFDocAuftragID,
      ABFDocAuftragNr,
      ABFDocDatum,
      ABFDocKundeKurzbez
    from ABFDoc
    where ABFDocOptFertig = false
      and ABFDocVisType = 1
    group by
      ABFDocAuftragID,
      ABFDocAuftragNr,
      ABFDocDatum,
      ABFDocKundeKurzbez

Das packst Du Dir, wie andere SQLs auch, in Delphi in die SQL-Eigenschaft einer Query und führst die dann per qryDeineQuery.ExecSQL; aus. Das muss nur einmalig für jede View geschehen. Willst oder musst Du mal 'ne View löschen, geht das mit

Delphi-Quellcode:

qryDeineQuery.SQL.Clear;
qryDeineQuery.SQL.Add('drop viewname');
qryDeineQuery.ExecSQL;

Analog dazu für die Tabelle ABFPos:

SQL-Code:

create view v_ABFPos as
   select
      ABFPosAuftragID,
      ABFPosArtNr,
      Max(ABFPosMenge) as Max_ABFPosMenge,
      Min(ABFPosMenge) as Min_ABFPosMenge,
      Count(ABFPosAuftragID) as Count_ABFPosAuftragID
    from ABFPos
    where ABFPosEPreis > 0
      and ABFPosType in(0,7)
      and ABFPosNr <> ''
    group by
      ABFPosArtNr,
      ABFPosAuftragID

Danach sollte aus dem "großen" SQL dann dashier werden können:

SQL-Code:

select
  ABFDocAuftragNr,
  ABFDocDatum,
  ABFDocKundeKurzbez,
  IIF(Count_ABFPosAuftragID > 1,Max_ABFPosMenge - Min_ABFPosMenge, Max_ABFPosMenge) as offen
from
(
  (
    select ABFDocAuftragID, ABFDocAuftragNr, ABFDocDatum, ABFDocKundeKurzbez 
    from v_ABFDoc
  ) a
  inner join
  (
    select ABFPosAuftragID, ABFPosArtNr, Max_ABFPosMenge, Min_ABFPosMenge, Count_ABFPosAuftragID 
    from v_ABFPos 
    where ABFPosArtNr = :ArtNr
  ) b on b.ABFPosAuftragID = a.ABFDocAuftragID
)
order by
  ABFDocAuftragNr;

Statt v_ABFDoc und v_ABFPos kannst Du auch eigene (aussagefähigere) Namen für die Views wählen.

Vermutlich lässt es sich noch weiter "verkürzen":

SQL-Code:

select
  ABFDocAuftragNr,
  ABFDocDatum,
  ABFDocKundeKurzbez,
  IIF(Count_ABFPosAuftragID > 1,Max_ABFPosMenge - Min_ABFPosMenge, Max_ABFPosMenge) as offen
from
(
  select * from v_ABFDoc
  inner join v_ABFPos on ABFPosAuftragID = ABFDocAuftragID
  where ABFPosArtNr = :ArtNr
)
order by
  ABFDocAuftragNr;

Wenn's noch "einfacher" werden soll:

SQL-Code:

create view v_ABFDoc_ABFDoc_Mengen as
select
  ABFDocAuftragNr,
  ABFDocDatum,
  ABFDocKundeKurzbez,
  ABFPosArtNr,
  IIF(Count_ABFPosAuftragID > 1,Max_ABFPosMenge - Min_ABFPosMenge, Max_ABFPosMenge) as offen
from
(
  select * from v_ABFDoc
  inner join v_ABFPos on ABFPosAuftragID = ABFDocAuftragID
);

Im Programm wäre der Aufruf dann:

SQL-Code:

select 
  ABFDocAuftragNr,
  ABFDocDatum,
  ABFDocKundeKurzbez,
  offen
from v_ABFDoc_ABFDoc_Mengen 
where ABFPosArtNr = :ArtNr
order by
  ABFDocAuftragNr;

Was ich nicht weiß, welche Variante unter Laufzeitgesichtspunkten die beste ist. Bei der (sql-technisch) kürzesten Variante, dürfte die Verarbeitungsmenge (und damit die Laufzeit?) die größte sein, da die Einschränkung auf die ArtNr erst zum Schluss erfolgt, es würde mich nicht wundern, wenn die Version ohne Views zu kürzeren Laufzeiten führt, da hier die Einschränkung der Datenmenge sehr früh erfolgt. Aber das wirst Du wohl oder übel durch Ausprobieren ermitteln müssen. Die letzte Variante ist im Delphiquelltext aber garantiert die am einfachsten zu verstehende.

Und wieder gilt: Ungetest hingedaddelt, keine Ahnung, ob ich da neue / weitere Fehler eingebaut habe.