Laufzeit von Stored Procedure verkürzen

[Andidreas]

Hallo zusammen,

ich habe in meiner MS SQL DB eine Tabelle mit ca. 1 Millionen Datensätze. Die Tabelle ist Indiziert!

Für eine komplexe Abfrage habe ich nun eine Stored Procedure erstellt an die ich diverse Parameter übergeben kann.
In der Stored Procedure werden diverse Werte über die SUM und COUNT Funktion errechnet.
Wenn ich diese Stored Procedure ausführe, habe ich eine Abfragezeit von ca. 2 Minuten...

Da die Tabelle auf die ich Abfrage nur aus VARCHAR Feldern besteht, müssen für alle Dezimal Berrechnungen und Abfragen die VARCHAR Felder konvertiert werden, z.B.:

SUM(CONVERT(FLOAT, ALL_AvailableStock))

Kann dies die Ursache für die lange Laufzeit meiner Stored Procedure sein?

[Furtbichler]

Welcher PC?, RAM? HD?
Wie sieht die Query bzw. SP aus?
Verwendest Du einen Cursor?
Hast Du dir mal den Query plan angeschaut?

[Andidreas]

Zitat von Furtbichler:

Welcher PC?, RAM? HD?

Spielt das eine Rolle bei einer Stored Procedure?

Zitat von Furtbichler:

Wie sieht die Query bzw. SP aus?

Was ist mit SP gemeint?

Zitat von Furtbichler:

Verwendest Du einen Cursor?
Hast Du dir mal den Query plan angeschaut?

Wie kann ich kontrollieren ob ich einen Cursor verwende?

Sorry ich versteh grad nur Bahnhof...

[p80286]

Habe ich das richtig gelesen?
Numerische Werte in VARCHAR-Feldern??????

Gruß
K-H

SP=Stored Procedure (vgl. Titel)
cursor=die Ergebnismenge einer Abfrage in einer SP, die in der SP weiter verarbeitet wird (ua)

[Bernhard Geyer]

Zitat von Andidreas:

Zitat von Furtbichler:

Welcher PC?, RAM? HD?

Spielt das eine Rolle bei einer Stored Procedure?

Freilich. Oder glaubst du ein SQL Server lebt nur von guten willen alleine
Es sind naturlich die Daten des Servers auf dem das DBMS läuft gefragt

Zitat von Andidreas:

Was ist mit SP gemeint?

SP = Store Procedure

[Andidreas]

Zitat von p80286:

Habe ich das richtig gelesen?
Numerische Werte in VARCHAR-Feldern??????

Gruß
K-H

Ja hast Du... Ist unglücklich so entstanden...
Ist das mein Problem?

[p80286]

Zitat von Andidreas:

Zitat von p80286:

Habe ich das richtig gelesen?
Numerische Werte in VARCHAR-Feldern??????

Gruß
K-H

Ja hast Du... Ist unglücklich so entstanden...
Ist das mein Problem?

Augenscheinlich JA!

Gruß
K-H

[Andidreas]

Also die Performance vom Server ist eher weniger das Problem...

Anbei die Stored Procedure

SQL-Code:

USE [inventory]
GO

CREATE PROCEDURE prInventoryManagement_Overview @Brand varchar(255), @Productline varchar(255)
AS

