Sum() OVER oder Join?

I

IchHH

Datenbank-Guru
Beiträge
291
Moin Moin,

ich versuche gerade in eine Tabelle mit Warenein- und ausgängen so pro Material und Werk zu ermitteln wann (Tag und Menge) ist die pro Buchungstag vorhanden Ware aufgrund der Warenausgänge aufgebraucht sind.

Dabei verfolgte ich zwei Ansatzpunkte.
1. Sum () Over (Partion by ... Order by) und
2. Select innerhalb des Select Statements
3. Left Join auf sich selbst

Das Problem bei 1 besteht meines Erachtens nach darin, dass ich keine Möglichkeit sehe das Ergebnis noch einmal zu nutzen um zu sagen: "Prüfe ob die Menge am Buchungstag identisch bzw. größer und kleiner mit der Sum() Over Wert ist

Bei 2 ahben ich selbige Schwierigkeiten und bei
den 3. Punkt vermehren sich die Daten ungewollt obwohl ich nur einen Wert zuückerwartet hätte.

Könnt ihr mir sagen welchen ansatz ihr verfolgen würdet?
 
Werbung:
Lass mal sortieren:
- Ware
- Material
- Werk
- Tag
- Menge

Lassen wir mal die Ware weg, keine Ahnung aus welchem Material sich die zusammensetzt.

Wenn Du Material im Zu und Abgang pro Tag aufsummierst, also positive und negative Zahlen, dann wird es Tage geben, wo Material 0 oder kleiner 0 ist.
Dann gäbe es noch den Zusammenhang, dass nichts rausgehen kann, wenn nichts da ist ...
Betrachtet man das in der Zukunft, was anderes macht m.E. nicht so viel Sinn, dann wäre das nur auf Planzahlen machbar.

Wenn das auf Materialebene gelöst ist, kann man es auf Ware umlegen.

Für die Summe von Zu und Abgängen braucht man eigentlich nur ein Sum() mit Group By.
Dazu dann mit Window Function ein > RunningTotal.
 
Ein Total Running ist es in diesem Fall allerdings nicht, weil mit zu einen jeden Stichtag nicht die zukünftigen Wareneingänge sondern nur die Warenausgänge interessieren und ich wissen möchte, wie lange reichen diese.

Ich denke daher das sogar ein Subselect die richtige Wahl darstellt, da ich zum einen die Warenausgänge identifizieren muss und zum anderen eine Aufsummierung der Warenausgänge vornehmen muss. Bis ich den Wert exakt oder gar mit den Warenausgang sogar um x unterschritten hätte.

Das Problem am Subselect ist jedoch, dass ich keine Lösung dafür finde, wie ich sagen dass er den mit SUM() OVER kumulierten Wert nutzen soll um zu prüfen die Ausgangsmenge zwischen den SUM () OVER Wert liegt.
 
Hier auch einmal mein Ansatz als Code für ein Material exemplarisch:

SELECT

Menge
,Buchungsdatum
,Materialnummer
,WERK
,[Belegnummer des Einkaufsbelegs]

,(
SELECT SUM(Menge) OVER (PARTITION BY MATERIALNUMMER, WERK ORDER BY BUCHUNGSDATUM) test FROM Anreicherung_2 AR
WHERE
AR.WERK like '1010%'
AND AR.Menge_Original < 0
AND AR.[Wert in Hauswährung] IS NULL
AND AR.Buchungsdatum > ar2.Buchungsdatum
AND AR.WERK = ar2.WERK
AND AR.Materialnummer = ar2.Materialnummer

) Menge_Ausgang

FROM Anreicherung_2 ar2

WHERE
Materialnummer = '5109@1'

Nach 15 Minuten haben ich jedoch noch immer kein Ergebnis, was mir zeigt das der Ansatz nicht praktikabel ist.
 
IchHH schrieb:
Nach 15 Minuten haben ich jedoch noch immer kein Ergebnis, was mir zeigt das der Ansatz nicht praktikabel ist.
Zum Vergrößern anklicken....
Naja, es ist wahrscheinlich nicht optimal gemacht, praktikabel ist die Grundidee wahrscheinlich schon.
Ich habe noch nicht komplett verstanden, was Du vorhast und ich kann nicht beurteilen, was hinter views wie anreichung2 steckt, geschweige das Datenmodel insgesamt beurteilen.
Aber dein Select Statement liegt vor und es ziemlich sicher nicht der beste Ansatz.

