SQL Nachhilfe

[HCB]

Hallo liebe SQL-Experten,
ich bin hier am Optimieren meiner SQL Queries. Folgende Abfrage funktioniert wie es sein soll, jedoch ist diese mit ca. 5 sek. etwas zu langsam. Da kann man sicher noch was rausholen:

Delphi-Quellcode:

SELECT s.ArtNr, s.ArtName1,s.ArtGruppe, s.ArtZusInfo4
FROM   sArtikel s
WHERE  NOT EXISTS (SELECT *
                   FROM   ArtLief l
                   WHERE  s.ArtNr = l.ArtLiefArtNr
                   and l.ArtLiefLiefNr = s.ArtZusInfo4
                   )
and s.ArtInaktiv = false
and s.ArtZusInfo4 like 'K*'
order by  s.ArtNr;

Es wäre sehr nett, wenn mir wieder jemand helfen könnte.

LG Harry

[jaenicke]

Auf den ersten Blick würde ich vermuten, dass ein left join mit Abfrage, ob das gejointe Feld null ist, schneller ist.

Denn so muss ja für jeden Datensatz eine Abfrage über die ganze Tabelle laufen. Wenn dann die dabei abgefragten Felder nicht in einem Index sind, ist das noch einmal langsamer.

Indizes sind aber auch generell ein Thema, um Abfragen zu beschleunigen.

Hast du dir zu der Abfrage einmal den Ausführungsplan angeschaut? Der zeigt, wo wieviel Zeit verbraucht wird.

[himitsu]

Jupp, ein JOIN, anstatt des SubSELECTs.
Und dann auch schauen, ob auf den verlinkten Feldern ein Index liegt.

SQL-Code:

SELECT s.ArtNr, s.ArtName1,s.ArtGruppe, s.ArtZusInfo4
FROM sArtikel AS s
LEFT JOIN ArtLief AS l 
   ON l.ArtLiefArtNr = s.ArtNr
  AND l.ArtLiefLiefNr = s.ArtZusInfo4
WHERE s.ArtInaktiv = false
  AND s.ArtZusInfo4 LIKE 'K*'
  AND l.irgendwas IS NULL  -- hier irgendein NOT NULL Keyfield, vielleicht l.ArtLiefArtNr

INDIZE auf:
l) ArtLiefLiefNr, ArtLiefArtNr (hier dachte ich erst es wäre zwei mal das gleiche, aber einfach das = umgedreht und schon fällt es sofort auf)
s) ArtInaktiv, ArtZusInfo4, ArtNr

Und bezüglich des LIKE, k.A. wie es bei Access aussieht, aber z.B. in MySQL gibt es verschiedene Indize, wobei Manche für sowas wie LIKE nicht geeignet sind.
Zum Glück ist auch der * am Ende, denn die meisten Indize sind für Suchen, wo * am Anfang oder mittendrin wäre, ebenfalls ungeeignet.

Gut, das System könnte natürlich auch intelligenter sein und erkennen, dass das Subselect als JOIN besser geeignet ist und es das entsprechend selbstständig umstellt.
* anstatt für jede Zeile eine einzelne Abfrage auszufühen
* die Tabelle nur einmal lagen und jeweils nur schnell darin zu suchen

Wenn die Optimierung im DSMS nichts kann, sie also nicht erkennen würde, dass dein Subsselekt langsam ist und es scheller wäre die folgenden AND vorzuziehen,
dann würde das Subselect für JEDEN Datensatz ausgewertet, anstatt nur für einen Teil, nach der Filterung durch die beiden = , also

SQL-Code:

WHERE s.ArtInaktiv = false
  AND s.ArtZusInfo4 like 'K*'
  AMD NOT EXISTS (SELECT *
                    FROM ArtLief l
                    WHERE s.ArtNr = l.ArtLiefArtNr
                    and l.ArtLiefLiefNr = s.ArtZusInfo4
                 )

Drum kommt bei mir gedanklich auch das IS NULL "gedanklich" als letztes im WHERE vor ,
wobei ein NULL-Prüfung in einem INDEX wohl auch wiederum optimaler/schneller sein dürfte, als ein = oder LIKE.


