Optimierung im Code

Noch eine Frage zu deinem ersten Posting.
Deine Beschreibung (Ziel) und deine Abfrage (SQL) stimmen ja nicht zusammen.
Du kriegst X zurück wenn in der Woche Menge 0 ist und in deren Vorwoche > 0 und zusätzlich kriegst du X zurück wenn in der Woche Menge > 0 und in deren Folgewoche kein Wert (NULL) steht.
Was soll jetzt wirklich rauskommen?

Und entweder hast du deine KWYEAR geändert oder deine Angabe passt nicht zu Posting #7.
 
Werbung:
Code:
SELECT *
  FROM (SELECT CASE
                 WHEN [ Menge ] = '0' AND LAG([ Menge ])
                  OVER(PARTITION BY WERKS, MATNR ORDER BY KWYEAR) > 0 THEN
                  'x'
                 WHEN [ Menge ] > '0' AND LEAD([ Menge ])
                  OVER(PARTITION BY WERKS, MATNR ORDER BY KWYEAR) IS NULL THEN
                  'x'
                 ELSE
                  ''
               END AS new_KW
          FROM B) A
 WHERE new_KW = 'x'
könnte auch sein (ohne Textumwandlung, generalisierte Where Bedingung):
Code:
SELECT *
  FROM (SELECT CASE
                 WHEN [ Menge ] = 0 AND LAG([ Menge ])
                  OVER(PARTITION BY WERKS, MATNR ORDER BY KWYEAR) > 0 THEN
                  1
                 WHEN [ Menge ] > 0 AND LEAD([ Menge ])
                  OVER(PARTITION BY WERKS, MATNR ORDER BY KWYEAR) IS NULL THEN
                  1
                 ELSE
                  0
               END AS new_KW
          FROM B
          where Menge >=0 ) A   -- Exclusion von Nullwerten in der Grundmenge
                                -- macht aber nur Sinn, falls die gar nicht interessieren
                                -- das könnte zu einer starken Einschränkung der Grundmenge führen (kannst nur Du wissen)
                                --   (die dann nicht mehr verarbeitet werden muss)
 WHERE new_KW = 1               -- sieht so aus, als ob die gar nicht interessieren
Die Order By Angabe in lag und lead ist sehr grob, Ergebnis dadurch unsicher. Ggf. partition clause wiederholen in order by.
Du könntest die Abfrage umstellen, z.B. erst lag() und lead() im inneren Select machen
Ja!

Du kannst auch lag() und lead() durch Joins ersetzen, dann geht alles in einer Abfrage.
Ich würde bezweifeln, dass das etwas bringt. "Eine Abfrage" ist schick, aber lag und lead durch Joins zu ersetzen aufwändig. Ergibt vermutlich keinen Single Pass..

und 11 Sekunden benötigt. Ich habe versucht die View mal in einer Tabelle umzuwandeln um zu schauen ob es ein Performance Unterschied macht, dieses ist nicht der Fall. Daher würde ich nun versuchen ein Index anzulegen
11 Sekunden ist viel für 400T Zeilen. Wenn eine daraus erzeugte Tabelle plus Index keine signifikante Verbesserung ergibt, greift auch der Index nur teilweise oder das System ist überlastet.
Siehe zum Index auch meine Umstellung des SQL Statements oben. Typkonvertierungen sind nicht gut für die Indexnutzung. Unnötige Typkonvertierungen sind bei Performanceproblemen sowieso zu vermeiden.
Wenn Du einen Index baust, sollte er die Reihenfolge der Verwendung im SQL Statement einhalten, sonst wird er wahrscheinlich auch nur teilweise genutzt.

Mich macht das ein bisschen stuzig..
Sehe ich auch so

Vielleicht sehr ihr was ich falsch gemacht habe.
Das würde ich erstmal nicht weiter verfolgen. Sondern das ursprüngliche Statement optimieren, wie zuvor angemerkt.