Du hast hier ein Subselect gebaut, das in einem großen, kaum eingeschränkten ( Materialnummer = '5109@1') Select läuft. Das Subselect ist nur lose gegen das Hauptselect gejoint (> buchungsdatum). Weder der Hauptfilter aus dem Hauptselect noch der Hauptfilter aus dem Subselect sind in beiden Mengen vorhanden, beide Mengen scheinen mir daher unnötig groß.
Das Grundproblem dieser Subselect Konstruktion ist aber ein anderes:
Für jeden Datensatz, den das Hauptselect liefert, muss das Subselect neu gemacht werden. Das kann zu sehr großen Datenbewegungen führen, die natürlich viel zu lange dauern.
Mache aus dem Subselect eine eigenständige Menge und joine diese mit dem Hauptselect. Filtere beide Mengen möglichst stark und möglichst gleichmäßig.
 
Alle drei Wege sind theoretisch möglich. 2 und 3 sind Performance-lastig, auch bei richtiger Anwendung, das spricht für 1. Was aus deiner Sicht gegen eine Window-Function spricht, verstehe ich nicht. Du kannst, wenn das Ergebnis aus der Window-Function aus einem eigenen Select kommt, auch damit weiter arbeiten / rechnen / prüfen.

Gegen den Ansatz 1 spricht nur die Menge an Daten die über die Zeit entstehen kann. Irgendwann wird man sicher nicht mehr mit alten Transaktionen rechnen wollen sondern einen Zwischenstand einspeichern wollen, z.B. zu einer Inventur. Aber mit so einem Fixpunkt kann man ja auch arbeiten.
 
Ich habe eine Lösung gefunden. Diese Funktioniert aber nur wenn ich in der Where Bedingung auf ein Material einfiltere. Sobald ich es ohne mache, läuft es irgendwann auf einen TimeOut weshalb ich Fragen wollte ob mir jemand beim Optimieren des Codes behilflich sein kann. Ich weiß nämlich nicht wie ich automatisch immer nur eine Materialnummer übergeben oder aber ich Abfrage so optimiere, dass er auch mal Ergebnisse in die into Tabelle überliefert. Hier der Code:
SQL: 
 SELECT

             Anreicherung_3.Menge
            ,Anreicherung_3.Materialnummer
            ,Anreicherung_3.WERK
            ,Anreicherung_3.Buchungsdatum_Anr2
            ,Anreicherung_3.[Wert in Hauswährung]
            ,Anreicherung_3.Kontonummer
            ,Anreicherung_3.Referenz
            ,Anreicherung_3.BUDAT
            ,Anreicherung_3.[Belegposition des Einkaufsbelegs]
            ,Anreicherung_3.Materialstatus
            ,Anreicherung_3.Materialklasse
            ,Anreicherung_3.Warengruppe
            ,Anreicherung_3.Cluster_1
            ,Anreicherung_3.Cluster_2
            ,Anreicherung_3.BELEGART

            ,CASE WHEN Anreicherung_3.[Belegnummer des Einkaufsbelegs]='0' THEN NULL ELSE Anreicherung_3.[Belegnummer des Einkaufsbelegs] END AS [Belegnummer des Einkaufsbelegs]
            ,AR2.Menge_Original
            ,AR2.Buchungsdatum Buchungsdatum_AR2
            ,SUM(AR2.Menge_Original) OVER (PARTITION BY Anreicherung_3.MATERIALNUMMER, Anreicherung_3.WERK, Anreicherung_3.Buchungsdatum_Anr2 ORDER BY AR2.Buchungsdatum)*-1 sum_WA

            ,CASE WHEN
                Anreicherung_3.Menge BETWEEN
                    SUM(AR2.Menge_Original) OVER (PARTITION BY Anreicherung_3.MATERIALNUMMER, Anreicherung_3.WERK, Anreicherung_3.Buchungsdatum_Anr2 ORDER BY AR2.Buchungsdatum)*-1
                    AND
                    SUM(AR2.Menge_Original) OVER (PARTITION BY Anreicherung_3.MATERIALNUMMER, Anreicherung_3.WERK, Anreicherung_3.Buchungsdatum_Anr2 ORDER BY AR2.Buchungsdatum)*-1
                  THEN 1 ELSE 0 END AS Test into _Test_DDS

  From [dbo].[_DDS_Gesamt] AS Anreicherung_3 --581.995

    INNER JOIN
    [dbo].[_DDS_WA] AS AR2
     ON Anreicherung_3.Materialnummer = AR2.Materialnummer
    AND Anreicherung_3.WERK = AR2.WERK
    AND AR2.Buchungsdatum > Anreicherung_3.Buchungsdatum_Anr2
 
