[SQL] Wie Gruppensumme bilden?

[mkinzler]

In Form einer SP/einem Codeblock:

SQL-Code:

...for select
  substring(vp.ArtikelNr from 1 for 5),
  vp.ArtikelNr,
  max(vp.Beschreibung) as Beschreibung,
  max(vp.Farbe) as Farbe,
  sum(vp.Menge) as Menge,
  sum(vp.NettoSumme) as NettoSumme
from 
  VorgangPos vp 
  into :gruppe, ArtikelNr, :... do
begin
  for select sum(Menge) from VorgangPos where substring(ArtikelNr from 1 for 5)  = Gruppe into :Gruppenmenge do suspend;
end

[mjustin]

"Richtig" wäre nach meinem Bauchgefühl mit Windowing Funktionen zu arbeiten:

https://www.firebirdsql.org/file/com...3windowing.pdf

Dabei setzt man eine partition auf die gewünschten Gruppen und kann dann je Gruppe mit Aggregatfunktionen arbeiten.

Vielleicht kann hier im Forum das für Firebird (3) kurz in SQL giessen.


p.s. ich habe Windowing mit MS SQL / Oracle / DB2 bereits eingesetzt und eine Gruppenmenge sollte damit problemlos und vor allem sehr performant realisierbar sein. Vielleicht kann ich es in den nächsten Tagen an einer Beispiel-DB zeigen.

[jobo]

Wenn durch eine solche Abfrage ein paar GB gedumped werden und alles so ewig dauert, dann mal so ins Blaue:
Vielleicht ein Join Kriterium vergessen?
Wenn die Daten trotz Gruppierung mehr werden kann irgendwas nicht stimmen.

Die Variante von Reedemer (äußerer Join) würde ich jedenfalls vorziehen. Wenn fb Partition Windows kann, dann wohl das. Die Partition / Gruppierung & Join anhand eines Teilstrings zu machen, ist natürlich generell nicht die Grundlage für irrsinnige Geschwindigkeit.

[IBExpert]

Zitat von mkinzler:

In Form einer SP/einem Codeblock:

SQL-Code:

...for select
  substring(vp.ArtikelNr from 1 for 5),
  vp.ArtikelNr,
  max(vp.Beschreibung) as Beschreibung,
  max(vp.Farbe) as Farbe,
  sum(vp.Menge) as Menge,
  sum(vp.NettoSumme) as NettoSumme
from 
  VorgangPos vp 
  into :gruppe, ArtikelNr, :... do
begin
  for select sum(Menge) from VorgangPos where substring(ArtikelNr from 1 for 5)  = Gruppe into :Gruppenmenge do suspend;
end

Der Vorschlag von mkinzler ist der einzige, der dich weiter bringen wird, weil nur der genau die Anzahl der Records im Resultset benutzt, um damit den inneren sum befehl für jedes ergebnis genau ein mal ausführt.

Wenn dein äußeres Statement schnell ist, bekommst du auf den Weg die garantie, das dein inneres Statement genau n mal aufgerufen wird. mit dem debugger in ibexpert kannst du dann sogar noch die genauen laufzeiten der einzelaufrufe ermitteln und optimieren, weil evtl genau ein artikel 99% der zeit verbraucht, das wird dir aber mit deinem wild verschachtelten sql niemals klar sein.

Es geht dann weiter mit einem Expression index auf substring(ArtikelNr from 1 for 5) und einem doppeltpunkt vor der Gruppe ( substring(ArtikelNr from 1 for 5) = :Gruppe) damit die variable auch die ist die von oben kommt (das hätte mkinzler aber auch da gesetzt, wenn es darum geht, nicht mal eben nur eine hilfreiches Beispiel hier reinzutippen, sondern das in der relaen Datenbank auszuführen).

wenn du keine sp willst, dann mach das als execute block

Firebird kann zwar with und andere konstruktionen wie subselects etc, aber vermutlich wird dir dabei in ibexpert services-database monitoring das statements mit milliarden indexed oder non indexed reads um die ohren fliegen, sonst wäre das nicht so lahm

und bitte nicht immer "andere server können das alles ganz toll mit ..."
das bringt niemanden wirklich weiter, der das mit firebird lösen soll.

ziel sollte es bei jeder Programmierung sein, mit einem jederzeit nachvollziehbaren Statement ein möglichst performantes statements zu erreichen. Und der weg, die ebenen in einem block oder einer sp mit leicht verständlichen unteraufrufen zu implementieren sorgt für gutes Verständnis des Codes, auch wenn du dir den Kram selber in 5 Jahren noch mal anschauen musst.

