Access SQL Query optimieren?

[Delphi.Narium]

Teil Dir das SQL von innen nach außen auf:

Funktioniert das?

SQL-Code:

    select ABFDocAuftragNr, ABFDocDatum, ABFDocKundeKurzbez, ABFDocAuftragID
    from ABFDoc
    where ABFDocOptFertig = false
      and ABFDocVisType = 1

Funktioniert das?

SQL-Code:

    select ABFPosArtNr, ABFPosMenge
    from ABFPos
    where ABFPosEPreis > 0
      and ABFPosType in(0,7)
      and ABFPosNr <> '' 
      and ABFPosArtNr = :ArtNr

Funktioniert das?

SQL-Code:

select 
* 
from
(
  (
    select ABFDocAuftragNr, ABFDocDatum, ABFDocKundeKurzbez, ABFDocAuftragID
    from ABFDoc
    where ABFDocOptFertig = false
      and ABFDocVisType = 1
  ) a
  inner join
  (
    select ABFPosArtNr, ABFPosMenge
    from ABFPos
    where ABFPosEPreis > 0
      and ABFPosType in(0,7)
      and ABFPosNr <> '' 
      and ABFPosArtNr = :ArtNr
  ) b on b.ABFPosAuftragID = a.ABFDocAuftragID
)

Es kann sein, dass Access mit der Art der Schachtelung der Selects nicht zurechtkommt.

Hab' grade mal mit FireBird ein ähnliches Konstrukt hingedaddelt. dort muss vor dem group by das c weg.

Geht es so?

SQL-Code:

select
  ABFDocAuftragNr,
  ABFDocDatum,
  ABFDocKundeKurzbez,
  IIF(Count(ABFDocAuftragID) > 1,Max(ABFPosMenge) - Min(ABFPosMenge), Max(ABFPosMenge)) as offen
from
(
  (
    select ABFDocAuftragNr, ABFDocDatum, ABFDocKundeKurzbez, ABFDocAuftragID
    from ABFDoc
    where ABFDocOptFertig = false
      and ABFDocVisType = 1
  ) a
  inner join
  (
    select ABFPosArtNr, ABFPosMenge
    from ABFPos
    where ABFPosEPreis > 0
      and ABFPosType in(0,7)
      and ABFPosNr <> '' 
      and ABFPosArtNr = :ArtNr
  ) b on b.ABFPosAuftragID = a.ABFDocAuftragID
)
group by
  ABFDocAuftragNr,
  ABFDocDatum,
  ABFDocKundeKurzbez,
  ABFdocVisType
order by
  ABFDocAuftragNr;

[HCB]

SQL Code 1 läuft,
SQL Code 2 läuft,
SQL Code 3: 2 Parameter wurden erwartet, aber es wurden zu wenig Parameter übergeben bzw. Mit '*' ausgewählte Felder können nicht gruppiert werden
SQL Code 4: 2 Parameter wurden erwartet, aber es wurden zu wenig Parameter übergeben

[Delphi.Narium]

Das liegt eventuell hierdran:  and ABFPosArtNr = :ArtNr

Wenn Du das als reines SQL ausführen lässt, musst Du statt :ArtNr einen konkreten Wert eingeben.

Wobei:

Access neigt dazu bei Schreibfehlern in Spaltennamen, teils auch bei Syntaxfehlern, ... auch schonmal von fehlenden Parameterangaben zu sprechen.

Da es im SQL nur einen Parameter gibt, Access aber zwei Parameter bemängelt, liegt es entweder an Schreib- und/oder Syntaxfehlern oder Access kommt mit dieser Art von SQL-Verschachtelung nicht zurecht.

Ohne Access und die entsprechende Datenbank ist das recht schwierig (eigentlich garnicht) nachzuvollziehen.

[HCB]

Ja ich weiß: and ABFPosArtNr = :ArtNr, da hb ich schon die Artikelnummer zum Testen eingegebn: and ABFPosArtNr = '10621008'
Artikelnummer ist ein Textfeld.

[hoika]

Hallo,
wie sieht es mit fehlenden Indices aus?

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