Laufzeit von Stored Procedure verkürzen

[Andidreas]

Zitat von Andidreas:

SQL-Code:

/* Stored Procedure 1 */
Select
SUM(PLC_Lower25) as PLC_Lower25, SUM(PLC_25) as PLC_25, SUM( PLC_30) as PLC_30,
SUM( PLC_40 ) as PLC_40, SUM(PLC_50_55) as PLC_50_55, SUM(PLC_60) as PLC_60,
SUM(PLC_70) as PLC_70, SUM(PLC_TOTAL) as PLC_Total
From
(
Select CASE WHEN Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 2) IN ('00', '10', '20') OR Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 1) IN ('#') THEN 1 ELSE 0 END as PLC_Lower25,
       CASE WHEN Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 2) = ('25') THEN 1 ELSE 0 END as PLC_25,
       CASE WHEN Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 2) = ('30') THEN 1 ELSE 0 END as PLC_30,
       CASE WHEN Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 2) = '40' THEN 1 ELSE 0 END as PLC_40,
       CASE WHEN Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 2) IN ( '50', '55') THEN 1 ELSE 0 END as PLC_50_55,
       CASE WHEN Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 2) = '60' THEN 1 ELSE 0 END as PLC_60,
       CASE WHEN Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 2) = '70' THEN 1 ELSE 0 END as PLC_70,
       1 as PLC_total
 From [inventory].[dbo].[inventory.inv_test]
 Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
 And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
 AND   (ALL_AvailableStock > 0
 OR     ALL_Pir > 0
 OR     ALL_Pir1 > 0
 OR     ALL_Pir2 > 0
 OR     ALL_Pir3 > 0)
Group By (ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU)
) TempTable


/* Stored Procedure 2 */
Select
SUM(PLC_Lower25) as PLC_Lower25, SUM(PLC_25) as PLC_25, SUM( PLC_30) as PLC_30,
SUM( PLC_40 ) as PLC_40, SUM(PLC_50_55) as PLC_50_55, SUM(PLC_60) as PLC_60,
SUM(PLC_70) as PLC_70, SUM(PLC_TOTAL) as PLC_Total
From
(
Select CASE WHEN Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 2) IN ('00', '10', '20') OR Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 1) IN ('#') THEN 1 ELSE 0 END as PLC_Lower25,
       CASE WHEN Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 2) = ('25') THEN 1 ELSE 0 END as PLC_25,
       CASE WHEN Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 2) = ('30') THEN 1 ELSE 0 END as PLC_30,
       CASE WHEN Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 2) = '40' THEN 1 ELSE 0 END as PLC_40,
       CASE WHEN Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 2) IN ( '50', '55') THEN 1 ELSE 0 END as PLC_50_55,
       CASE WHEN Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 2) = '60' THEN 1 ELSE 0 END as PLC_60,
       CASE WHEN Right((ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU), 2) = '70' THEN 1 ELSE 0 END as PLC_70,
       1 as PLC_total
 From [inventory].[dbo].[inventory.inv_test]
 Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
 And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
 AND   (ALL_ClosingStockQTY > 0
 OR     ALL_IssueQTY > 0)
Group By (ALL_MaterialGrid + '_' + ALL_LowestPLC_SKU)
) TempTable


/* Stored Procedure 3 */
Select
SUM(PLC_Lower25) as PLC_Lower25, SUM(PLC_25) as PLC_25, SUM( PLC_30) as PLC_30,
SUM( PLC_40 ) as PLC_40, SUM(PLC_50_55) as PLC_50_55, SUM(PLC_60) as PLC_60,
SUM(PLC_70) as PLC_70, SUM(PLC_TOTAL) as PLC_Total
From
(
Select CASE WHEN ALL_PLC IN ('#', '00', '10', '20') THEN ALL_AvailableStock ELSE 0 END as PLC_Lower25,
       CASE WHEN ALL_PLC = ('25') THEN ALL_AvailableStock ELSE 0 END as PLC_25,
       CASE WHEN ALL_PLC = ('30') THEN ALL_AvailableStock ELSE 0 END as PLC_30,
       CASE WHEN ALL_PLC = '40' THEN ALL_AvailableStock ELSE 0 END as PLC_40,
       CASE WHEN ALL_PLC IN ( '50', '55') THEN ALL_AvailableStock ELSE 0 END as PLC_50_55,
       CASE WHEN ALL_PLC = '60' THEN ALL_AvailableStock ELSE 0 END as PLC_60,
       CASE WHEN ALL_PLC = '70' THEN ALL_AvailableStock ELSE 0 END as PLC_70,
       ALL_AvailableStock as PLC_total
 From [inventory].[dbo].[inventory.inv_test]
 Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
 And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
) TempTable