Das lässt sich so einfach nicht optimieren, selbst wenn du uns mehr Informationen gibst. Dafür spielen einfach zu viele Faktoren eventuell eine Rolle.

- Von welcher Laufzeit reden wir denn hier mit WHERE-Bedingung auf Materialnummer? < 1 Sekunde oder auch schon ein höherer Bereich?
- 581.995 Datensätze sind dann mit WHERE-Bedingung? Ohne läuft es ja nicht.
- Sind [_DDS_Gesamt] und [_DDS_WA] Sichten oder physische Tabellen?
- Welche Indexe gibt es auf [_DDS_Gesamt] und [_DDS_WA]? Werden die im Ausführungsplan genutzt?

Am besten ist immer, man fängt klein an mit der Abfrage Logik und achtet drauf, bei welcher Anpassung die Abfragezeit hoch geht. Das kommt auch mal an mehreren Stellen vor und am Ende potenzieren sich diese Ausführungszeiten irgendwo.
 
IchHH schrieb:
weshalb ich Fragen wollte
Zum Vergrößern anklicken....

Was ich auffällig finde sind die Joins über Materialnummer (und Werk). Sowas ginge normalerweise über eine ID. Für mich sieht es so aus, dass anreicherung_xy ein View ist. Damit wäre ein Optimierungsansatz auf der Basis des Select Statements oben vergebene Mühe.
Außerdem wundert mich das extreme Verhalten, dass bereits bei mehr als einer Materialnummer die Ausführungszeiten stark steigen. Zweite Vermutung ist daher, dass der Join unvollständig ist (Permutation) oder die Basismenge (view) nicht eindeutig bezogen auf die Join Kriterien.
Ein Standardverfahren für die Untersuchung langsamer Ausführung ist die Abfrage der Ausführungspläne (explain), um zu sehen, wo Indizes verwendet werden und wo nicht.
 
Nachtrag:
Der Join mit dem > Operator ergibt per se eine Vervielfachung.
Ohne Datenmodell und Beispiel-Daten, die verschiedene Fälle abdecken, ist das schwer zu beurteilen, so wie @ukulele schon schrieb.

Aber noch ein paar Details:
Welcher Natur ist diese Datum: ..ANDAR2.Buchungsdatum > Anreicherung_3.Buchungsdatum_Anr2
Datetime mit Sekunden oder noch "schlimmer" oder kummuliert auf den Tag ohne Zeitanteil?
Das hier sieht redundant / falsch aus:
IchHH schrieb:
..
SUM(AR2.Menge_Original) OVER (PARTITION BY Anreicherung_3.MATERIALNUMMER, Anreicherung_3.WERK, Anreicherung_3.Buchungsdatum_Anr2 ORDER BY AR2.Buchungsdatum)*-1 sum_WA ,
CASE WHEN Anreicherung_3.Menge BETWEEN
SUM(AR2.Menge_Original) OVER (PARTITION BY Anreicherung_3.MATERIALNUMMER, Anreicherung_3.WERK, Anreicherung_3.Buchungsdatum_Anr2 ORDER BY AR2.Buchungsdatum)*-1
AND
SUM(AR2.Menge_Original) OVER (PARTITION BY Anreicherung_3.MATERIALNUMMER, Anreicherung_3.WERK, Anreicherung_3.Buchungsdatum_Anr2 ORDER BY AR2.Buchungsdatum)*-1
THEN 1
ELSE 0
END AS Test into _Test_DDS
Zum Vergrößern anklicken....
Der Between Vergleich läuft in 2 identischen Ausdrücken?
Der Sum() over Ausdruck wird 3x identisch abgerufen, ich weiß nicht, wie gut der Optimizer das umsetzt. Besser wäre vielleicht eine CTE oder verschachtelte Abfrage mit nur einem Aufruf.
Wozu dient das "into _Test_DDS"?
Wenn Du sowieso mit Window Functions arbeitest, könntest Du den Join vielleicht ganz weglassen und alle benötiten Vorgänger-Werte bzw. Aggregate (AR2) mittels Lag/Lead, Sum() over holen. Dann käme man vielleicht zu einer Single Pass Query ohne Datenwiederholung.
 