Select
'Warehouse' as Storage,
'SKUs' as Typ,
(Select (Count(Distinct(ALL_MaterialGrid))) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And   ALL_LowestPLC_SKU IN  ('#', '00', '10', '20') 
AND   (CONVERT(Float, REPLACE(ALL_AvailableStock, ',', '.')) > 0
OR     CONVERT(Float, REPLACE(ALL_Pir,  ',', '.'))  > 0
OR     CONVERT(Float, REPLACE(ALL_Pir1, ',', '.'))  > 0
OR     CONVERT(Float, REPLACE(ALL_Pir2, ',', '.'))  > 0
OR     CONVERT(Float, REPLACE(ALL_Pir3, ',', '.'))  > 0)) as "PLC_<25",
(Select (Count(Distinct(ALL_MaterialGrid))) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And   ALL_LowestPLC_SKU IN  ('25') 
AND   (CONVERT(Float, REPLACE(ALL_AvailableStock, ',', '.')) > 0
OR     CONVERT(Float, REPLACE(ALL_Pir,  ',', '.'))  > 0
OR     CONVERT(Float, REPLACE(ALL_Pir1, ',', '.'))  > 0
OR     CONVERT(Float, REPLACE(ALL_Pir2, ',', '.'))  > 0
OR     CONVERT(Float, REPLACE(ALL_Pir3, ',', '.'))  > 0)) as "PLC_25",
(Select (Count(Distinct(ALL_MaterialGrid))) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And   ALL_LowestPLC_SKU IN  ('30') 
AND   (CONVERT(Float, REPLACE(ALL_AvailableStock, ',', '.')) > 0
OR     CONVERT(Float, REPLACE(ALL_Pir,  ',', '.'))  > 0
OR     CONVERT(Float, REPLACE(ALL_Pir1, ',', '.'))  > 0
OR     CONVERT(Float, REPLACE(ALL_Pir2, ',', '.'))  > 0
OR     CONVERT(Float, REPLACE(ALL_Pir3, ',', '.'))  > 0)) as "PLC_30",
(Select (Count(Distinct(ALL_MaterialGrid))) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And   ALL_LowestPLC_SKU IN  ('40') 
AND   (CONVERT(Float, REPLACE(ALL_AvailableStock, ',', '.')) > 0
OR     CONVERT(Float, REPLACE(ALL_Pir,  ',', '.'))  > 0
OR     CONVERT(Float, REPLACE(ALL_Pir1, ',', '.'))  > 0
OR     CONVERT(Float, REPLACE(ALL_Pir2, ',', '.'))  > 0
OR     CONVERT(Float, REPLACE(ALL_Pir3, ',', '.'))  > 0)) as "PLC_40",
(Select (Count(Distinct(ALL_MaterialGrid))) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And   ALL_LowestPLC_SKU IN  ('50', '55') 
AND   (CONVERT(Float, REPLACE(ALL_AvailableStock, ',', '.')) > 0
OR     CONVERT(Float, REPLACE(ALL_Pir,  ',', '.'))  > 0
OR     CONVERT(Float, REPLACE(ALL_Pir1, ',', '.'))  > 0
OR     CONVERT(Float, REPLACE(ALL_Pir2, ',', '.'))  > 0
OR     CONVERT(Float, REPLACE(ALL_Pir3, ',', '.'))  > 0)) as "PLC_50_55",
(Select (Count(Distinct(ALL_MaterialGrid))) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And   ALL_LowestPLC_SKU IN  ('60') 
AND   (CONVERT(Float, REPLACE(ALL_AvailableStock, ',', '.')) > 0
OR     CONVERT(Float, REPLACE(ALL_Pir,  ',', '.'))  > 0
OR     CONVERT(Float, REPLACE(ALL_Pir1, ',', '.'))  > 0
OR     CONVERT(Float, REPLACE(ALL_Pir2, ',', '.'))  > 0
OR     CONVERT(Float, REPLACE(ALL_Pir3, ',', '.'))  > 0)) as "PLC_60",
(Select (Count(Distinct(ALL_MaterialGrid))) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And   ALL_LowestPLC_SKU IN  ('70') 
AND   (CONVERT(Float, REPLACE(ALL_AvailableStock, ',', '.')) > 0
OR     CONVERT(Float, REPLACE(ALL_Pir,  ',', '.'))  > 0
OR     CONVERT(Float, REPLACE(ALL_Pir1, ',', '.'))  > 0
OR     CONVERT(Float, REPLACE(ALL_Pir2, ',', '.'))  > 0
OR     CONVERT(Float, REPLACE(ALL_Pir3, ',', '.'))  > 0)) as "PLC_70",
(Select (Count(Distinct(ALL_MaterialGrid))) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
AND   (CONVERT(Float, REPLACE(ALL_AvailableStock, ',', '.')) > 0
OR     CONVERT(Float, REPLACE(ALL_Pir,  ',', '.'))  > 0
OR     CONVERT(Float, REPLACE(ALL_Pir1, ',', '.'))  > 0
OR     CONVERT(Float, REPLACE(ALL_Pir2, ',', '.'))  > 0
OR     CONVERT(Float, REPLACE(ALL_Pir3, ',', '.'))  > 0)) as "PLC_Total"


UNION


