Access SQL performance

[HCB]

Hallo, vielleicht kann und will mir jemand helfen:
Folgendes SQL script habe ich laufen (Delphi mit FireDac Access) und funktioniert einwandfrei.

Select Top 30 * from
(SELECT ABFStatKundenNr, sum(ABFStatUmsatz) as total from ABFStat WHERE ABFStatDatum > #01/01/2023#
and ABFStatKundenNr IN
(SELECT PersKtoNummer FROM sPersKto WHERE PersKtoArt = 68 )
group by ABFStatKundenNr)
order by total desc;

Hier werden die Top30 Kunden mit Kundennummer und Umsatz gelistet.
Problem: Es dauert 10 sek. bis das Ergebnis ausgewertet ist.
Frage: Könnte man das sql umschreiben damit es schneller ist? Ich habe schon einiges probiert, komme aber nicht weiter.

Für Hilfe und Unterstützung würde ich mich sehr freuen.

LG Harry

[DasWolf]

Verwende mal EXISTS wie folgt:

Select Top 30 * from
(SELECT ABFStatKundenNr, sum(ABFStatUmsatz) as total from ABFStat WHERE ABFStatDatum > #01/01/2023#
and EXISTS (SELECT NULL FROM sPersKto WHERE sPersKto.PersKtoNummer = ABFStat.ABFStatKundenNr AND PersKtoArt = 68 )
group by ABFStatKundenNr)
order by total desc;

[peterbelow]

Zitat:

Zitat von HCB (Beitrag 1520517)

Hallo, vielleicht kann und will mir jemand helfen:
Folgendes SQL script habe ich laufen (Delphi mit FireDac Access) und funktioniert einwandfrei.

Select Top 30 * from
(SELECT ABFStatKundenNr, sum(ABFStatUmsatz) as total from ABFStat WHERE ABFStatDatum > #01/01/2023#
and ABFStatKundenNr IN
(SELECT PersKtoNummer FROM sPersKto WHERE PersKtoArt = 68 )
group by ABFStatKundenNr)
order by total desc;

Hier werden die Top30 Kunden mit Kundennummer und Umsatz gelistet.
Problem: Es dauert 10 sek. bis das Ergebnis ausgewertet ist.
Frage: Könnte man das sql umschreiben damit es schneller ist? Ich habe schon einiges probiert, komme aber nicht weiter.

Naja, die Query engine muss halt den kompletten Ergebnissatz zusammensammeln und sortieren, bevor es die ersten 30 identifizieren und zurückliefern kann. Da kann man nur versuchen, die Subqueries zu beschleunigen. Hat ABFStat einen Index auf ABFStatDatum, hat sPersKto einen auf PersKtoArt? Hast Du versucht die IN-Query durch einen JOIN zu ersetzen?

[HCB]

Mit EXIST nimmt es sich nichts, gleiche 10 sek. Wartezeit.
ABFStat und sPersKto haben keinen Index, mit Inner join bekomme ich Fehlermeldung (bzw. hab ich wahrscheinlich was falsch gemacht).
Auch eine Reduzierung auf 20 bringt keinen Performancevorteil, was ja klar ist, es muss die gesamte Datenbank durchsucht werden.

LH Harry

[Uwe Raabe]

Ohne das jetzt gerade mit Access probieren zu können:

SQL-Code:

Select Top 30 
a.ABFStatKundenNr, 
sum(a.ABFStatUmsatz) as total 
from ABFStat a
JOIN sPersKto b ON 
  (b.PersKtoNummer = a.ABFStatKundenNr) and
  (b.PersKtoArt = 68)
WHERE a.ABFStatDatum > #01/01/2023#
group by a.ABFStatKundenNr
order by total desc

[HCB]

Hallo Uwe,

Danke, dein sql code mit join bringt ebenfalls die Fehlermeldung 'Syntaxfehler in FROM-Klausel'.
Normal sieht das in Ordnung aus, ich kann nicht erkennen woran es liegen könnte.

LG Harry

[Uwe Raabe]

Ich arbeite eigentlich nicht mit Access, aber vielleicht geht dies ja besser:

SQL-Code:

Select Top 30
a.ABFStatKundenNr,
sum(a.ABFStatUmsatz) as total
from ABFStat as a
JOIN sPersKto as b ON
  (b.PersKtoNummer = a.ABFStatKundenNr) and
  (b.PersKtoArt = 68)
