Optimierung im Code

[IchHH]

Hallo,
ich würde gerne von euch wissen wie ich folgenden Code so ändern kann, dass ich schneller Ergebnisse bekomme.

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'

Zudem was der Code machen soll. Immer wenn eine Reihe in der Spalte Menge einen Wert hat der "0" ist und der nach Sortierung von Werk, Material und KWYEAR in der davor liegenden Reihe keine Null hat, soll dieser mit einem "X" markiert werden, da ich danach in einer weiteren Abfrage weiter arbeiten möchte. Diese Prozess hier dauert aber sehr lange.

 

[dabadepdu]

Ich nehme mal an, dass brauchbare Indizierung da ist?
Hast Du mal einen Ausführungsplan angeschaut?

Ich sehe eigentlich kein großes Optimierungspotential. Falls Tabelle "B" wirklich eine Tabelle ist, bleibt fast nur die Indizierung. Falls B ein View ist bzw. eine verschachtelte Abfrage, sollte man dort weiter schauen.
innerhalb von A könnte es vielleicht gute Kriterien geben, die GEsamtmenge deutlich einzuschränken. Also Spaltenwerte die die Gesamtmenge extrem verkleinern, aber natürlich nicht die gesuchte Menge verändern. Vor allem irgendwelche Datumswerte (also ALtdaten rausfiltern).

 

[MDDaniel]

Ein paar Fragen: Gibt es wirklich Zeilen in denen die Menge NULL ist? Wenn ja, warum ist die Menge nicht 0?
Was steht in KWYEAR z.B. inhaltlich drinnen?
Gibt es ausser KWYEAR eine andere Möglichkeit zu sortieren wenn es um das LAG geht?
Liegt es in deiner Hand die Struktur anzupassen?
Wieviele Zeilen hat die Tabelle insgesamt und wieviel mit Menge 0?

 

[ukulele]

Du könntest die Abfrage umstellen, z.B. erst lag() und lead() im inneren Select machen und dann CASE ersetzen durch eine WHERE Bedingung im äußeren Select. Du kannst auch lag() und lead() durch Joins ersetzen, dann geht alles in einer Abfrage.

Aber ganz ehrlich: Das können wir unmöglich nur aufgrund des Codes bewerten. Der tatsächliche Ausführungsplan und deine Indizes verraten dir mehr. Auch wie viele Datensätze in wie viel Zeit betroffen sind bleibt dein Geheimnis. Mit SET STATISTICS TIME ON kannst du die genaue Dauer der Abfrage ermitteln, das ist zum Finetuning praktisch.

 

[IchHH]

Wow, da ist nun viel an Fragen und Input dabei den ich versuche mal nachzugehen. Die Quelle "B" ist eine View die selber nur "365.223" Zeilen mit 18 Spalten beinhaltet 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. Ich würde ihn wie folgt schreiben:

