Optimierung im Code

MDDaniel · 25 Mai 2023

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.

dabadepdu · 25 Mai 2023

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.

ukulele schrieb:
Du könntest die Abfrage umstellen, z.B. erst lag() und lead() im inneren Select machen

Ja!

ukulele schrieb:
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..

IchHH schrieb:
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.

ukulele schrieb:
Mich macht das ein bisschen stuzig..

Sehe ich auch so

IchHH schrieb:
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)

IchHH · 30 Mai 2023

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:

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.

ukulele · 30 Mai 2023

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)

IchHH · 30 Mai 2023

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.

MDDaniel · 30 Mai 2023

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.

dabadepdu schrieb:
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.

ukulele · 30 Mai 2023

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.

IchHH · 30 Mai 2023

@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_RowNumber	Test	WERKS	KWYEAR	TheFirstOfWeek	DateID	MATNR	Menge	WUNSCH
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

IchHH · 30 Mai 2023

ukulele schrieb:
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.

IchHH · 30 Mai 2023

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.

ukulele · 30 Mai 2023

Verglichen mit den 11 Sekunden oder den 4h?

IchHH · 30 Mai 2023

Verglichen mit den 4h.

ukulele · 30 Mai 2023

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?

Optimierung im Code

MDDaniel

SQL-Guru

dabadepdu

Datenbank-Guru

IchHH

Datenbank-Guru

ukulele

Datenbank-Guru

IchHH

Datenbank-Guru

MDDaniel

SQL-Guru

ukulele

Datenbank-Guru

IchHH

Datenbank-Guru

IchHH

Datenbank-Guru

IchHH

Datenbank-Guru

ukulele

Datenbank-Guru

IchHH

Datenbank-Guru

ukulele

Datenbank-Guru

Ähnliche Themen