/* Stored Procedure 4 */
Select
SUM(PLC_Lower25) as PLC_Lower25, SUM(PLC_25) as PLC_25, SUM( PLC_30) as PLC_30,
SUM( PLC_40 ) as PLC_40, SUM(PLC_50_55) as PLC_50_55, SUM(PLC_60) as PLC_60,
SUM(PLC_70) as PLC_70, SUM(PLC_TOTAL) as PLC_Total
From
(
Select CASE WHEN ALL_PLC IN ('#', '00', '10', '20') THEN ALL_ClosingStockQTY ELSE 0 END as PLC_Lower25,
       CASE WHEN ALL_PLC = ('25') THEN ALL_ClosingStockQTY ELSE 0 END as PLC_25,
       CASE WHEN ALL_PLC = ('30') THEN ALL_ClosingStockQTY ELSE 0 END as PLC_30,
       CASE WHEN ALL_PLC = '40' THEN ALL_ClosingStockQTY ELSE 0 END as PLC_40,
       CASE WHEN ALL_PLC IN ( '50', '55') THEN ALL_ClosingStockQTY ELSE 0 END as PLC_50_55,
       CASE WHEN ALL_PLC = '60' THEN ALL_ClosingStockQTY ELSE 0 END as PLC_60,
       CASE WHEN ALL_PLC = '70' THEN ALL_ClosingStockQTY ELSE 0 END as PLC_70,
       ALL_ClosingStockQTY as PLC_total
 From [inventory].[dbo].[inventory.inv_test]
 Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
 And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
) TempTable


/* Stored Procedure 5 */
Select
CASE WHEN SUM(PIR_Lower25) = 0 THEN 0 ELSE (SUM(Stock_Lower25) / (SUM(PIR_Lower25)/77.4)) END as PLC_Lower25,
CASE WHEN SUM(PIR_25) = 0 THEN 0 ELSE (SUM(Stock_25) / (SUM(PIR_25)/77.4)) END as PLC_25,
CASE WHEN SUM(PIR_30) = 0 THEN 0 ELSE (SUM(Stock_30) / (SUM(PIR_30)/77.4)) END as PLC_30,
CASE WHEN SUM(PIR_40) = 0 THEN 0 ELSE (SUM(Stock_40) / (SUM(PIR_40)/77.4)) END as PLC_40,
CASE WHEN SUM(PIR_50_55) = 0 THEN 0 ELSE (SUM(Stock_50_55) / (SUM(PIR_50_55)/77.4)) END as PLC_50_55,
CASE WHEN SUM(PIR_60) = 0 THEN 0 ELSE (SUM(Stock_60) / (SUM(PIR_60)/77.4)) END as PLC_60,
CASE WHEN SUM(PIR_70) = 0 THEN 0 ELSE (SUM(Stock_70) / (SUM(PIR_70)/77.4)) END as PLC_70,
CASE WHEN SUM(PIR_Total) = 0 THEN 0 ELSE (SUM(Stock_Total) / (SUM(PIR_Total)/77.4)) END as PLC_Total
From
(
Select CASE WHEN ALL_PLC IN ('#', '00', '10', '20') THEN (ALL_PIR+ALL_PIR1+ALL_PIR2) ELSE 0 END as PIR_Lower25,
      CASE WHEN ALL_PLC IN ('#', '00', '10', '20') THEN ALL_AvailableStock ELSE 0 END as Stock_Lower25,      
      CASE WHEN ALL_PLC IN ('25') THEN (ALL_PIR+ALL_PIR1+ALL_PIR2) ELSE 0 END as PIR_25,
      CASE WHEN ALL_PLC IN ('25') THEN ALL_AvailableStock ELSE 0 END as Stock_25,      
      CASE WHEN ALL_PLC IN ('30') THEN (ALL_PIR+ALL_PIR1+ALL_PIR2) ELSE 0 END as PIR_30,
      CASE WHEN ALL_PLC IN ('30') THEN ALL_AvailableStock ELSE 0 END as Stock_30,      
      CASE WHEN ALL_PLC IN ('40') THEN (ALL_PIR+ALL_PIR1+ALL_PIR2) ELSE 0 END as PIR_40,
      CASE WHEN ALL_PLC IN ('40') THEN ALL_AvailableStock ELSE 0 END as Stock_40,      
      CASE WHEN ALL_PLC IN ('50', '55') THEN (ALL_PIR+ALL_PIR1+ALL_PIR2) ELSE 0 END as PIR_50_55,
      CASE WHEN ALL_PLC IN ('50', '55') THEN ALL_AvailableStock ELSE 0 END as Stock_50_55,      
      CASE WHEN ALL_PLC IN ('60') THEN (ALL_PIR+ALL_PIR1+ALL_PIR2) ELSE 0 END as PIR_60,
      CASE WHEN ALL_PLC IN ('60') THEN ALL_AvailableStock ELSE 0 END as Stock_60,      
      CASE WHEN ALL_PLC IN ('70') THEN (ALL_PIR+ALL_PIR1+ALL_PIR2) ELSE 0 END as PIR_70,
      CASE WHEN ALL_PLC IN ('70') THEN ALL_AvailableStock ELSE 0 END as Stock_70,      
       (ALL_PIR+ALL_PIR1+ALL_PIR2) as PIR_Total,
       ALL_AvailableStock as Stock_Total       
 From [inventory].[dbo].[inventory.inv_test]
 Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
 And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
) TempTable


