[SQL] Wie Gruppensumme bilden?

[BlueStarHH]

Zitat von Delphi.Narium:

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!

[Redeemer]

Wenn es so lange läuft, wird der Index nicht genutzt. Der dient je gerade dazu, die Komplexität logarithmisch zu begrenzen.

Ein paar Ideen, je nachdem, was dein DBMS kann:

• Hast du überhaupt einen Index auf ArtikelNr?
• Führe im JOIN einen Linksvergleich mit LIKE aus (setzt Index auf ArtikelNr voraus):
  Code:

  LEFT OUTER JOIN VorgangPos vp2 ON vp2.ArtikelNr LIKE substring(vp.ArtikelNr from 1 for 5) || '%'

  (Ich hab keine Ahnung, was in deinem DBMS der Konkatenationsoperator ist. Da hat jedes seinen eigenen, Doppelpipe ist einfach der SQL-Standard, an den sich fast niemand hält.)
• LEFT statt SUBSTRING benutzen.
• Index auf eine berechnete Spalte mit dem Vergleichswert setzen.

[mkinzler]

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) );

[Jumpy]

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

[BlueStarHH]

Zitat von Jumpy:

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.

[Jumpy]

Zitat von BlueStarHH:

Zitat von Jumpy:

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

[Ghostwalker]

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

[Redeemer]

Zitat von mkinzler:

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.

Zitat von mkinzler:

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.

[Delphi.Narium]

Zitat von BlueStarHH:

Zitat von Delphi.Narium:

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?