CREATE NONCLUSTERED INDEX [NonClusteredIndex-20230524-111243] ON [dbo].[B]
(
    [WERKS] ASC,
    [KWYEAR] ASC,
    [MATNR] ASC,
    [Total_Menge_FERT_ABC] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

würdet ihr das auch machen?

Zum Ausführungsplan, dieser sieht wie folgt aus:

Ob nun die Idee mit dem Joins noch gut ist würde ich nun gerne von euch wissen. Die Idee finde ich aber irgendwie charmant. Ebenfalls interessant finde ich die Idee die LAG & LEAD Funktion in den Inneren Select zu tun.

 

[MDDaniel]

2 von 7 Fragen beantwortet.

Bin gespannt ob der Optimierer den Index überhaupt verwendet.
Du kannst ja mal versuchen noch einen Index mit dem selben Inhalt zu bauen aber die Reihenfolge wie folgt:

[Total_Menge_FERT_ABC] ASC,
[WERKS] ASC,
[MATNR] ASC,
[KWYEAR] ASC

Und dann schaust welchen er verwendet.

 

[IchHH]

Zu den anderen Fragen:
Was steht in KWYEAR z.B. inhaltlich drinnen? Kalenderwoche+Jahr (z.B. 092023)
Gibt es ausser KWYEAR eine andere Möglichkeit zu sortieren wenn es um das LAG geht? Leider nein.
Liegt es in deiner Hand die Struktur anzupassen? Ja
Wieviele Zeilen hat die Tabelle insgesamt und wievel mit Menge 0? 365.223 davon 295.385 mit Wert 0.

Wie sehe ich den welchen Index SQL nutzt?

 

[MDDaniel]

Im Ausführungsplan wird dir ein Index Scan oder Index Seek angegeben und der verwendete Index angeführt.

Also wenn du die Werte von innerhalb 1 Jahres im Lag / Lead haben willst dann KWYEAR funktioniert damit nur innerhalb eines Jahres korrekt, dass ist dir vielleicht / hoffentlich aufgefallen (Vielleicht beinhaltet die Tabelle ja auch nur die Daten 1 Jahres)

 

[IchHH]

Er nutzt tatsächlich beide. Verwendet aber den zweiten Häufiger und die Kosten liegen auch 3x bei 18%, gefolgt von Sortierung mit 3x15%. Wenn ich dann ein Index auf die Spalten anlege die er tatsächlich nutzt (WERKS, MATNR und KWYEAR) geht zwar schneller nur die Daten würden dennoch erst in 4 Std fertig sein.

 

[ukulele]

Mich macht das ein bisschen stuzig das du aus den Daten der View eine vollwertige Tabelle gemacht hast und das die Abfrage nicht beeinflusst haben soll. Führst du die Abfrage direkt auf dem Server aus oder ist da ein Netzwerk dazuwischen? 300k Datensätze sollte die DB eigentlich schneller sortieren können, auch ganz ohne Index.

 

[MDDaniel]

Wenn ich dann ein Index auf die Spalten anlege die er tatsächlich nutzt (WERKS, MATNR und KWYEAR) geht zwar schneller nur die Daten würden dennoch erst in 4 Std fertig sein.

Das ist mir jetzt unklar.
Oben bei #5 hast du geschrieben die Abfrage dauere 11 Sekunden jetzt schreibst du von 4 Stunden.
Solche Sprünge oder Informations-Fragmente sind verwirrend!

 

[IchHH]

Ich komme irgendwie nicht weiter.

die 11 Sekunden bezogen sich auf die Basis Tabelle "B". Sobald ich diese aber mit meinem Code verwende, braucht er mehr als 4 Stunden um überhaupt Ergebnisse zu liefern. Da ich Performancetechnisch wohl alles richtig gemacht habe und keine andere Lösung finden, bin ich auf den Versuch von @ukulele nachzugehen und mit Joins zu arbeiten. Hier ist aber das Problem, dass ich offensichtlich etwas mit meinem LAST_VALUE Code falsch mache, da dort nur "NULL" rauskommt.

Vielleicht sehr ihr was ich falsch gemacht habe.

Hier der Code.

SELECT B.[WERKS]
      ,B.[KWYEAR]
      --,ISNULL(CASE WHEN B.[Total_Menge_FERT_ABC] > 0 THEN (SELECT TOP 1 C.KWYEAR FROM Markierung C WHERE B.WERKS = C.WERKS
            --    AND B.MATNR = C.MATNR AND C.KWYEAR > B.KWYEAR ORDER BY C.KWYEAR ASC) ELSE B.[KWYEAR] END,B.KWYEAR) AS Test
      ,CASE WHEN b.Total_Menge_FERT_ABC = 0 THEN b.KWYEAR ELSE LAST_VALUE(c.[KWYEAR]) OVER(PARTITION BY c.MATNR, c.WERKS Order by c.KWYEAR) END AS LastValue
      ,B.[TheFirstOfWeek]
      ,B.[DateID]
      ,B.[MATNR]
      ,B.[Total_Menge_FERT_ABC]
      ,B.[WB_Menge_FERT_Soll]
      ,B.[WB_Menge_FERT_max]
      ,B.[WE_Menge_FERT_Max]
      ,B.[WE_Total_Menge_FERT_ABC]
      ,B.[WA_Menge_FERT]
      ,B.[CN_Menge_FERT_Max]
      ,B.[CN_Total_Menge_FERT_ABC]
      ,B.[ABC_Kat]
      ,B.[ABC_Wert]
      ,B.[IsPlanned]
      ,B.[Verfuegbarkeit_ABC]
      ,B.[Verfuegbarkeit_Shop]
  FROM B
        LEFT JOIN
            Markierung AS C
                 ON B.WERKS = C.WERKS
                AND B.MATNR = C.MATNR
                AND B.KWYEAR = (C.KWYEAR-1)--CASE WHEN RIGHT(C.KWYEAR+1,2) > DATEPART(wk, CONCAT(YEAR(GETDATE()),'1231')) THEN CONCAT(YEAR(GETDATE())+1,'01') ELSE C.KWYEAR+1 END

 

[IchHH]

Achso, was ihr noch wissen müssten in der Tabelle "Makierung" steht folgendes drinnen

new_KW KWYEAR WERKS MATNR
x 202339 1010 000000000000059025
x 202421 1044 000000000000059025
x 202325 1010 000000000000094074
x 202334 1010 000000000000094074
x 202349 1010 000000000000094074

 

[MDDaniel]

Deine gesamte Abfrage dauert 4 Stunden und du kümmerst dich um den Part der 11 Sekunden dauert??
Das kann nur dann Sinn machen wenn du in deinem Ablauf einen Knopf drinnen hast und die ganze Zeit auf "dieser Grundabfrage" rum reitest.
Also muss es doch ganz wo anders klemmen.

Er nutzt tatsächlich beide.

Beide Indexe oder beide Operatoren beim Index, Scan und Seek?

Steht in der Spalte Firstofweek ein Datum drinnen?

 

[ukulele]

KWYEAR ist eventuell eine Zeichenkette und wenn du dann -1 rechnest wird konvertiert. Dabei gehen mögliche führende Nullen verloren, dann passt eventuell der Join nicht mehr aber alles Spekulation.

DECLARE    @KWYEAR VARCHAR(10) = '012023'
SELECT    @KWYEAR,@KWYEAR-1

Es ist nicht ganz abwägig die 11 Sekunden zu optimieren, eventuell wird die Abfrage mehrfach ausgeführt. Aber grundsätzlich scheinen natürlich auch noch andere Dinge nicht ganz optimal zu laufen