Select
'Consi' as Storage,
'SKUs' as Typ,
(Select (Count(Distinct(ALL_MaterialGrid))) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And   ALL_LowestPLC_SKU IN  ('#', '00', '10', '20') 
AND   (CONVERT(Float, REPLACE(ALL_ClosingStockQTY, ',', '.')) > 0
OR     CONVERT(Float, REPLACE(ALL_IssueQTY, ',', '.'))  > 0)) as "PLC_<25",
(Select (Count(Distinct(ALL_MaterialGrid))) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And   ALL_LowestPLC_SKU IN  ('25') 
AND   (CONVERT(Float, REPLACE(ALL_ClosingStockQTY, ',', '.')) > 0
OR     CONVERT(Float, REPLACE(ALL_IssueQTY, ',', '.'))  > 0)) as "PLC_25",
(Select (Count(Distinct(ALL_MaterialGrid))) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And   ALL_LowestPLC_SKU IN  ('30') 
AND   (CONVERT(Float, REPLACE(ALL_ClosingStockQTY, ',', '.')) > 0
OR     CONVERT(Float, REPLACE(ALL_IssueQTY, ',', '.'))  > 0)) as "PLC_30",
(Select (Count(Distinct(ALL_MaterialGrid))) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And   ALL_LowestPLC_SKU IN  ('40') 
AND   (CONVERT(Float, REPLACE(ALL_ClosingStockQTY, ',', '.')) > 0
OR     CONVERT(Float, REPLACE(ALL_IssueQTY, ',', '.'))  > 0)) as "PLC_40",
(Select (Count(Distinct(ALL_MaterialGrid))) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And   ALL_LowestPLC_SKU IN  ('50', '55') 
AND   (CONVERT(Float, REPLACE(ALL_ClosingStockQTY, ',', '.')) > 0
OR     CONVERT(Float, REPLACE(ALL_IssueQTY, ',', '.'))  > 0)) as "PLC_50_55",
(Select (Count(Distinct(ALL_MaterialGrid))) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And   ALL_LowestPLC_SKU IN  ('60') 
AND   (CONVERT(Float, REPLACE(ALL_ClosingStockQTY, ',', '.')) > 0
OR     CONVERT(Float, REPLACE(ALL_IssueQTY, ',', '.'))  > 0)) as "PLC_60",
(Select (Count(Distinct(ALL_MaterialGrid))) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And   ALL_LowestPLC_SKU IN  ('70') 
AND   (CONVERT(Float, REPLACE(ALL_ClosingStockQTY, ',', '.')) > 0
OR     CONVERT(Float, REPLACE(ALL_IssueQTY, ',', '.'))  > 0)) as "PLC_70",
(Select (Count(Distinct(ALL_MaterialGrid))) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
AND   (CONVERT(Float, REPLACE(ALL_ClosingStockQTY, ',', '.')) > 0
OR     CONVERT(Float, REPLACE(ALL_IssueQTY, ',', '.'))  > 0)) as "PLC_Total"


UNION


Select
'Warehouse' as Storage,
'Pieces' as Typ,
(Select SUM(CONVERT(FLOAT, ALL_AvailableStock)) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_PLC In ('#', '00', '10', '20')
) as PLC_Lower25,
(Select SUM(CONVERT(FLOAT, ALL_AvailableStock)) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_PLC In ('25')
) as PLC_25,
(Select SUM(CONVERT(FLOAT, ALL_AvailableStock)) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_PLC In ('30')
) as PLC_30,
(Select SUM(CONVERT(FLOAT, ALL_AvailableStock)) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_PLC In ('40')
) as PLC_40,
(Select SUM(CONVERT(FLOAT, ALL_AvailableStock)) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_PLC In ('50', '55')
) as PLC_50_55,
(Select SUM(CONVERT(FLOAT, ALL_AvailableStock)) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_PLC In ('60')
) as PLC_60,
(Select SUM(CONVERT(FLOAT, ALL_AvailableStock)) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_PLC In ('70')
) as PLC_70,
(Select SUM(CONVERT(FLOAT, ALL_AvailableStock)) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
) as PLC_Total


UNION