WHERE a.ABFStatDatum > #01/01/2023#
group by a.ABFStatKundenNr
order by sum(a.ABFStatUmsatz) desc

[HCB]

Danke Uwe,

verflixt es kommt der gleiche Fehler 'Syntaxfehler in FROM-Klausel'.
Kann in der FROM Klausel haber nicht erkennen was daran falsch sein sollte. Vielleicht ist auch die Fehlermeldung nur pauschal und hat gar nichts mit der FROM Klausel zu tun?

LG Harry

[Uwe Raabe]

Kannst du die DB zur Verfügung stellen? Geht auch mit abgespeckten Testdaten.

[HCB]

Ich habe leider keine neutrale DB zur Verfügung. In der Originalen sind ca. 70.000 Einträge in der ABFStat und ca. 18.000 in der sPersKto.
Da sich aber in der sPersKto die Kunden befinden, müsste ich alle datengeschützten Daten erst aus der Tabelle löschen und in der ABFStat ebenfalls. Das ist etwas aufwendig.
Ich hoffe, du hast Verständnis dafür. Aber vielen Dank, das du dich dafür einbringen willst.

LG Harry

[Delphi.Narium]

Drei Versuche:

SQL-Code:

select
  top 30
  ABFStatKundenNr,
  total
from
(
  select
    ABFStat.ABFStatKundenNr,
    sum(ABFStat.ABFStatUmsatz) as total
  from ABFStat
  where ABFStat.ABFStatDatum > #01/01/2023#
  and exists
  (
    select 1 
    from sPersKto
    where sPersKto.PersKtoArt = 68
    and sPersKto.PersKtoNummer = ABFStat.ABFStatKundenNr
  )
  group by ABFStat.ABFStatKundenNr
)
order by total desc

SQL-Code:

select
  top 30
  ABFStatKundenNr,
  total
from
(
  select
    a.ABFStatKundenNr,
    sum(a.ABFStatUmsatz) as total
  from
    ABFStat a,
    sPersKto b
  where b.PersKtoNummer = a.ABFStatKundenNr
  and b.PersKtoArt = 68
  and a.ABFStatDatum > #01/01/2023#
  group by a.ABFStatKundenNr
)
order by total desc;

SQL-Code:

select
  top 30
  ABFStatKundenNr,
  total
from
(
  select
    a.ABFStatKundenNr,
    sum(a.ABFStatUmsatz) as total
  from
    (select ABFStatKundenNr, ABFStatUmsatz from ABFStat where ABFStatDatum > #01/01/2023#) a, -- Datenmengen möglichst früh eingrenzen
    (select PersKtoNummer from sPersKto where PersKtoArt = 68) b
  where b.PersKtoNummer = a.ABFStatKundenNr -- und erst dann die Teilmengen miteinander verbinden
  group by a.ABFStatKundenNr
)
order by total desc

Und zur Vorbeugung:

Zitat:

Zitat von himitsu (aus einem anderen Thread zum gleichen Themenkomplex)

IHHHHHH, nicht so.

Access kann auch JOINs in Richtig.

Weiß ich, so geht aber auch und ist für ich leichter verständlich ;-)

Fehler im From?

SQL-Code:

select
  Sum(Spaltenname) as AliasName -- as vor den Aliasnamen
from TabellenName Aliasname -- kein as vor den Aliasnamen
group by Spaltenname

Manche Datenbanken kommen in beiden Fällen mit dem AS zurecht.
Manche Datenbanken kommen in beiden Fällen ohne das AS zurecht.
Manche Datenbanken kommen nur im ersten Fall mit dem AS zurecht, aber nicht im zweiten Fall.

Das könnte bei Access die Ursache für den 'Syntaxfehler in FROM-Klausel' sein.

[HCB]

Hallo Dekphi.Narium,

vielen Dank für die 3 Vorschläge. Vorab: Du bist das absolute Genie!!!!!
Folgende Ausführungsgeschwindig habe ich festgestellt:

1. Mein Sql: Exec Time 9,06 sek
2. mit Exist von 'DasWolf': Exec Time 9,09 sek
3. Code 1 von Delphi.Narium: ExecTime 9,00 sek.
4. Code 2 von Delphi.Narium: ExecTime 0,13 sek.
5. Code 3 von Delphi.Narium: ExecTime 0,13 sek.

