Access SQL Query optimieren?

[Jasocul]

Das schreit bei mir förmlich nach Views. Wenn das nicht "erlaubt" ist, könnte man die Basis-Abfragen auch als "with" ins SQL aufnehmen, falls Access das kennt.
Du brauchst aus der einen Tabelle Max und Min. Warum sollte man das jedesmal berechnen?
Die Grundabfragen (where-Bedingungen) sind in ihrer Basis auch immer gleich. Nur die ArtNr wird verändert.
Es stellt sich höchstens noch die Frage, ob die zwei Views (jede der gejointen Tabellen bekommt eine eigene View) oder drei (zusätzlich die Ergebnis-View). Eventuell reicht es nur die Ergebnis-View zu erstellen.

[Delphi.Narium]

Habe nochmal etwas "rumgedaddelt" und bin zu folgendem Ergebnis gekommen:

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 ABFDoc
    where ABFDocOptFertig = false
      and ABFDocVisType   = 1
    group by
      ABFDocAuftragID,
      ABFDocAuftragNr,
      ABFDocDatum,
      ABFDocKundeKurzbez
  ) a
  inner join
  (
    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     <> ''
      and ABFPosArtNr  = :ArtNr
    group by
      ABFPosArtNr,
      ABFPosAuftragID
  ) b on b.ABFPosAuftragID = a.ABFDocAuftragID
)
order by
  ABFDocAuftragNr;

Da Access zwei Parameter angemeckert hat und falsche bzw. fehlende Spalten in Fehlermeldungen gerne als Parameter betitel, hab' ich da mal gesucht.

Im Group By war die Spalte ABFdocVisType enthalten, die in keinem SQL ausgewählt wurde und im Ergebnis auch nicht enthalten ist. Die hab' ich einfach entfernt. Da ABFdocVisType immer 1 sein muss, dürfte eine Gruppierung nicht erforderlich sein, da es ja nur einen Wert gibt.
In das Select zur Tabelle ABFPos habe ich die Spalte ABFPosAuftragID mit aufgenommen. Damit hätten wir (mit etwas Glück) die zwei bemängelten Parameter "erwischt".

Dazu hab' ich die Min/Max-Ermittlung und das Zählen der AuftragID in die "inneren" Selects aufgenommen. Eventuell hilft das bei der Geschwindigkeit.

Da das Delphiprogramm auf die Datenbank eines Warenwirtschaftssystem zugreift, dürfte das Anlegen von (durchaus) sinnvollen Views eher nicht möglich sein, da hier ja dann in das Datenmodell / die Struktur eines Fremdsystems eingegriffen wird. Das Anlegen ggfls. erforderlicher Indices wird wahrscheinlich, aus eben diesem Grund, wohl auch nicht möglich sein.

[HCB]

Danke an alle für euere liebe Unterstützung.

@delphi.narium
Danke für deine Arbeit. Jetzt funktioniert deine SQL Query perfekt!!!
Noch 'ne Frage: Wie kann ich zum Testen in einer Kopie der mdb-Datei die Views anlegen?

LG Harry

[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.

[HCB]

@delphi.narium
Danke für Deine Zeit und Mühe. Ich werde das mal heute abend testen. Du hast dir einen Kasten Bier verdient.

LG Harry

[HCB]

@delphi.narium
Das mit den Views und deinem SQL Code hat sagenhaft funktioniert!! Ich lerne jetzt wieder ein wenig dazu! Vielen, vielen Dank.

Eine andere SQL Optimierung wäre wahrscheinlich auch hier möglich:

Delphi-Quellcode:

SELECT A.BestDocErfNr, A.BestDocDatum, A.BestDocLiefNr,A.BestDocKurzbez,U.BestPosAusstand,U.BestPosArtNr,U.BestPosBestNr,U.BestposName1,U.BestPosEPreis,L.ArtAltArtNr
FROM (BestDok As A inner JOIN BestPos As U
On A.BestDocID = U.BestPosDocID )
INNER JOIN sArtikel as L
      ON U.BestPosArtNr=L.ArtNr
WHERE A.BestDocOptGeliefert = false and U.BestPosAusstand  > 0 and A.BestDocType = 0
and U.BestPosArtNr = :ArtNr
order by  A.BestDocErfNr asc

Wie würde das hier mit den Views funktionieren?
Ich möchte dich wirklich nicht beanspruchen, eilt nicht. Vielleich wenn du wieder mal Zeit und Lust hast.

LG Harry

[Delphi.Narium]

SQL-Code:

create view v_Sinnvollername as
select
  BestDocErfNr, 
  BestDocDatum,   
  BestDocLiefNr,
  BestDocKurzbez,
  BestPosAusstand,
  BestPosArtNr,
  BestPosBestNr,
  BestposName1,
  BestPosEPreis,
  ArtAltArtNr
from
(
  (
    select 
      BestDocID, 
      BestDocErfNr, 
      BestDocDatum, 
      BestDocLiefNr, 
      BestDocKurzbez
    from BestDok 
    where BestDocOptGeliefert = false
      and BestDocType = 0
  ) A 
  inner join 
  (
    select
      BestPosArtNr,
      BestPosAusstand,
      BestPosArtNr,
      BestPosBestNr,
      BestposName1,
      BestPosEPreis
    from BestPos
    where BestPosAusstand > 0
  ) U on A.BestDocID = U.BestPosDocID
  inner join 
  (
    select 
      ArtNr,
      ArtAltArtNr
    from sArtikel
  ) L on U.BestPosArtNr = L.ArtNr
)

Im SQL dann "nur noch"

select * from v_Sinnvollername where BestPosArtNr = :ArtNr order by BestDocErfNr asc

Hoffentlich hab' ich da jetzt keine gravierenden Fehler eingebaut.

[HCB]

Bin am Testen, es kommt unter Delphi folgene Fehlermeldung:

Syntaxfehler (fehlender Operator) in Abfrageausdruck
A.BestDocId = U.BestPosDocId
innerjoin
(
select
ArtNr,
ArtAltArtNr
from sArtikel
) L on U.BestPosArtNr = L.ArtNr

[Delphi.Narium]

Zitat von HCB:

Bin am Testen, es kommt unter Delphi folgene Fehlermeldung:

Syntaxfehler (fehlender Operator) in Abfrageausdruck
A.BestDocId = U.BestPosDocId
innerjoin
(
select
ArtNr,
ArtAltArtNr
from sArtikel
) L on U.BestPosArtNr = L.ArtNr

Schwierig ohne das vollständige SQL (und ohne DB mit der entsprechenden Tabellenstruktur zum Testen).

Wenn das innerjoin kein Copy&Paste-Fehler beim Einfügen hier im Forum ist, dann fehlt da ein Leerzeichen: inner join

Ansonsten sehe ich hier momentan keine Chance, da irgendwie sinnvoll weiterhelfen zu können.

[HCB]

Ok, vielen Dank.
Inner join ist schon richtig ohne Leerzeichen beim Query eingetragen.
Muss ich halt mal sehen wie ich das ganze gebacken bekomme. Irgendwie gibts immer andere Lösungsansätze. Try and Error

LG Harry