Laufzeit von Stored Procedure verkürzen

[Andidreas]

Ich verstehs nicht ganz...

Würdest Du jeden ermittelt Wert in eie TempTable schreiben um Performance zu sparen, oder würdest du jeden Select in die TempTable schreiben um die Unions zu vermeiden?

[Bummi]

ich würde
-die Results der Prozeduren in Temptabellen übernehmen ([fnSplit](@Brand und [fnSplit](@Productline)

-die gecasteten Felder, nur über die bis dahin bestehenden Temptabellen (ALL_Brand , ALL_ProductLine )gefiltert, in Temptabellen übernehmen

diese 6 Tabellen (#tmp1-6) würde ich für diese ALL_Pir(x)>0 etc und ALL_LowestPLC_SKU in
in die Resultabelle kumulieren

[jobo]

Ich habe den ersten Teil Deiner Unionabfrage umgebaut, so dass schätzungsweise ein Table Scan reicht.
Ist in Oracle gemacht, dürfte aber keine große Rolle spielen. Die Feldtypen habe ich natürlich "optimiert".

Statt der Split Funktion habe ich aus Bequemlichkeit eine normale Tabelle verwendet, geht ja nur um ein Beispiel.
Die Tabelle hat keine Indizes oder Constraints, knapp 1 Mio Datensätze.
Als Test habe ich noch eine Datumsspalte im 2. Select verwendet. Wirst Du wahrscheinlich auch haben / brauchen.

Code:

10:01:02 SQL> select * from fnsplit;
 
BRAND      PRODUCTLINE
---------- -----------
Mercedes   Carrera
Mercedes   B-Klasse
Porsche    Cayenne
Porsche    Boxter
Porsche    Carrera
 
Executed in 0,125 seconds
 
10:01:19 SQL> 
10:01:46 SQL>        Select 'Warehouse' as Storage, 'SKUs' as Typ,
           2              sum(PLCMIN25), sum(PLC_25), sum( PLC_30), sum( PLC_40 ), sum(PLC_50_55),
           3              sum(PLC_60), sum(PLC_70), sum(PLC_TOTAL) from (
           4              select
           5                 case when ALL_LowestPLC_SKU IN ('#', '00', '10', '20')
           6                      then 1 else 0 end as PLCmin25,
           7                 case when ALL_LowestPLC_SKU = ('25')
           8                      then 1 else 0 end as PLC_25,
           9                 case when ALL_LowestPLC_SKU = ('30')
          10                      then 1 else 0 end as PLC_30,
          11                 case when ALL_LowestPLC_SKU = '40'
          12                      then 1 else 0 end as PLC_40,
          13                 case when ALL_LowestPLC_SKU IN ( '50', '55')
          14                      then 1 else 0 end as PLC_50_55,
          15                 case when ALL_LowestPLC_SKU = '60'
          16                      then 1 else 0 end as PLC_60,
          17                 case when ALL_LowestPLC_SKU = '70'
          18                      then 1 else 0 end as PLC_70,
          19                 1 as PLC_total,
          20                 a.all_date
          21             From inv_all a
          22            Where ALL_Brand In (SELECT Brand FROM fnSplit)
          23              And ALL_ProductLine In (SELECT Productline FROM fnSplit)
          24              AND (ALL_AvailableStock > 0 OR
          25                   ALL_Pir > 0 OR
          26                   ALL_Pir1 > 0 OR
          27                   ALL_Pir2 > 0 OR
          28                   ALL_Pir3 > 0))x
10:01:46 SQL> /
 
STORAGE   TYP  SUM(PLCMIN25) SUM(PLC_25) SUM(PLC_30) SUM(PLC_40) SUM(PLC_50_55) SUM(PLC_60) SUM(PLC_70) SUM(PLC_TOTAL)
--------- ---- ------------- ----------- ----------- ----------- -------------- ----------- ----------- --------------
Warehouse SKUs           231      282634      183282       98890          98846      183942         110         999889
 
Executed in 1,155 seconds
 
10:01:50 SQL> delete from fnsplit s where s.brand ='Mercedes';
 
2 rows deleted
 
Executed in 0,047 seconds
 
10:03:05 SQL> 
10:03:22 SQL>        Select 'Warehouse' as Storage, 'SKUs' as Typ,
           2              sum(PLCMIN25), sum(PLC_25), sum( PLC_30), sum( PLC_40 ), sum(PLC_50_55),
           3              sum(PLC_60), sum(PLC_70), sum(PLC_TOTAL) from (
           4              select
           5                 case when ALL_LowestPLC_SKU IN ('#', '00', '10', '20')
           6                      then 1 else 0 end as PLCmin25,
           7                 case when ALL_LowestPLC_SKU = ('25')
           8                      then 1 else 0 end as PLC_25,
           9                 case when ALL_LowestPLC_SKU = ('30')
          10                      then 1 else 0 end as PLC_30,
          11                 case when ALL_LowestPLC_SKU = '40'
          12                      then 1 else 0 end as PLC_40,
          13                 case when ALL_LowestPLC_SKU IN ( '50', '55')
          14                      then 1 else 0 end as PLC_50_55,
          15                 case when ALL_LowestPLC_SKU = '60'
          16                      then 1 else 0 end as PLC_60,
          17                 case when ALL_LowestPLC_SKU = '70'
          18                      then 1 else 0 end as PLC_70,
          19                 1 as PLC_total,
          20                 a.all_date
          21             From inv_all a
          22            Where ALL_Brand In (SELECT Brand FROM fnSplit)
          23              And ALL_ProductLine In (SELECT Productline FROM fnSplit)
          24              AND (ALL_AvailableStock > 0 OR
          25                   ALL_Pir > 0 OR
          26                   ALL_Pir1 > 0 OR
          27                   ALL_Pir2 > 0 OR
          28                   ALL_Pir3 > 0))x
10:03:22 SQL> /
 
STORAGE   TYP  SUM(PLCMIN25) SUM(PLC_25) SUM(PLC_30) SUM(PLC_40) SUM(PLC_50_55) SUM(PLC_60) SUM(PLC_70) SUM(PLC_TOTAL)
--------- ---- ------------- ----------- ----------- ----------- -------------- ----------- ----------- --------------
Warehouse SKUs           231      199650      127974       71214          71170      128590         110         709379
 
Executed in 1,466 seconds
 
10:03:26 SQL> 
10:04:05 SQL>        Select 'Warehouse' as Storage, 'SKUs' as Typ, to_char(all_date, 'YYMM'),
           2              sum(PLCMIN25), sum(PLC_25), sum( PLC_30), sum( PLC_40 ), sum(PLC_50_55),
           3              sum(PLC_60), sum(PLC_70), sum(PLC_TOTAL) from (
           4              select
           5                 case when ALL_LowestPLC_SKU IN ('#', '00', '10', '20')
           6                      then 1 else 0 end as PLCmin25,
           7                 case when ALL_LowestPLC_SKU = ('25')
           8                      then 1 else 0 end as PLC_25,
           9                 case when ALL_LowestPLC_SKU = ('30')
          10                      then 1 else 0 end as PLC_30,
          11                 case when ALL_LowestPLC_SKU = '40'
          12                      then 1 else 0 end as PLC_40,
          13                 case when ALL_LowestPLC_SKU IN ( '50', '55')
          14                      then 1 else 0 end as PLC_50_55,
          15                 case when ALL_LowestPLC_SKU = '60'
          16                      then 1 else 0 end as PLC_60,
          17                 case when ALL_LowestPLC_SKU = '70'
          18                      then 1 else 0 end as PLC_70,
          19                 1 as PLC_total,
          20                 a.all_date
          21             From inv_all a
          22            Where ALL_Brand In (SELECT Brand FROM fnSplit)
          23              And ALL_ProductLine In (SELECT Productline FROM fnSplit)
          24              AND (ALL_AvailableStock > 0 OR
          25                   ALL_Pir > 0 OR
          26                   ALL_Pir1 > 0 OR
          27                   ALL_Pir2> 0 OR
          28                   ALL_Pir3> 0)
          29                   )x
          30            group by to_char(all_date, 'YYMM')
          31  /
 
STORAGE   TYP  TO_CHAR(ALL_DATE,'YYMM') SUM(PLCMIN25) SUM(PLC_25) SUM(PLC_30) SUM(PLC_40) SUM(PLC_50_55) SUM(PLC_60) SUM(PLC_70) SUM(PLC_TOTAL)
--------- ---- ------------------------ ------------- ----------- ----------- ----------- -------------- ----------- ----------- --------------
Warehouse SKUs 1103                                63       49242       31428       17682          17672       31590          30         174355
Warehouse SKUs 1102                                63       44234       28096       16022          16010       28270          30         157647
Warehouse SKUs 1101                                63       54450       34902       19422          19410       35070          30         193467
Warehouse SKUs 1104                                42       51724       33548       18088          18078       33660          20         183910
 
Executed in 0,889 seconds
 
10:04:08 SQL>

[Andidreas]

@jobo

Vielen Dank für deine mühe!
Gehst Du in deiner Lösung davon aus das es Felder wie z.B. "PLC_25", "PLC_30" usw. in der Datenbank gibt?

[jobo]

Nein, ich erzeuge sie über die Case Anweisungen. Eine statische Kreuztabelle sozusagen.
Die Transformation und auch Aggregation (aus Listenform in Spaltenform und Summierung) könnte man allerdings auch in einem Excelmakro durchführen, wenn Excel sowieso als Reportausgabe dient.
Dann muss das SQL jenachdem etwas mehr Daten ausspucken.

[Andidreas]

Ok habs verstanden...
Die Abfrage ist bei mir auch rasend schnell... Aber leider stimmt das Ergebnis nicht... da muss ich nochmal zeit investieren um das richtig hinzubekommen...

Aber die erste Abfrage war auch nicht mein zeitliches Problem...
Ich denke mal deine Lösung hilft mir bei den späteren Abfragen die Mengen sumiert werden z.B. bei WHs Stock

[Andidreas]

@jobo
Die Idee mit der Kreuztabelle ist genial
Ich hab die Stock Berechnungen darauf angepasst... Die dauern jetzt nur noch 8 Sekunden, davor waren es 31 Sekunden!