Select
'Consi' as Storage,
'Pieces' as Typ,
(Select SUM(CONVERT(FLOAT, ALL_ClosingStockQTY)) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_PLC In ('#', '00', '10', '20')
) as PLC_Lower25,
(Select SUM(CONVERT(FLOAT, ALL_ClosingStockQTY)) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_PLC In ('25')
) as PLC_25,
(Select SUM(CONVERT(FLOAT, ALL_ClosingStockQTY)) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_PLC In ('30')
) as PLC_30,
(Select SUM(CONVERT(FLOAT, ALL_ClosingStockQTY)) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_PLC In ('40')
) as PLC_40,
(Select SUM(CONVERT(FLOAT, ALL_ClosingStockQTY)) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_PLC In ('50', '55')
) as PLC_50_55,
(Select SUM(CONVERT(FLOAT, ALL_ClosingStockQTY)) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_PLC In ('60')
) as PLC_60,
(Select SUM(CONVERT(FLOAT, ALL_ClosingStockQTY)) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And ALL_PLC In ('70')
) as PLC_70,
(Select SUM(CONVERT(FLOAT, ALL_ClosingStockQTY)) From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
) as PLC_Total


UNION


Select
'Warehouse' as Storage,
'DoH' as Typ,
(Select
CASE WHEN (SUM(CONVERT(FLOAT, ALL_PIR))+SUM(CONVERT(FLOAT, ALL_PIR1))+SUM(CONVERT(FLOAT, ALL_PIR2))) = 0 THEN 0 ELSE
((SUM(CONVERT(FLOAT, ALL_AvailableStock))) / ((SUM(CONVERT(FLOAT, ALL_PIR))+SUM(CONVERT(FLOAT, ALL_PIR1))+SUM(CONVERT(FLOAT, ALL_PIR2)))/77.4))
END
From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And   ALL_PLC In ('#', '00', '10', '20')
) as PLC_Lower25,
(Select
CASE WHEN (SUM(CONVERT(FLOAT, ALL_PIR))+SUM(CONVERT(FLOAT, ALL_PIR1))+SUM(CONVERT(FLOAT, ALL_PIR2))) = 0 THEN 0 ELSE
((SUM(CONVERT(FLOAT, ALL_AvailableStock))) / ((SUM(CONVERT(FLOAT, ALL_PIR))+SUM(CONVERT(FLOAT, ALL_PIR1))+SUM(CONVERT(FLOAT, ALL_PIR2)))/77.4))
END
From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And   ALL_PLC In ('25')
) as PLC_25,
(Select
CASE WHEN (SUM(CONVERT(FLOAT, ALL_PIR))+SUM(CONVERT(FLOAT, ALL_PIR1))+SUM(CONVERT(FLOAT, ALL_PIR2))) = 0 THEN 0 ELSE
((SUM(CONVERT(FLOAT, ALL_AvailableStock))) / ((SUM(CONVERT(FLOAT, ALL_PIR))+SUM(CONVERT(FLOAT, ALL_PIR1))+SUM(CONVERT(FLOAT, ALL_PIR2)))/77.4))
END
From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And   ALL_PLC In ('30')
) as PLC_30,
(Select
CASE WHEN (SUM(CONVERT(FLOAT, ALL_PIR))+SUM(CONVERT(FLOAT, ALL_PIR1))+SUM(CONVERT(FLOAT, ALL_PIR2))) = 0 THEN 0 ELSE
((SUM(CONVERT(FLOAT, ALL_AvailableStock))) / ((SUM(CONVERT(FLOAT, ALL_PIR))+SUM(CONVERT(FLOAT, ALL_PIR1))+SUM(CONVERT(FLOAT, ALL_PIR2)))/77.4))
END
From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And   ALL_PLC In ('40')
) as PLC_40,
(Select
CASE WHEN (SUM(CONVERT(FLOAT, ALL_PIR))+SUM(CONVERT(FLOAT, ALL_PIR1))+SUM(CONVERT(FLOAT, ALL_PIR2))) = 0 THEN 0 ELSE
((SUM(CONVERT(FLOAT, ALL_AvailableStock))) / ((SUM(CONVERT(FLOAT, ALL_PIR))+SUM(CONVERT(FLOAT, ALL_PIR1))+SUM(CONVERT(FLOAT, ALL_PIR2)))/77.4))
END
From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And   ALL_PLC In ('50', '55')
) as PLC_50_55,
(Select
CASE WHEN (SUM(CONVERT(FLOAT, ALL_PIR))+SUM(CONVERT(FLOAT, ALL_PIR1))+SUM(CONVERT(FLOAT, ALL_PIR2))) = 0 THEN 0 ELSE
((SUM(CONVERT(FLOAT, ALL_AvailableStock))) / ((SUM(CONVERT(FLOAT, ALL_PIR))+SUM(CONVERT(FLOAT, ALL_PIR1))+SUM(CONVERT(FLOAT, ALL_PIR2)))/77.4))
END
From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And   ALL_PLC In ('60')
) as PLC_60,
(Select
CASE WHEN (SUM(CONVERT(FLOAT, ALL_PIR))+SUM(CONVERT(FLOAT, ALL_PIR1))+SUM(CONVERT(FLOAT, ALL_PIR2))) = 0 THEN 0 ELSE
((SUM(CONVERT(FLOAT, ALL_AvailableStock))) / ((SUM(CONVERT(FLOAT, ALL_PIR))+SUM(CONVERT(FLOAT, ALL_PIR1))+SUM(CONVERT(FLOAT, ALL_PIR2)))/77.4))
END
From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And   ALL_PLC In ('70')
) as PLC_70,
(Select
CASE WHEN (SUM(CONVERT(FLOAT, ALL_PIR))+SUM(CONVERT(FLOAT, ALL_PIR1))+SUM(CONVERT(FLOAT, ALL_PIR2))) = 0 THEN 0 ELSE
((SUM(CONVERT(FLOAT, ALL_AvailableStock))) / ((SUM(CONVERT(FLOAT, ALL_PIR))+SUM(CONVERT(FLOAT, ALL_PIR1))+SUM(CONVERT(FLOAT, ALL_PIR2)))/77.4))
END
From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
) as PLC_Total