Ich kann dir gerne das SQL von einem Kunden zu analyse übergeben, besteht aus ca 400 Zeilen und ergibt folgende Performance Analysis
(ist auch ziemlich verschachtelt, läuft aber trotzdem in ca 0,1 sekunden durch, aber leider nur ohne fetchall, dann brauch das immer noch
vertretbare 4 minuten für ein resultset von ca 1mio records und auf dem weg dahin ca 25mio indexed reads und 1 mio non indexed reads.
Selbst ich kann dafür nicht abschätzen ob ich eine stunde, ein Tag oder eine Woche brauche, um den Speed deutlich zu verbessern
weil der SQL Basiscode nicht von mir stammt, und weil der Kunde den sql nur ein mal am tag braucht, muss das auch niemand beschleunigen,
aber bei anderen Statements kann das ganz anders aussehen.

Code:

Plan
PLAN (S POL INDEX (FK_PAYMENT_ORDER_LINE_1))
PLAN JOIN (S CH INDEX (IX_CALC_HEADER_DOC_ID), S VE INDEX (PVE_CALC_HEADER))
PLAN (A2 INDEX (ACTIONS_ACTION_ID))
PLAN (A INDEX (PK_ACTIONS))
PLAN (RH INDEX (RDB$PRIMARY12))
PLAN (A INDEX (PK_ACTIONS))
PLAN (RH INDEX (RDB$PRIMARY12))
PLAN JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (S A NATURAL, S AD INDEX (PK_ADRESS)), S CO INDEX (RDB$PRIMARY5)), S VR INDEX (RDB$PRIMARY35)), S F INDEX (PK_FILES)), S FD INDEX (PK_FILE_DETAILS)), S VR2 INDEX (RDB$PRIMARY35)), S U INDEX (RDB$PRIMARY17)), S PP INDEX (RDB$PRIMARY56)), S Q INDEX (POOL_QUOTATION_QUOTATION_NO)), S Q2 INDEX (RDB$PRIMARY59)), S C INDEX (RDB$PRIMARY4)), S AST INDEX (APPLICATION_SETUP_LOCATION)), S CN INDEX (RDB$PRIMARY23)), S POH INDEX (PAYMENT_ORDER_NO)), S A2 INDEX (ACTIONS_ACTION_ID)), S PP2 INDEX (RDB$PRIMARY56)), S Q3 INDEX (POOL_QUOTATION_QUOTATION_NO)), S F2 INDEX (PK_FILES)), S FD2 INDEX (PK_FILE_DETAILS)), S VR3 INDEX (RDB$PRIMARY35)), S COM INDEX (RDB$PRIMARY4)), S APS INDEX (APPLICATION_SETUP_LOCATION))

Adapted Plan
PLAN (S POL INDEX (FK_PAYMENT_ORDER_LINE_1))
PLAN JOIN (S CH INDEX (IX_CALC_HEADER_DOC_ID), S VE INDEX (PVE_CALC_HEADER))
PLAN (A2 INDEX (ACTIONS_ACTION_ID))
PLAN (A INDEX (PK_ACTIONS))
PLAN (RH INDEX (INTEG_44))
PLAN (A INDEX (PK_ACTIONS))
PLAN (RH INDEX (INTEG_44))
PLAN JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (S A NATURAL, S AD INDEX (PK_ADRESS)), S CO INDEX (PK_CONTACTS)), S VR INDEX (INTEG_184)), S F INDEX (PK_FILES)), S FD INDEX (PK_FILE_DETAILS)), S VR2 INDEX (INTEG_184)), S U INDEX (PK_USERS)), S PP INDEX (PK_CONTACTS6)), S Q INDEX (POOL_QUOTATION_QUOTATION_NO)), S Q2 INDEX (PK_CONTACTS9)), S C INDEX (PK_COMPANY)), S AST INDEX (APPLICATION_SETUP_LOCATION)), S CN INDEX (INTEG_164)), S POH INDEX (PAYMENT_ORDER_NO)), S A2 INDEX (ACTIONS_ACTION_ID)), S PP2 INDEX (PK_CONTACTS6)), S Q3 INDEX (POOL_QUOTATION_QUOTATION_NO)), S F2 INDEX (PK_FILES)), S FD2 INDEX (PK_FILE_DETAILS)), S VR3 INDEX (INTEG_184)), S COM INDEX (PK_COMPANY)), S APS INDEX (APPLICATION_SETUP_LOCATION))

------ Performance info ------  ohne fetchall
Prepare time = 31ms
Execute time = 63ms
Avg fetch time = 2,17 ms
Current memory = 89.204.432
Max memory = 89.262.048
Memory buffers = 20.000
Reads from disk to cache = 155
Writes from cache to disk = 0
Fetches from cache = 5.400

------ Performance info ------ mit fetchall
Prepare time = 16ms
Execute time = 4m 19s 750ms
Avg fetch time = 0,28 ms
Current memory = 89.283.648
Max memory = 90.161.856
Memory buffers = 20.000
Reads from disk to cache = 221.092
Writes from cache to disk = 1
Fetches from cache = 87.363.844

[BlueStarHH]