Das ursprüngliche Statement würde ich dabei Schrittweise aufbauen und ausführen (und den Ausführungsplan im Auge behalten), um die Stelle zu erkennen, wo es einbricht. Insgesamt am besten erst auf Basis der aus B erzeugten Tabelle.
Ich könnte mir vorstellen, dass die Case When Clause vom Optimizer wie ein Subselect umgesetzt wird, also wie die berühmte IF schleife. Das wäre dann ein Grund für den Genickbruch von 4h. Ist allerdings pure Vermutung.

Also erster Schritt:
Typkonvertierung raus
where Bedingung raus
case raus
und nur lag und lead zusätzlich ausgeben
Dabei kannst Du auch gleich kontrollieren, ob es überhaupt das ergibt, was Du erwartest.

Zweiter Schritt:
Case Statement dazu
(Das darf keinen Performance Unterschied ergeben)

Dritter Schritt:
where Kriterium rein
(u.U. gemäß meines Vorschlages auch die Grundmenge direkt filtern: Menge is Null raus, von Anfang an)
 
Ich habe mit meinen Halbwissen versucht alles umzusetzen. Der Server ist nicht in der Lage mir in adäquater Zeit ein Ergebnis zu liefern.

Ich würde daher gerne einen andere Lösungsweg versuchen, wo mir aber ein wenig die Erfahrung fehlt.

Ich würde gerne die Tabelle mit dem Join nun weglassen und statt dessen und mit ROW_NUMBER arbeiten...
SQL:
ROW_NUMBER () OVER (PARTITION BY MATNR, WERKS ORDER BY KWYEAR)
ABER... er zählt natürlich immer weiter wenn sich nichts an MATNR oder WERKS ändert, aber genau das möchte ich nicht. Heißt immer wenn die Menge 0 ist soll er von vorne beginnen. Ich habe nur keine Möglichkeit gefunden den ROW_NUMBER auf 0 zurückzusetzen.
Dadurch passiert leider folgendes:
1685437877822.png
Wie aber kann ich sagen, dass er bitte bei einer Menge 0 diese Zeile nicht zählen soll?
Ein Case When Bedingung á la:
SQL:
CASE WHEN Total_Menge_FERT_ABC <> 0 THEN ROW_NUMBER () OVER (PARTITION BY MATNR, WERKS ORDER BY KWYEAR) ELSE Total_Menge_FERT_ABC END AS Result_RowNumber
hilft hier leider nicht.
 
Spontan fällt mir dabei der einfachste Weg ein, die Zeilen in der Ausgabe weg lassen! Vermutlich willst du das nicht aber so richtig verstehe ich ja auch nicht was die Daten bedeuten.

Wie wäre Zählen mit sum()?
Code:
sum(CASE WHEN Total_Menge_FERT_ABC = 0 THEN 0 ELSE 1 END) OVER (PARTITION BY MATNR, WERKS ORDER BY KWYEAR)
 
Hallo @ukulele

das ist noch nicht das gewünschte Ziel. Sobald nur eine 0 in der Menge kommt, soll er wieder ab 1 anfangen zu zählen.

Jetzt würde er die letzte Summe behalten. Das soll er aber eben nicht.

Ich habe zwischenzeitlich über ein Fetch nachgedacht, nur ganz verstanden habe ich den noch nicht.
 
Fetch?

11 sec Verarbeitung in einem Teil das 4 Stunden dauert. Das entspricht 0,076 % der Gesamtverarbeitungszeit.
Variante A) Der Rest deiner Verarbeitung dauert nochmal so lange und du reduzierst den Ablauf von dieser Abfrage auf 1 Sec. dann dauert die Abfrage knapp 2 Stunden.
Variante B) Deine Abfrage (B) dauert 11 Sekunden und der Rest dauert 3 h 59 min und 49 sec. Dann dauert die Abfrage im besten Fall nach vollkommender Optimierung genau so lange.
Variante C) Der Rest deiner Abfrage dauert 1 Sekunde dann würde eine Optimierung viel bringen. Zu klären gilt es dann nur noch warum du die Abfrage 1200 mal durchläufst. Und da liegt meines Erachtens der Hase im Pfeffer. Wenn das ganze nämlich mit 1 Durchlauf geht dann bist du bei 12 Sekunden ohne das Statement zu korrigieren.

