AGB  ·  Datenschutz  ·  Impressum  







Anmelden
Nützliche Links
Registrieren
Zurück Delphi-PRAXiS Programmierung allgemein Datenbanken [SQL] Wie Gruppensumme bilden?

[SQL] Wie Gruppensumme bilden?

Ein Thema von BlueStarHH · begonnen am 3. Mär 2021 · letzter Beitrag vom 10. Mär 2021
Antwort Antwort
Seite 2 von 3     12 3   
mkinzler
(Moderator)

Registriert seit: 9. Dez 2005
Ort: Heilbronn
39.851 Beiträge
 
Delphi 11 Alexandria
 
#11

AW: [SQL] Wie Gruppensumme bilden?

  Alt 4. Mär 2021, 08:08
Zitat:
Hast du überhaupt einen Index auf ArtikelNr?
Wird ihm so wenig bringen. Aber ein (weiterer) Index als expression Index könnte hilfreich sein.

SQL-Code:
CREATE INDEX IDX_ARTIKELGRUPPE ON VorgangPos
  COMPUTED BY ( substring(ArtikelNr from 1 for 5) );
Markus Kinzler
  Mit Zitat antworten Zitat
Jumpy

Registriert seit: 9. Dez 2010
Ort: Mönchengladbach
1.733 Beiträge
 
Delphi 6 Enterprise
 
#12

AW: [SQL] Wie Gruppensumme bilden?

  Alt 4. Mär 2021, 09:30
Vermutlich hab ich einen Denkfehler, aber macht der Join von Redeemer die Sache nicht eher schlimmer, so dass auch die Summen nicht mehr stimmen? Wenn eine Gruppe 2 Positionen hat, werden die über kreuz gejoined und ich bekomme 4 Datensätze und dann werden die gruppiert?

Das gejointe müsste mMn zuvor in einem Supselect gruppiert werden.

Kann Firebird "with"?

SQL-Code:
with Basis as (
  select
    substring(ArtikelNr from 1 for 5) as "Gruppe",
    ArtikelNr,
    max(Beschreibung) as Beschreibung, max(Farbe) as Farbe,
    sum(Menge) as Menge, sum(NettoSumme) as NettoSumme
  from VorgangPos vp
  group by ArtikelNr
  order by ArtikelNr
)

Select B.*,
  (Select sum(Menge) From Basis Where Gruppe=B.Gruppe) as Gruppenmenge
From Basis B
Ralph

Geändert von Jumpy ( 4. Mär 2021 um 09:38 Uhr)
  Mit Zitat antworten Zitat
Delphi.Narium

Registriert seit: 27. Nov 2017
2.400 Beiträge
 
Delphi 7 Professional
 
#13

AW: [SQL] Wie Gruppensumme bilden?

  Alt 4. Mär 2021, 10:32
Bevor wir nach anderen Lösungen für den Aufbau der Abfrage suchen, probiere es bitte mal mit

create index ix_VorgangPos_Gruppe on VorgangPos computed by (substring(ArtikelNr from 1 for 5))
Das habe ich vor der Ausführung meines SQL-Statements aus dem Eingangspost ausgeführt. Mein läuft SQL-Statement im IBExpert läuft nun seit mehr als 30 minuten... Woher weiss ich, dass der Index benutzt wird? Ist die DB so schlau, das automatisch zu nutzen? Die anderen Vorschläge teste ich morgen. Danke!
Ob ein Index benutzt wird oder nicht, erfährt man im Ausführungsplan.

Für FireBird nutze ich eigentlich immer FlameRobin.
Dort erhält man den Ausführungsplan über das Menü "Statement" und dort das Untermenü "Show execution plan".

Und: Über welche Datenmengen reden wir hier? Ein paar, ein paar hundert, ein paar tausend, mehrere Millionen?
  Mit Zitat antworten Zitat
BlueStarHH

Registriert seit: 28. Mär 2005
Ort: Hannover-Hainholz
797 Beiträge
 
Delphi 11 Alexandria
 
#14

AW: [SQL] Wie Gruppensumme bilden?

  Alt 4. Mär 2021, 12:38
Vermutlich hab ich einen Denkfehler, aber macht der Join von Redeemer die Sache nicht eher schlimmer, so dass auch die Summen nicht mehr stimmen? Wenn eine Gruppe 2 Positionen hat, werden die über kreuz gejoined und ich bekomme 4 Datensätze und dann werden die gruppiert?

Das gejointe müsste mMn zuvor in einem Supselect gruppiert werden.

Kann Firebird "with"?

SQL-Code:
with Basis as (
  select
    substring(ArtikelNr from 1 for 5) as "Gruppe",
    ArtikelNr,
    max(Beschreibung) as Beschreibung, max(Farbe) as Farbe,
    sum(Menge) as Menge, sum(NettoSumme) as NettoSumme
  from VorgangPos vp
  group by ArtikelNr
  order by ArtikelNr
)

Select B.*,
  (Select sum(Menge) From Basis Where Gruppe=B.Gruppe) as Gruppenmenge
From Basis B
Das läuft in 2,5 Minuten durch. Danke! In VorgangPos sind im Moment ca. 400.000 Datensätze.
  Mit Zitat antworten Zitat
Jumpy

Registriert seit: 9. Dez 2010
Ort: Mönchengladbach
1.733 Beiträge
 
Delphi 6 Enterprise
 
#15

AW: [SQL] Wie Gruppensumme bilden?

  Alt 5. Mär 2021, 08:15
