Laufzeit von Stored Procedure verkürzen

[Blup]

Stored Procedure 1 & 2

SQL-Code:

Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 2) 
.

Da der Unterstrich nicht im Case berücksichtigt wird, könnte man hier einkürzen:

SQL-Code:

Right(ALL_LowestPLC_SKU, 2)
.

Ich würde eine View einsetzen, damit die beiden Abfragen übersichtlicher werden:

SQL-Code:

Select *,
       CASE WHEN Right(ALL_LowestPLC_SKU, 1) = '#' OR
                 Right(ALL_LowestPLC_SKU, 2) IN ('00', '10', '20') THEN 1 ELSE 0 END as PLC_Lower25,
       CASE WHEN Right(ALL_LowestPLC_SKU, 2) = '25'                THEN 1 ELSE 0 END as PLC_25,
       CASE WHEN Right(ALL_LowestPLC_SKU, 2) = '30'                THEN 1 ELSE 0 END as PLC_30,
       CASE WHEN Right(ALL_LowestPLC_SKU, 2) = '40'                THEN 1 ELSE 0 END as PLC_40,
       CASE WHEN Right(ALL_LowestPLC_SKU, 2) IN ('50', '55')       THEN 1 ELSE 0 END as PLC_50_55,
       CASE WHEN Right(ALL_LowestPLC_SKU, 2) = '60'                THEN 1 ELSE 0 END as PLC_60,
       CASE WHEN Right(ALL_LowestPLC_SKU, 2) = '70'                THEN 1 ELSE 0 END as PLC_70,
                                                                        1            as PLC_total
From [inventory].[dbo].[inventory.inv_test]

Stored Procedure 3 - 6

Ebenfalls eine View:

SQL-Code:

Select *,
       CASE WHEN ALL_PLC IN ('#', '00', '10', '20') THEN 1 ELSE 0 END as PLC_Lower25,
       CASE WHEN ALL_PLC = '25'                     THEN 1 ELSE 0 END as PLC_25,
       CASE WHEN ALL_PLC = '30'                     THEN 1 ELSE 0 END as PLC_30,
       CASE WHEN ALL_PLC = '40'                     THEN 1 ELSE 0 END as PLC_40,
       CASE WHEN ALL_PLC IN ( '50', '55')           THEN 1 ELSE 0 END as PLC_50_55,
       CASE WHEN ALL_PLC = '60'                     THEN 1 ELSE 0 END as PLC_60,
       CASE WHEN ALL_PLC = '70'                     THEN 1 ELSE 0 END as PLC_70,
                                                         1            as PLC_total
From [inventory].[dbo].[inventory.inv_test]

Am Beispiel Procedure 3:

SQL-Code:

Select SUM(PLC_Lower25 * ALL_AvailableStock) as PLC_Lower25,
       SUM(PLC_25      * ALL_AvailableStock) as PLC_25,
       SUM(PLC_30      * ALL_AvailableStock) as PLC_30,
       SUM(PLC_40      * ALL_AvailableStock) as PLC_40,
       SUM(PLC_50_55   * ALL_AvailableStock) as PLC_50_55,
       SUM(PLC_60      * ALL_AvailableStock) as PLC_60,
       SUM(PLC_70      * ALL_AvailableStock) as PLC_70,
       SUM(PLC_TOTAL   * ALL_AvailableStock) as PLC_Total
From   MyView2
Where  ALL_Brand       In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) And
       ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))