Und ohne zu wissen was du da wirklich aufführst, was das Ziel der Abfrage ist und wie der Aufbau deines Konstrukts (Gesamtabfrage) ist, ist das hier vergebene Liebesmühe.

Wenn Du einen Index baust, sollte er die Reihenfolge der Verwendung im SQL Statement einhalten, sonst wird er wahrscheinlich auch nur teilweise genutzt.
Dem widerspreche ich jetzt mal. Der Optimierer dreht das entsprechend. Viel wichtiger scheint mir die Datenverteilung. Da die Statistik auf dem beruht die für die Verwendung des Indexes ausschlaggebend ist.
 
Also mit Window-Functions sehe ich keine andere Möglichkeit als zwei Funktions in einander zu schachteln. Im inneren Select mache ich Total_Menge_FERT_ABC einmalig innerhalb der Partition, auch per sum() aber wie ist eigentlich egal, es muss nur einmalig sein. Im äußeren Select nehme ich "spalte" dann in die Partition mit auf und schon zählt es von vorne los.
Code:
WITH t1(MATNR,WERKS,KWYEAR,Total_Menge_FERT_ABC) AS (
    SELECT 100,100,'012023',1 UNION ALL
    SELECT 100,100,'022023',1 UNION ALL
    SELECT 100,100,'032023',0 UNION ALL
    SELECT 100,100,'042023',1 UNION ALL
    SELECT 100,100,'052023',1 UNION ALL
    SELECT 100,100,'062023',0 UNION ALL
    SELECT 100,100,'072023',0 UNION ALL
    SELECT 100,100,'082023',1 UNION ALL
    SELECT 200,100,'012023',1 UNION ALL
    SELECT 200,100,'022023',1 UNION ALL
    SELECT 200,100,'032023',0 UNION ALL
    SELECT 200,100,'042023',0 UNION ALL
    SELECT 200,100,'052023',1 UNION ALL
    SELECT 200,100,'062023',0
    )
    SELECT    *,
            sum(CASE WHEN Total_Menge_FERT_ABC = 0 THEN 0 ELSE 1 END) OVER (PARTITION BY MATNR, WERKS, spalte ORDER BY KWYEAR)
    FROM    (

        SELECT    *,
                sum(CASE WHEN Total_Menge_FERT_ABC = 0 THEN 1 ELSE 0 END) OVER (PARTITION BY MATNR, WERKS ORDER BY KWYEAR) AS spalte
        FROM    t1
        
            ) t2
    ORDER BY MATNR, WERKS, KWYEAR
Es geht auch anders als mit Window-Functions aber ich bin denke nicht das es schneller wird.
 