Vermutlich hab ich einen Denkfehler, aber macht der Join von Redeemer die Sache nicht eher schlimmer, so dass auch die Summen nicht mehr stimmen? Wenn eine Gruppe 2 Positionen hat, werden die über kreuz gejoined und ich bekomme 4 Datensätze und dann werden die gruppiert?

Das gejointe müsste mMn zuvor in einem Supselect gruppiert werden.

Kann Firebird "with"?

SQL-Code:
with Basis as (
  select
    substring(ArtikelNr from 1 for 5) as "Gruppe",
    ArtikelNr,
    max(Beschreibung) as Beschreibung, max(Farbe) as Farbe,
    sum(Menge) as Menge, sum(NettoSumme) as NettoSumme
  from VorgangPos vp
  group by ArtikelNr
  order by ArtikelNr
)

Select B.*,
  (Select sum(Menge) From Basis Where Gruppe=B.Gruppe) as Gruppenmenge
From Basis B
Das läuft in 2,5 Minuten durch. Danke! In VorgangPos sind im Moment ca. 400.000 Datensätze.
Dann versuchen wir noch eine Optimierung:

SQL-Code:
with Basis as (
  select
    substring(ArtikelNr from 1 for 5) as "Gruppe",
    ArtikelNr,
    max(Beschreibung) as Beschreibung, max(Farbe) as Farbe,
    sum(Menge) as Menge, sum(NettoSumme) as NettoSumme
  from VorgangPos vp
  group by ArtikelNr
  order by ArtikelNr
)

Select B.*, G.Menge
From Basis B
Left Join
  (Select Gruppe, sum(Menge) From Basis Group By Gruppe) G
On G.Gruppe=B.Gruppe
Ralph
  Mit Zitat antworten Zitat
Ghostwalker

Registriert seit: 16. Jun 2003
Ort: Schönwald
1.299 Beiträge
 
Delphi 10.3 Rio
 
#16

AW: [SQL] Wie Gruppensumme bilden?

  Alt 5. Mär 2021, 13:19
Hi,
ohne jetzt Firebird im Detail zu kennen, würd ich das so schreiben

Code:
  select
    VP2.Gruppe As Gruppe,
    ArtikelNr,
    max(Beschreibung) as Beschreibung,
    max(Farbe) as Farbe,
    sum(Menge) as Menge,
    VP2.Gruppenmenge as Gruppenmenge
    sum(NettoSumme) as NettoSumme
  from VorgangPos vp
       (Select
          Substring(ArtikelNr from 1 for 5) as Gruppe,
          Sum(Menge) as Gruppenmenge
       From VorgangPos) AS VP2
  group by ArtikelNr
  order by ArtikelNr
Uwe
e=mc² or energy = milk * coffee²
  Mit Zitat antworten Zitat
Redeemer

Registriert seit: 19. Jan 2009
Ort: Kirchlinteln (LK Verden)
1.010 Beiträge
 
Delphi 2009 Professional
 
#17

AW: [SQL] Wie Gruppensumme bilden?

  Alt 5. Mär 2021, 15:32
Zitat:
Hast du überhaupt einen Index auf ArtikelNr?
Wird ihm so wenig bringen.
Hängt vom Datenbanksystem ab. MSSQL ist da teilweise überraschend intelligent und zieht beispielsweise einen solchen Index zur Lösung heran, wenn man ein LIKE-Match macht, das nicht mit % beginnt.

Aber ein (weiterer) Index als expression Index könnte hilfreich sein.
SQL-Code:
CREATE INDEX IDX_ARTIKELGRUPPE ON VorgangPos
  COMPUTED BY ( substring(ArtikelNr from 1 for 5) );
Das war schon vorgeschlagen worden.
Janni
2005 PE, 2009 PA, XE2 PA
  Mit Zitat antworten Zitat
Benutzerbild von IBExpert
IBExpert

Registriert seit: 15. Mär 2005
642 Beiträge
 
FreePascal / Lazarus
 
#18

AW: [SQL] Wie Gruppensumme bilden?

  Alt 6. Mär 2021, 09:35
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
Holger Klemt
www.ibexpert.com - IBExpert GmbH
Oldenburger Str 233 - 26203 Wardenburg - Germany
IBExpert and Firebird Power Workshops jederzeit auch als Firmenschulung
  Mit Zitat antworten Zitat
BlueStarHH

Registriert seit: 28. Mär 2005
Ort: Hannover-Hainholz
797 Beiträge
 
Delphi 11 Alexandria
 
#19

AW: [SQL] Wie Gruppensumme bilden?

  Alt 10. Mär 2021, 08:14
@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...
  Mit Zitat antworten Zitat
mkinzler
(Moderator)

Registriert seit: 9. Dez 2005
Ort: Heilbronn
39.851 Beiträge
 
Delphi 11 Alexandria
 
#20

AW: [SQL] Wie Gruppensumme bilden?

  Alt 10. Mär 2021, 08:21
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
Markus Kinzler
  Mit Zitat antworten Zitat
Themen-Optionen Thema durchsuchen
Thema durchsuchen:

Erweiterte Suche
Ansicht

Forumregeln

Es ist dir nicht erlaubt, neue Themen zu verfassen.
Es ist dir nicht erlaubt, auf Beiträge zu antworten.
Es ist dir nicht erlaubt, Anhänge hochzuladen.
Es ist dir nicht erlaubt, deine Beiträge zu bearbeiten.

BB-Code ist an.
Smileys sind an.
[IMG] Code ist an.
HTML-Code ist aus.
Trackbacks are an
Pingbacks are an
Refbacks are aus

Gehe zu:

Impressum · AGB · Datenschutz · Nach oben
Alle Zeitangaben in WEZ +1. Es ist jetzt 06:58 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