Access SQL Query optimieren?

[HCB]

Danke für die schnelle Antwort.

Delphi-Quellcode:

 where ABFDocOptFertig = false
  and ABFDocVisType = 1 //zur Tabelle ABFDoc

  and ABFPosEPreis > 0
  and ABFPosType in(0,7)
  and ABFPosNr <> '' 
  and ABFPosArtNr = :ArtNr // zur Tabelle ABFPos

Mastersource = sArtikel
MasterFields = sArtikel.ArtNr
IndexesActive = true

Frage: Was kann ich in Windows einstellen (Programme im Hintergrund ist aktiv) damit ich das verbessern kann, bzw. reicht die Hardware nicht aus? (16 gB RAM, SSD Festplatte).

LG Harry

[Delphi.Narium]

Über welche Datenmengen reden wir denn überhaupt? 16 Sekunden bei 100 Sätzen ist sicherlich lang, 16 Sekunden bei 100.000.000.000 eher nicht Wie groß ist die Datenbankdatei?

Nur hingedaddelt und ungetestet und zusätzlich keine Ahnung, ob Access mit 'ner Syntax in der Form zurechtkommt. Aber eventuell wird verständlich, worauf ich hinausmöchte. Und ob das dann auch noch schneller wird kommt auf Versuch und Irrtum an. Grundsätzlich versuche ich bei großen Datenmengen diese möglichst früh maximal einzuschränken, bevor per Join mehrere Tabellen zusammengefügt werden. (Und Ja, die Lesbarkeit von SQL-Statements kann darunter leiden. Kürzere Laufzeiten gehen leider zuweilen (massiv) zu Lasten der Lesbarkeit der SQLs.)

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
) c
group by
  ABFDocAuftragNr,
  ABFDocDatum,
  ABFDocKundeKurzbez,
  ABFdocVisType
order by
  ABFDocAuftragNr;

Den inner join sArtikel on c.ABFPosArtNr = sArtikel.ArtNr hab' ich rausgenommen, da für mich nicht erkennbar ist, ob und ggfls. wofür ein Join auf diese Tabelle erforderlich sein sollte. Im Ergebnis scheinen keine Daten aus sArtikel enthalten zu sein, für die Wherebedingungen scheint sie auch nicht benötigt zu werden. Dann ist sie in diesem SQL eigentlich auch überflüssig und frist nur Speicher und Laufzeit.

Bevor Du anfängst an Windows und / oder Hardware zu schrauben, schau bitte erstmal, ob Du datenbankseitig eine Beschleunigung hinbekommst. Datenträger haben nunmal Zugriffzeiten > 0, von daher werden Datenbankabfragen auch immer mit Laufzeiten > 0 ausgeführt. Und wenn es dann auch noch über ein Netzwerk geht, muss der APP PC (bei Access) halt die ganze Datenbankdatei "mal eben über die Leitung ziehen", um dann die Auswertung vornehmen zu können. Access ist halt keine Datenbank, bei der der Client sagen kann. "Lieber Datenbankserver gib mir mal die Daten entsprechend der Dir hiermit übergebenen Abfrage."

Letztlich sind wir dann aber wieder bei dem Thema: Access Datenbank langsam, vermutlich reden wir hier über das gleiche WW-System?

[HCB]

Es handelt sich um 180000 Datensätze in einer Master-Detail Beziehung.
Master = sArtikel (gibt ArtNr vor) > Detail = Abfrage aus ABFDok und ABFPos.

Ja, ich knappere immer noch an der Performance herum.
Bei jedem DataChange wird die Query aufgerufen. Ist nicht optimal, deshalb muss ich weitere Lösungen finden.

Danke für die Zeit die du dir nimmst.

LG Harry
P.S. Syntaxfehler in FROM-Klausel bei deinem Vorschlag. Mal sehen was ich daraus machen kann.

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