@MDDaniel Variante B ist bei mir der Fall. Da ich hier auch nicht weiter weiß habe ich mich nun darum bemüht die sehr lang dauernde Abfrage komplett abzulösen und mittels Count Row zu ersetzen. Daher ist für mich die Frage sehr wichtig, wie ich dafür sorgen kann folgende Ergebnis zu erhalten (Spalte WUNSCH):
Result_RowNumberTestWERKSKWYEARTheFirstOfWeekDateIDMATNRMengeWUNSCH
2​
2​
1044​
202421​
20.05.2024​
20240520​
59025​
11​
2​
1​
1​
1044​
202422​
27.05.2024​
20240527​
59025​
11​
1​
53​
16​
1010​
202322​
29.05.2023​
20230529​
94074​
1​
3​
52​
15​
1010​
202323​
05.06.2023​
20230605​
94074​
1​
2​
51​
14​
1010​
202324​
12.06.2023​
20230612​
94074​
1​
1​
50​
13​
1010​
202325​
19.06.2023​
20230619​
94074​
0​
0​
49​
13​
1010​
202326​
26.06.2023​
20230626​
94074​
1​
8​
48​
12​
1010​
202327​
03.07.2023​
20230703​
94074​
1​
7​
47​
11​
1010​
202328​
10.07.2023​
20230710​
94074​
1​
6​
46​
10​
1010​
202329​
17.07.2023​
20230717​
94074​
1​
5​
45​
9​
1010​
202330​
24.07.2023​
20230724​
94074​
1​
4​
44​
8​
1010​
202331​
31.07.2023​
20230731​
94074​
1​
3​
43​
7​
1010​
202332​
07.08.2023​
20230807​
94074​
1​
2​
42​
6​
1010​
202333​
14.08.2023​
20230814​
94074​
1​
1​
41​
5​
1010​
202334​
21.08.2023​
20230821​
94074​
0​
0​
40​
5​
1010​
202335​
28.08.2023​
20230828​
94074​
0​
0​
39​
5​
1010​
202336​
04.09.2023​
20230904​
94074​
0​
0​
38​
5​
1010​
202337​
11.09.2023​
20230911​
94074​
0​
0​
37​
5​
1010​
202338​
18.09.2023​
20230918​
94074​
0​
0​
36​
5​
1010​
202339​
25.09.2023​
20230925​
94074​
0​
0​
35​
5​
1010​
202340​
02.10.2023​
20231002​
94074​
0​
0​
34​
5​
1010​
202341​
09.10.2023​
20231009​
94074​
1​
1​
33​
4​
1010​
202342​
16.10.2023​
20231016​
94074​
0​
0​
 
Also mit Window-Functions sehe ich keine andere Möglichkeit als zwei Funktions in einander zu schachteln. Im inneren Select mache ich Total_Menge_FERT_ABC einmalig innerhalb der Partition, auch per sum() aber wie ist eigentlich egal, es muss nur einmalig sein. Im äußeren Select nehme ich "spalte" dann in die Partition mit auf und schon zählt es von vorne los.
Code:
WITH t1(MATNR,WERKS,KWYEAR,Total_Menge_FERT_ABC) AS (
    SELECT 100,100,'012023',1 UNION ALL
    SELECT 100,100,'022023',1 UNION ALL
    SELECT 100,100,'032023',0 UNION ALL
    SELECT 100,100,'042023',1 UNION ALL
    SELECT 100,100,'052023',1 UNION ALL
    SELECT 100,100,'062023',0 UNION ALL
    SELECT 100,100,'072023',0 UNION ALL
    SELECT 100,100,'082023',1 UNION ALL
    SELECT 200,100,'012023',1 UNION ALL
    SELECT 200,100,'022023',1 UNION ALL
    SELECT 200,100,'032023',0 UNION ALL
    SELECT 200,100,'042023',0 UNION ALL
    SELECT 200,100,'052023',1 UNION ALL
    SELECT 200,100,'062023',0
    )
    SELECT    *,
            sum(CASE WHEN Total_Menge_FERT_ABC = 0 THEN 0 ELSE 1 END) OVER (PARTITION BY MATNR, WERKS, spalte ORDER BY KWYEAR)
    FROM    (

        SELECT    *,
                sum(CASE WHEN Total_Menge_FERT_ABC = 0 THEN 1 ELSE 0 END) OVER (PARTITION BY MATNR, WERKS ORDER BY KWYEAR) AS spalte
        FROM    t1
       
            ) t2
    ORDER BY MATNR, WERKS, KWYEAR
Es geht auch anders als mit Window-Functions aber ich bin denke nicht das es schneller wird.
Perfekt! Mit deinen Daten und einem DESC hinter KWYEAR macht es genau das was es soll. Nun werde ich mal versuche das auf meine Daten umzusetzen.
 
Das hat geklappt. Die gesamte Abfrage dauert 23 Sekunden über alle 360T Datensätze. Nun muss ich mir das noch einmal in Ruhe anschauen und es verstehen.
 
Werbung:
Ich sollte Lizenzgebühren nehmen :-)

Die letzten Sekunden kleiner zu kriegen könnte schwer werden aber denkbar. Kommen die Daten noch übers Netzwerk? Clustered Index auf MATNR, WERKS, KWYEAR (ggf. DESC) ist vorhanden und wird genutzt?
 
Zurück
Oben