Laufzeit von Stored Procedure verkürzen

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

[Andidreas]

Zitat von Furtbichler:

Jo, is normal das das so langsam ist

Bedingt durch die Konvertierung?

fnSplit Teilt Werte wie z.B. "Test1;Test2;Test3" so wieder auf das es in einer Where Bedingung mit IN verwendet werden kann...

[DeddyH]

Zitat von Andidreas:

fnSplit Teilt Werte wie z.B. "Test1;Test2;Test3" so wieder auf das es in einer Where Bedingung mit IN verwendet werden kann...

Dann entspricht die DB nicht einmal der 1. NF? Wenn man da erst atomare Daten herstellen und konvertieren muss, ist es kein Wunder, wenn das "ein wenig" dauert.

[p80286]

Das ist ja wahrhaftig grauslich.
Da hat wohl jemand eine ASCII-Datei in eine Datenbanktabelle gekippt und das dann als Datenbank verkauft.

Ich würde auch soweit gehen, alle benötigten Daten in "richtige" Tabellen zu übertragen, und natürlich die Brands und Productlines in Temptables hinterlegen.

erschütterte Grüße
K-H

[jobo]

Zitat von DeddyH:

Zitat von Andidreas:

fnSplit Teilt Werte wie z.B. "Test1;Test2;Test3" so wieder auf das es in einer Where Bedingung mit IN verwendet werden kann...

Dann entspricht die DB nicht einmal der 1. NF? Wenn man da erst atomare Daten herstellen und konvertieren muss, ist es kein Wunder, wenn das "ein wenig" dauert.

Ich glaub hier liegt ein Missverständnis vor. Vielleicht verwechsel ich das, aber vor einiger Zeit ging es hier genau um das Thema, "Mehrere Parameter an SP übergeben"

Was hier gesplittet wird, ist nicht ein DB Wert, sondern eine "Parameter Liste". Also nichts anderes als ein dynamischer Filter...

[Bummi]

Ich will ja nicht kritteln, aber die oft beklagte mangelhafte Lesbarkeit von SQL ist oft vermeidbar.

Warum arbeitest Du nicht mit mehreren Temptables für

[inventory].[dbo].[fnSplit](@Brand, ';')
[inventory].[dbo].[fnSplit](@Productline, ';')

die Zurechtgecasteten Auswahltabellen bereits eingeschränkt über o.g.

darüber dann die Kumulierungen laufen lassen ...

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