Datenbank: Access mdb • Version: 2003 • Zugriff über: FireDac
Access SQL Query optimieren?
Hallo SQL Experten,
ich brauche mal wieder euere Hilfe in Sachen SQL Abfrage in einer mdb Access Datenbank. Für den ersten Aufruf meiner Abfrage dauert es 16 sek. bis das Ergebnis korrekt angezeigt wird. Bei einer anschließenden nochmaligen Abfrage dauert es nur die Hälfte der Zeit. Die mdb Datei liegt auf einem Win 10 Rechner im lokalen Netzwerk. Der App PC greift darauf zu. Meine Frage1: Warum dauert es bei der Erstabfrage so lange und auf welchem Rechner wird die Abfrage gecached (Win10 Netzwerkrechner oder lokaler PC) weil es dann schneller geht? Frage2: Kann man durch Optimierung der SQL-Query ein schnelleres Resultat erreichen? Hier mein Query:
Delphi-Quellcode:
Wenn einer mir helfen könnte wäre ich sehr sehr dankbar.
SELECT ABFDocAuftragNr, ABFDocDatum,ABFDocKundeKurzbez,
IIF(Count(ABFDocAuftragID)>1,Max(ABFPosMenge)-Min(ABFPosMenge),Max(ABFPosMenge)) as offen FROM (ABFDok INNER JOIN ABFPos ON ABFPos.ABFPosAuftragID = ABFDok.ABFDocAuftragID) INNER JOIN sArtikel ON ABFPos.ABFPosArtNr=sArtikel.ArtNr where ABFDocOptFertig = false and ABFDocVisType= 1 and ABFPosEPreis >0 and ABFPosType in(0,7) and ABFPosNr <>'' and ABFPosArtNr = :ArtNr GROUP BY ABFDocAuftragNr,ABFDocDatum,ABFDocKundeKurzbez,ABFdocVisType order by ABFDocAuftragNr; LG Harry |
AW: Access SQL Query optimieren?
Das kenne ich auch von FireBird.
Ab der zweiten Abfrage wird's schneller, weil ein Teil der Datenbankdatei bereits gelesen wurde und damit im Cache der Festplatte liegt. Wenn Du lange wartest und in der Zeit andere Programme die Festplatte extensiv nutzen, wird die Abfrage wieder einmal langsamer werden und beim zweiten Mal wieder schneller. Das Problem liegt eher an Windows und der Hardware als an der Datenbank.
SQL-Code:
Zu welcher Tabelle gehören diese Einschränkungen?
SELECT
ABFDocAuftragNr, ABFDocDatum, ABFDocKundeKurzbez, IIF(Count(ABFDocAuftragID) > 1,Max(ABFPosMenge) - Min(ABFPosMenge), Max(ABFPosMenge)) as offen FROM ( ABFDok INNER JOIN ABFPos ON ABFPos.ABFPosAuftragID = ABFDok.ABFDocAuftragID ) INNER JOIN sArtikel ON ABFPos.ABFPosArtNr = sArtikel.ArtNr where ABFDocOptFertig = false and ABFDocVisType = 1 and ABFPosEPreis > 0 and ABFPosType in(0,7) and ABFPosNr <>'' and ABFPosArtNr = :ArtNr GROUP BY ABFDocAuftragNr, ABFDocDatum, ABFDocKundeKurzbez, ABFdocVisType order by ABFDocAuftragNr;
SQL-Code:
Eventuell solltest Du die entsprechende(n) Tabelle(n) erst per Wherebedingung einschränken und dann per Join die anderen Tabellen dazunehmen.
where ABFDocOptFertig = false
and ABFDocVisType = 1 and ABFPosEPreis > 0 and ABFPosType in(0,7) and ABFPosNr <> '' and ABFPosArtNr = :ArtNr Wie sehen die Tabellendefinitionen aus? Wie sehen die Indexdefinitionen aus? |
AW: Access SQL Query optimieren?
Danke für die schnelle Antwort.
Delphi-Quellcode:
Mastersource = sArtikel
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 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 |
AW: Access SQL Query optimieren?
Ü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:
Den
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;
Delphi-Quellcode:
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.
inner join sArtikel on c.ABFPosArtNr = sArtikel.ArtNr
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? |
AW: Access SQL Query optimieren?
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. |
AW: Access SQL Query optimieren?
Teil Dir das SQL von innen nach außen auf:
Funktioniert das?
SQL-Code:
Funktioniert das?
select ABFDocAuftragNr, ABFDocDatum, ABFDocKundeKurzbez, ABFDocAuftragID
from ABFDoc where ABFDocOptFertig = false and ABFDocVisType = 1
SQL-Code:
Funktioniert das?
select ABFPosArtNr, ABFPosMenge
from ABFPos where ABFPosEPreis > 0 and ABFPosType in(0,7) and ABFPosNr <> '' and ABFPosArtNr = :ArtNr
SQL-Code:
Es kann sein, dass Access mit der Art der Schachtelung der Selects nicht zurechtkommt.
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 ) 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; |
AW: Access SQL Query optimieren?
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 |
AW: Access SQL Query optimieren?
Das liegt eventuell hierdran:
SQL-Code:
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. |
AW: Access SQL Query optimieren?
Ja ich weiß: and ABFPosArtNr = :ArtNr, da hb ich schon die Artikelnummer zum Testen eingegebn: and ABFPosArtNr = '10621008'
Artikelnummer ist ein Textfeld. |
AW: Access SQL Query optimieren?
Hallo,
wie sieht es mit fehlenden Indices aus? |
AW: Access SQL Query optimieren?
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. |
AW: Access SQL Query optimieren?
Habe nochmal etwas "rumgedaddelt" und bin zu folgendem Ergebnis gekommen:
SQL-Code:
Da Access zwei Parameter angemeckert hat und falsche bzw. fehlende Spalten in Fehlermeldungen gerne als Parameter betitel, hab' ich da mal gesucht.
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; 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. |
AW: Access SQL Query optimieren?
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 |
AW: Access SQL Query optimieren?
In meinem obigen SQL ist / war ein Fehler, den ich oben korrigiert habe. Es muss
Delphi-Quellcode:
statt
IIF(Count_ABFPosAuftragID > 1,Max_ABFPosMenge - Min_ABFPosMenge, Max_ABFPosMenge) as offen
Delphi-Quellcode:
heißen.
IIF(ABFPosAuftragID > 1,Max_ABFPosMenge - Min_ABFPosMenge, Max_ABFPosMenge) as offen
Bleiben wir einfach bei dem obigen SQL. Für die Tabelle ABFDoc:
SQL-Code:
Das packst Du Dir, wie andere SQLs auch, in Delphi in die SQL-Eigenschaft einer Query und führst die dann per
create view v_ABFDoc as
select ABFDocAuftragID, ABFDocAuftragNr, ABFDocDatum, ABFDocKundeKurzbez from ABFDoc where ABFDocOptFertig = false and ABFDocVisType = 1 group by ABFDocAuftragID, ABFDocAuftragNr, ABFDocDatum, ABFDocKundeKurzbez
Delphi-Quellcode:
aus. Das muss nur einmalig für jede View geschehen. Willst oder musst Du mal 'ne View löschen, geht das mit
qryDeineQuery.ExecSQL;
Delphi-Quellcode:
Analog dazu für die Tabelle ABFPos:
qryDeineQuery.SQL.Clear;
qryDeineQuery.SQL.Add('drop viewname'); qryDeineQuery.ExecSQL;
SQL-Code:
Danach sollte aus dem "großen" SQL dann dashier werden können:
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
SQL-Code:
Statt v_ABFDoc und v_ABFPos kannst Du auch eigene (aussagefähigere) Namen für die Views wählen.
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; Vermutlich lässt es sich noch weiter "verkürzen":
SQL-Code:
Wenn's noch "einfacher" werden soll:
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;
SQL-Code:
Im Programm wäre der Aufruf dann:
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 );
SQL-Code:
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.
select
ABFDocAuftragNr, ABFDocDatum, ABFDocKundeKurzbez, offen from v_ABFDoc_ABFDoc_Mengen where ABFPosArtNr = :ArtNr order by ABFDocAuftragNr; Und wieder gilt: Ungetest hingedaddelt, keine Ahnung, ob ich da neue / weitere Fehler eingebaut habe. |
AW: Access SQL Query optimieren?
@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 |
AW: Access SQL Query optimieren?
@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:
Wie würde das hier mit den Views funktionieren?
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 Ich möchte dich wirklich nicht beanspruchen, eilt nicht. Vielleich wenn du wieder mal Zeit und Lust hast. LG Harry |
AW: Access SQL Query optimieren?
SQL-Code:
Im SQL dann "nur noch"
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 )
SQL-Code:
select * from v_Sinnvollername where BestPosArtNr = :ArtNr order by BestDocErfNr asc
Hoffentlich hab' ich da jetzt keine gravierenden Fehler eingebaut. |
AW: Access SQL Query optimieren?
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 |
AW: Access SQL Query optimieren?
Zitat:
Wenn das
Delphi-Quellcode:
kein Copy&Paste-Fehler beim Einfügen hier im Forum ist, dann fehlt da ein Leerzeichen:
innerjoin
Delphi-Quellcode:
inner join
Ansonsten sehe ich hier momentan keine Chance, da irgendwie sinnvoll weiterhelfen zu können. |
AW: Access SQL Query optimieren?
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 |
Alle Zeitangaben in WEZ +1. Es ist jetzt 10:27 Uhr. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
LinkBacks Enabled by vBSEO © 2011, Crawlability, Inc.
Delphi-PRAXiS (c) 2002 - 2023 by Daniel R. Wolf, 2024 by Thomas Breitkreuz