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

Delphi-Quellcode:

=

, 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 :wink:,
wobei ein NULL-Prüfung in einem INDEX wohl auch wiederum optimaler/schneller sein dürfte, als ein

Delphi-Quellcode:

=

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.:bouncing4:
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:

Zitat von HCB (Beitrag 1532832)

... inner joints.

Harry, YMMD :lol:

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.:wink:

[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

Delphi-Quellcode:

Year([A.ABFDocDatum])

.

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

[hoika]

Hallo,
das Year-Dingens könnte man so umschreiben

DateAdd("yyyy", -5, Date())

Aber warum übergibst Du das nicht als konstanten Parameter?
Warum wird das jedesmal berechnet?

Und noch mal auf die Indizes zu kommen.
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


Die fett markierten Felder müssten Indizes enthalten.

Ich würde danach auch mal die MDB komprimieren.

[himitsu]

Das

Delphi-Quellcode:

DateAdd("yyyy", -5, Date())

wird nur einmal berechnet. (außer die haben bei der Funktion und dem Queryplaner echt Scheiße gebaut)

Aber das Date vom Feld, wird natürlich für jeden Datensatz berechnet.
Ja, die Berechnung/Funktionsausführung dauert nicht lange und braucht nahezu keine Zeit,
aber da es keinen Index auf dessen Ergebnis gibt, kann eben auch Keiner genutzt werden.

[hoika]

Hallo,
kein Index?

CREATE INDEX idx_ABFDok_ABFPosArtNr_Datum_Type_KundenNr ON ABFDok (ABFPosArtNr, ABFDocDatum, ABFDocType, ABFDocKundenNr);


Eventuell sollte noch ein Index direkt auf ABFDocDatum gesetzt werden, also nicht so ein "komischer" Compound-Index.

[himitsu]

idx_ABFDok_ABFPosArtNr_Datum_Type_KundenNr ist ein Combi-Index, der genutzt wird, wenn im WHERE direkt auf alle Felder (ABFPosArtNr, ABFDocDatum, ABFDocType, ABFDocKundenNr) gerpft wird.

Hast du einen Index nur auf "ABFDocDatum", also

Delphi-Quellcode:

CREATE INDEX idx_ABFDocDatum ON ABFDok (ABFDocDatum);

(oder z.B.

Delphi-Quellcode:

ABFDocDatum UNIQUE

im CREATE TABLE), dann
* wird bei

Delphi-Quellcode:

WHERE ABFDocDatum ...

dieser Index benutzt
* aber nicht bei

Delphi-Quellcode:

WHERE Year(ABFDocDatum) ...

Für Letzteres bräuchte man z.B. sowas

Delphi-Quellcode:

CREATE INDEX idx_YearABFDocDatum ON ABFDok (Year(ABFDocDatum));

(falls Access sowas kann)



Und wie gesagt, kommt es teilweise auch auch auf die Art des Indize drauf an.
MySQL oder z.B. Postgres kennen unterschiedliche Typen (z.B. B-tree, hash, GiST, SP-GiST, GIN, and BRIN)

B-Tree ist heute oft gebräuchlich und auch für ein

Delphi-Quellcode:

LIKE 'irgendwas*'

geeignet, während HASH für ein LIKE absolut unnütz ist, aber für ein

Delphi-Quellcode:

=

mit langen Strings super wäre.
Und für

Delphi-Quellcode:

LIKE '*sonstwas'

wäre es besser, wenn der B-Tree rückwärts von hinten aufgebaut ist.