Es handelt sich um ausschließlich Tabellen die hier gejoint werden, keine Views.
Auf der DDS_Gesamt liegt ein Index der wie folgt aussieht.

SQL: 
CREATE NONCLUSTERED INDEX [Materialnummer_WERK_Buchungsdatum_Anr2] ON [dbo].[_DDS_Gesamt]
(
    [Materialnummer] ASC,
    [WERK] ASC,
    [Buchungsdatum_Anr2] ASC
)
INCLUDE([Menge],[Wert in Hauswährung],[Kontonummer],[Referenz],[BUDAT],[Belegposition des Einkaufsbelegs],[Materialstatus],[Materialklasse],[Warengruppe],[Cluster_1],[Cluster_2],[BELEGART],[Belegnummer des Einkaufsbelegs]) WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

Die Abfrage auf ein einziges Material dauert 3-4 Sekunden, je nach Auslastung des Systems.

Die Tabellen als CTE zu nutzen habe ich bereits versucht und brachte keine Besserung.
 
Das hier sieht redundant / falsch aus
Zum Vergrößern anklicken....
Im Code geht es darum. Das ich zu jeden Material, Werk, Datum aus der Tabelle _DDS_Gesamt die Menge abgeglichen wird mit der aufkumulierte Summe der Tabelle _DDS_WA. Wobei die Tabelle _DDS_WA das Datum ausgeben soll. Wo die Eingangsmenge je Zeile aus _DDS_Gesamt (in Verbindung mit Werk und Material) erreicht bzw. überschritten wird. Wenn also 70 STück aus Gesamt kommen, dann will ich wissen an welchen Tag ist die Menge aufgebraucht wenn ich die Aufkumulierung des Warenausgangs der Tabelle _DDS_WA nutze. Dabei handelt es sind um eine Retrospektive die Analysiert werden soll.
 
IchHH schrieb:
Es handelt sich um ausschließlich Tabellen die hier gejoint werden, keine Views.
Auf der DDS_Gesamt liegt ein Index der wie folgt aussieht.

SQL: 
CREATE NONCLUSTERED INDEX [Materialnummer_WERK_Buchungsdatum_Anr2] ON [dbo].[_DDS_Gesamt]
(
    [Materialnummer] ASC,
    [WERK] ASC,
    [Buchungsdatum_Anr2] ASC
)
INCLUDE([Menge],[Wert in Hauswährung],[Kontonummer],[Referenz],[BUDAT],[Belegposition des Einkaufsbelegs],[Materialstatus],[Materialklasse],[Warengruppe],[Cluster_1],[Cluster_2],[BELEGART],[Belegnummer des Einkaufsbelegs]) WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

Die Abfrage auf ein einziges Material dauert 3-4 Sekunden, je nach Auslastung des Systems.

Die Tabellen als CTE zu nutzen habe ich bereits versucht und brachte keine Besserung.
Zum Vergrößern anklicken....
Also eine Abfrage auf [dbo].[_DDS_Gesamt] nach [Materialnummer] verwendet gemäß Execution Plan den Index [Materialnummer_WERK_Buchungsdatum_Anr2] und dauert dennoch 3-4 Sekunden? Wie viele Zeilen hat das Ergebnis, wie viele Datensätze hat die Tabelle?

Ich finde das deutlich zu langsam. Das müssten schon entweder extrem viele Datensätze sein oder ein sehr großes Ergebnis sein oder du hast einen sehr langsamen Server oder ein sehr langsames Netzwerk oder etwas in der Art.
 
Die [_DDS_Gesamt] hat 16.513.520 Datensätze und die [_DDS_WA] hat 3.034.508 Datensätze.
Wenn ich nur ein Material nutze, dann würden vor der Filterung 464.086 Datensätze erzeugt werden.
Es gibt 16.400 Material & Werks Komibnationen
 
Werbung:
Ich hatte ja erstmal nur von der [_DDS_Gesamt] gesprochen. Du musst wirklich mit dem Execution Plan arbeiten und dich da langsam ran tasten. Fange mit der kleinsten Abfrage an und versuche heraus zu finden, wo der Flaschenhals steckt.
 

Ähnliche Themen

I
Werte aus Gerade berechneter Spalte
2
Antworten
18
Aufrufe
751
ukulele
ukulele
Zurück
Oben