/* Stored Procedure 6 */
Select
(SUM(ClosingStock_Lower25) / (SUM(IssueQTY_Lower25)/77.4)) as PLC_Lower25,
(SUM(ClosingStock_25) / (SUM(IssueQTY_25)/77.4)) as PLC_25,
(SUM(ClosingStock_30) / (SUM(IssueQTY_30)/77.4)) as PLC_30,
(SUM(ClosingStock_40) / (SUM(IssueQTY_40)/77.4)) as PLC_40,
(SUM(ClosingStock_50_55) / (SUM(IssueQTY_50_55)/77.4)) as PLC_50_55,
(SUM(ClosingStock_60) / (SUM(IssueQTY_60)/77.4)) as PLC_60,
(SUM(ClosingStock_70) / (SUM(IssueQTY_70)/77.4)) as PLC_70,
(SUM(ClosingStock_Total) / (SUM(IssueQTY_Total)/77.4)) as PLC_Total
From
(
Select CASE WHEN ALL_PLC IN ('#', '00', '10', '20') THEN ALL_ClosingStockQTY ELSE 0 END as ClosingStock_Lower25,
      CASE WHEN ALL_PLC IN ('#', '00', '10', '20') THEN ALL_IssueQTY ELSE 0 END as IssueQTY_Lower25,      
      CASE WHEN ALL_PLC IN ('25') THEN ALL_ClosingStockQTY ELSE 0 END as ClosingStock_25,
      CASE WHEN ALL_PLC IN ('25') THEN ALL_IssueQTY ELSE 0 END as IssueQTY_25,      
      CASE WHEN ALL_PLC IN ('30') THEN ALL_ClosingStockQTY ELSE 0 END as ClosingStock_30,
      CASE WHEN ALL_PLC IN ('30') THEN ALL_IssueQTY ELSE 0 END as IssueQTY_30,      
      CASE WHEN ALL_PLC IN ('40') THEN ALL_ClosingStockQTY ELSE 0 END as ClosingStock_40,
      CASE WHEN ALL_PLC IN ('40') THEN ALL_IssueQTY ELSE 0 END as IssueQTY_40,      
      CASE WHEN ALL_PLC IN ('50', '55') THEN ALL_ClosingStockQTY ELSE 0 END as ClosingStock_50_55,
      CASE WHEN ALL_PLC IN ('50', '55') THEN ALL_IssueQTY ELSE 0 END as IssueQTY_50_55,      
      CASE WHEN ALL_PLC IN ('60') THEN ALL_ClosingStockQTY ELSE 0 END as ClosingStock_60,
      CASE WHEN ALL_PLC IN ('60') THEN ALL_IssueQTY ELSE 0 END as IssueQTY_60,      
      CASE WHEN ALL_PLC IN ('70') THEN ALL_ClosingStockQTY ELSE 0 END as ClosingStock_70,
      CASE WHEN ALL_PLC IN ('70') THEN ALL_IssueQTY ELSE 0 END as IssueQTY_70,      
       ALL_ClosingStockQTY as ClosingStock_Total,
       ALL_IssueQTY as IssueQTY_Total       
 From [inventory].[dbo].[inventory.inv_test]
 Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';'))
 And ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
) TempTable

Anbei der Clustered Index:

SQL-Code:

Create Clustered Index inv_test_cluster
On [inventory].[dbo].[inventory.inv_test]
(all_brand, all_productline)