Das ist der Wahnsinn, man glaubt man hat einen funktionieren SQL Code der das gewünschte Ergebnis bringt und alles ok ist.
Lässt man aber einen absoluten Profi ran, dann merkt man, dass man eigentlich nichts kann :oops:
Vielen Dank an alle, die mir helfen wollten und speziell an den Top-Profi Delphi.Narium für deine Hilfe.

LG Harry

[HCB]

Hallo Delphi.Narium,
ich habe doch noch eine Frage: Wie bekomme ich noch zusätzliche Felder aus der sPersKto unter?
So wie ich es jetzt habe bringt es keinen Fehler und läuft, aber die Felder b.PersKtoName1,b.PersKtoZusInfo1,b.PersKtoPreisGru ppe werden nicht angezeigt.

select
top 30
ABFStatKundenNr,
total
from
(
select
a.ABFStatKundenNr,
sum(a.ABFStatUmsatz) as total,
b.PersKtoName1,b.PersKtoZusInfo1,b.PersKtoPreisGru ppe//Änderung Felder sollen mit angezeigt werden
from
ABFStat a,
sPersKto b
where b.PersKtoNummer = a.ABFStatKundenNr
and b.PersKtoArt = 68
and a.ABFStatDatum > #01/01/2022# and a.ABFStatDatum < #31/12/2022#
group by a.ABFStatKundenNr, b.PersKtoName1, b.PersKtoZusInfo1,b.PersKtoPreisGruppe
)
order by total desc;

Kannst dur mir bitte nochmals helfen?

LG Harry

[HCB]

Ok erledigt! Habs so gemacht:
select
top 30
ABFStatKundenNr,
total,
PersKtoName1, PersKtoZusInfo1, PersKtoPreisGruppe
from
(
select
a.ABFStatKundenNr,
sum(a.ABFStatUmsatz) as total,
b.PersKtoName1,b.PersKtoZusInfo1,b.PersKtoPreisGru ppe
from
ABFStat a,
sPersKto b
where b.PersKtoNummer = a.ABFStatKundenNr
and b.PersKtoArt = 68
and a.ABFStatDatum > #01/01/2022# and a.ABFStatDatum < #31/12/2022#
group by a.ABFStatKundenNr, b.PersKtoName1, b.PersKtoZusInfo1,b.PersKtoPreisGruppe
)
order by total desc;

[Delphi.Narium]

SQL-Code:

select
  top 30
  ABFStatKundenNr,
  PersKtoName1,PersKtoZusInfo1,PersKtoPreisGruppe, -- müssen auch mit ins äußere Select
  total
from
(
  select
    a.ABFStatKundenNr,
    sum(a.ABFStatUmsatz) as total,
    b.PersKtoName1,b.PersKtoZusInfo1,b.PersKtoPreisGruppe -- Änderung Felder sollen mit angezeigt werden
  from
    ABFStat a,
    sPersKto b
  where b.PersKtoNummer = a.ABFStatKundenNr
  and b.PersKtoArt = 68
  -- and a.ABFStatDatum > #01/01/2022# and a.ABFStatDatum < #31/12/2022#
  and a.ABFStatDatum between #01/01/2022# and #31/12/2022#
  group by a.ABFStatKundenNr
)
order by total desc;

[dummzeuch]

Zitat:

Zitat von Uwe Raabe (Beitrag 1520536)

Ich arbeite eigentlich nicht mit Access, aber vielleicht geht dies ja besser:

SQL-Code:

Select Top 30
a.ABFStatKundenNr,
sum(a.ABFStatUmsatz) as total
from ABFStat as a
JOIN sPersKto as b ON
  (b.PersKtoNummer = a.ABFStatKundenNr) and
  (b.PersKtoArt = 68)
WHERE a.ABFStatDatum > #01/01/2023#
group by a.ABFStatKundenNr
order by sum(a.ABFStatUmsatz) desc

Ich meine mich dunkel zu erinnern, dass man das bei MS Access ohne "as" formuliert:

"from ABFStat a"

Ich kann es aber auch gerade nicht ausprobieren, ich habe privat kein MS Office und es ist auch schon lange her, dass ich was damit gemacht hhabe.

[Uwe Raabe]

Zitat:

Zitat von dummzeuch (Beitrag 1520570)

Ich meine mich dunkel zu erinnern, dass man das bei MS Access ohne "as" formuliert:

Das war ja mein erster Ansatz: https://www.delphipraxis.net/1520524-post5.html