@Ibexpert: Danke für diese ausführliche Antwort. Dann probiere ich nochmal den Code von mkinzler:

SQL-Code:

execute block
as

begin
  for select
    substring(vp.ArtikelNr from 1 for 5) as Gruppe,
    vp.ArtikelNr as ArtikelNr,
    max(vp.Beschreibung) as Beschreibung,
    max(vp.Farbe) as Farbe,
    sum(vp.Menge) as Menge,
    sum(vp.NettoSumme) as NettoSumme
  from
    VorgangPos vp
    into :gruppe, :ArtikelNr, :Beschreibung, :Farbe, :Menge, :NettoSumme do
  begin
    for select sum(Menge) from VorgangPos where substring(ArtikelNr from 1 for 5) = Gruppe into :Gruppenmenge do suspend;
  end
end

Da erscheint diese Fehlermeldung:
Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause).

Ich bin verwirrt...

[mkinzler]

Es fehlt die Group by

SQL-Code:

execute block
as

begin
  for select
    substring(vp.ArtikelNr from 1 for 5) as Gruppe,
    vp.ArtikelNr as ArtikelNr,
    max(vp.Beschreibung) as Beschreibung,
    max(vp.Farbe) as Farbe,
    sum(vp.Menge) as Menge,
    sum(vp.NettoSumme) as NettoSumme
  from
    VorgangPos vp
  group by 
    vp.ArtikelNr
    into :gruppe, :ArtikelNr, :Beschreibung, :Farbe, :Menge, :NettoSumme do
  begin
    for select sum(Menge) from VorgangPos where substring(ArtikelNr from 1 for 5) = Gruppe into :Gruppenmenge do suspend;
  end
end

[BlueStarHH]

Zitat von mkinzler:

Es fehlt die Group by

SQL-Code:

execute block
as

begin
  for select
    substring(vp.ArtikelNr from 1 for 5) as Gruppe,
    vp.ArtikelNr as ArtikelNr,
    max(vp.Beschreibung) as Beschreibung,
    max(vp.Farbe) as Farbe,
    sum(vp.Menge) as Menge,
    sum(vp.NettoSumme) as NettoSumme
  from
    VorgangPos vp
  group by 
    vp.ArtikelNr
    into :gruppe, :ArtikelNr, :Beschreibung, :Farbe, :Menge, :NettoSumme do
  begin
    for select sum(Menge) from VorgangPos where substring(ArtikelNr from 1 for 5) = Gruppe into :Gruppenmenge do suspend;
  end
end

Super, das läuft im IBExpert in etwas mehr als einer Sekunde durch! Das Ergebnis ist ja nun in einzelnen Variablen im Execute block. Nur wie kann ich das Ergebnis nun als Datenmenge in Delphi nutzen? Sorry für die dumme Frage, aber ich hab noch nie mit dem execute block gearbeitet. Wenn ich das ganze in eine SP packe, habe ich ja das selbe Problem oder nicht? Die SP kann ja keine Datenmenge aus mehreren Records zurückgeben oder doch?

[mkinzler]

Im Header der SP / execution block die Spalten für die Rückgabe deklarieren, diese werden dann durch das suspend zurückgegeben.

SQL-Code:

execute block
  returns(
   gruppe char(5), ... 
  )
as
...

[IBExpert]

und ergänzend zu mkinzler, wenn du da alle variablen als return parameter drin hast, kannst du bei naherzu jeder Query Komponente, die du auch für einen select benutzen kannst, die property sql mit dem execute block .... text füllen und mit open dann durch die datenmenge laufen.

und wenn es mal eine summe, aber auch mal keine summe geben könnte , dann geht noch folgende änderung

Code:

    
select sum(Menge) from VorgangPos where substring(ArtikelNr from 1 for 5) = Gruppe into :Gruppenmenge;
suspend;

damit würdest du auch einen record zur äußeren Hauptdatenmenge bekommen, wenn es gar keine details in VorgangPos gibt
(ist in diesem fall wegen dem Sum() nicht so wichtig, weil der immer genau einen record liefert, wen das die einzige spalte ist, aber wichtig ist das der "for select ... into" auf einer datenmenge die immer nur einen record liefert auch wenig sinnvoll.

und als logik: immer wenn dein quelltext in einem execute block oder auch in eine sp beim schlüsselwort suspend landet, wird das was in den return parametern steht als record erzeugt und kann damit in delphi bei der query mit while not eof und next ausgewertet werden

eine execute block muss auch gar noch zwingend auf einer datenmenge entstehen auch so was geht nämlich

Code:

execute block
returns (anz integer)
as
begin
 anz=1;
 suspend;
 anz=2;
 suspend;
end

auf dem weg kann man sehr komplexe ergebnisse zeilenweise und spaltenweise zusammenstellen, bei denen man mit einem sql statement abstruse verschachtelungen oder unions brauchen würde