Laufzeit von Stored Procedure verkürzen

[Bummi]

Clustered bedeutet dass die Daten physikalisch dem Index entsprechend sortiert werden. Eine Ausführung des Indexoptimierers, nach dem Löschen eines solchen Indexes, kann diesen auf die Idee bringen dass ein weitere Index nicht benötigt würde.

[Andidreas]

Also Clustered Indexe hatten wir noch keine erstellt!

Wir haben aber gerade testweise welche erstellt, aber keine von den getesteten brachte eine Performance Verbesserung.

D.h. die Daten werden immernoch ohne Indexe schneller ermittelt und berrechnet als mit

[Blup]

Zitat:

Zitat von Blup:

Sind nur zwei getrennte Indexe (einer über ALL_BRAND und ein anderer über ALL_ProductLine) vorhanden, ist die Abfrage um ein vielfaches langsamer.

Gilt das auch wenn z.B. Index1 und Index2 jeweils die Felder ALL_Brand und ALL_Productline beinhalten?

Ja, wenn die Abfrage tatsächlich beide Indexe verwendet ist das langsam.
Dazu sieht man sich am besten den Ausführungsplan an.
Es ist nicht nur wichtig das die in der Bedingung verwendeten Felder im enthalten sind, diese sollten auch an erster Stelle im Index definiert sein.

Zitat:

D.h. die Daten werden immernoch ohne Indexe schneller ermittelt und berrechnet als mit

Dann sind entweder die Indexe für die Abfragen nicht geeignet oder die Abfragen ungünstig formuliert.
Bitte mal beides posten.

[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)

[Blup]

Jede deiner Abfragen verarbeitet alle Datensätze der gesamten Tabelle und benutzt dafür aufwendige Stringfunktionen.
So wie die Abfragen jetzt formuliert sind, kann der Index nicht genutzt werden.

In #42 und #49 sind meine Vorschläge, wie man die Abfragen übersichtlicher darstellen und für die Nutzung des Index optimieren kann.
SQL View

[jobo]

Zitat von Blup:

Jede deiner Abfragen verarbeitet alle Datensätze der gesamten Tabelle

sag ich doch

Zitat von jobo:

Die gesamte Abfrage sieht sowieso nach FullScan aus, Index hilft da nichts.