PS: In vielen DBMS gibt SQL-Befehle oder Zusatztools, um das Statement analysieren zu lassen.
Was wird wo gemacht (z.B. FullTableScann anstatt IndexScan) und vielleicht auch vie viel Zeit welcher Teil braucht.

sowas hab ich jetzt noch nicht gesehn,
https://www.postgresql.org/docs/curr...l-analyze.html
https://docs.oracle.com/en/database/...F-DCB3772C1B0E
aber
https://support.microsoft.com/en-us/...b-30b86082d792

[HCB]

Vielen lieben Dank für Euere schnell Hilfe.
Himitsus erster SQL Code hat perfekt gepasst! Ausführungszeit jetzt 0.03 sek. Brutal geil!
Jetzt läufts super schnell mit diesem Code:

Delphi-Quellcode:

SELECT s.ArtNr, s.ArtName1,s.ArtGruppe, s.ArtZusInfo4
FROM sArtikel AS s
LEFT JOIN ArtLief AS l
   ON l.ArtLiefArtNr = s.ArtNr
  AND l.ArtLiefLiefNr = s.ArtZusInfo4
WHERE s.ArtInaktiv = false
  AND s.ArtZusInfo4 LIKE 'K*'
  AND l.ArtLiefArtNr IS NULL
order by  s.ArtNr;

[HCB]

Da ist mir doch noch einer auffällig langsam:

Delphi-Quellcode:

SELECT
A.ABFDocErfNr,A.ABFDocDatum,A.ABFDocKundenNr, A.ABFDocKundekurzbez,
P.ABFPosArtNr,P.ABFPosName1,P.ABFPosEPreis,
M.PersKtoPreisGruppe
FROM (ABFDok as A
INNER JOIN ABFPos as P ON A.ABFDocId = P.ABFPosDocID )
inner join sPersKto as M on M.PersKtoNummer = A.ABFDocKundenNr
where P.ABFPosArtNr = '11024060' and Year([A.ABFDocDatum]) > Year(Date())-5 and A.ABFDocType = 1
ORDER BY A.ABFDocId desc;

Diesesmal scheint mir (als Dilli) das etwas komplizierter wegen den 2 inner joints.
Könnte nochmals Hilfe gebrauchen um die Query flinker zu machen.

LG Harry

[Jasocul]

Zitat von HCB:

... inner joints.

Harry, YMMD

Was mir sofort auffällt:
Durch

Code:

Year([A.ABFDocDatum]) > Year(Date())-5

kann für "ABFDocDatum" schonmal kein Index mehr gezogen werden.
Dann teste bitte zusätzlich noch, ob es ohne

Code:

order by

spürbar schneller ist.

Leider kann ich heute nicht mehr weiterhelfen, da ich gleich auf einer Demo bin.

[fisipjm]

Ich würd jetzt einfach mal das probieren:

Code:

-- Index für ABFPos
CREATE INDEX idx_ABFPos_ABFPosDocID ON ABFPos (ABFPosDocID);

-- Index für sPersKto
CREATE INDEX idx_sPersKto_PersKtoNummer ON sPersKto (PersKtoNummer);

-- Index für ABFDok
CREATE INDEX idx_ABFDok_ABFPosArtNr_Datum_Type_KundenNr ON ABFDok (ABFPosArtNr, ABFDocDatum, ABFDocType, ABFDocKundenNr);

[HCB]

Hallo,
mit den indexen wurde es keider auch nicht schneller.

LG Harry

[Jumpy]

Year([A.ABFDocDatum]) > Year(Date())-5

Könnte man vllt. umformulieren zu

[A.ABFDocDatum] >= DateSerial(Year(Date())-4, 1, 1 )

[himitsu]

Jupp, da hier das Feld durch eine Funktion geht, kann nicht der Index genutzt werden, welcher auf diesem Feld liegt.

Also direkt das Feld direkt verwenden, damit der Index genutzt werden kann, wäre somit eine Lösung (siehe Jumpy)



Oder einen Index auf das Jahr erstellen, also auf Year([A.ABFDocDatum]) .

Weiß aber nicht, ob es sowas auch im Access gibt.