UNION


Select
'Consi' as Storage,
'DoH' as Typ,
(Select ((SUM(CONVERT(FLOAT, ALL_ClosingStockQTY)))/(SUM(CONVERT(FLOAT, ALL_IssueQTY))/77.4))
From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And   ALL_PLC In ('#', '00', '10', '20')
) as PLC_Lower25,
(Select ((SUM(CONVERT(FLOAT, ALL_ClosingStockQTY)))/(SUM(CONVERT(FLOAT, ALL_IssueQTY))/77.4))
From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And   ALL_PLC In ('25')
) as PLC_25,
(Select ((SUM(CONVERT(FLOAT, ALL_ClosingStockQTY)))/(SUM(CONVERT(FLOAT, ALL_IssueQTY))/77.4))
From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And   ALL_PLC In ('30')
) as PLC_30,
(Select ((SUM(CONVERT(FLOAT, ALL_ClosingStockQTY)))/(SUM(CONVERT(FLOAT, ALL_IssueQTY))/77.4))
From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And   ALL_PLC In ('40')
) as PLC_40,
(Select ((SUM(CONVERT(FLOAT, ALL_ClosingStockQTY)))/(SUM(CONVERT(FLOAT, ALL_IssueQTY))/77.4))
From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And   ALL_PLC In ('50', '55')
) as PLC_50_55,
(Select ((SUM(CONVERT(FLOAT, ALL_ClosingStockQTY)))/(SUM(CONVERT(FLOAT, ALL_IssueQTY))/77.4))
From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And   ALL_PLC In ('60')
) as PLC_60,
(Select ((SUM(CONVERT(FLOAT, ALL_ClosingStockQTY)))/(SUM(CONVERT(FLOAT, ALL_IssueQTY))/77.4))
From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
And   ALL_PLC In ('70')
) as PLC_70,
(Select ((SUM(CONVERT(FLOAT, ALL_ClosingStockQTY)))/(SUM(CONVERT(FLOAT, ALL_IssueQTY))/77.4))
From [inventory].[dbo].[inventory.inv_all]
Where ALL_Brand In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Brand, ';')) 
And   ALL_ProductLine In (SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';'))
) as PLC_Total

[Furtbichler]

Jo, is normal das das so langsam ist.
Wie sieht fn_split aus?

Aber ich persönlich würde 1-2 Tagessätze ansetzen um das 1.zu verstehen und 2.zu verbessern.

[Uwe Raabe]

Überleg mal, wieviele eigentlich überflüssige Convert- und Replace-Aufrufe da bei der Menge an Datensätzen stattfinden. Du kannst das ja mal mit einer Kopie der Datenbank probieren, bei der du die Felder in ihre numerische Form konvertierst und die SP dann entsprechend vereinfachst. Der Zeitunterschied sollte relativ einfach überprüfbar sein.