Laufzeit von Stored Procedure verkürzen

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

**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

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

**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

zusammenfalten · markieren

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

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

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

Laufzeit von Stored Procedure verkürzen

AW: Laufzeit von Stored Procedure verkürzen

AW: Laufzeit von Stored Procedure verkürzen

AW: Laufzeit von Stored Procedure verkürzen

AW: Laufzeit von Stored Procedure verkürzen

AW: Laufzeit von Stored Procedure verkürzen

AW: Laufzeit von Stored Procedure verkürzen

AW: Laufzeit von Stored Procedure verkürzen

AW: Laufzeit von Stored Procedure verkürzen

AW: Laufzeit von Stored Procedure verkürzen

AW: Laufzeit von Stored Procedure verkürzen

Forumregeln

Furtbichler (Gast) n/a Beiträge	#1 AW: Laufzeit von Stored Procedure verkürzen 4. Okt 2012, 15:17 Welcher PC?, RAM? HD? Wie sieht die Query bzw. SP aus? Verwendest Du einen Cursor? Hast Du dir mal den Query plan angeschaut?
	Zitat

Andidreas Registriert seit: 27. Okt 2005 1.110 Beiträge Delphi 10.1 Berlin Enterprise	#5 AW: Laufzeit von Stored Procedure verkürzen 4. Okt 2012, 15:32 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? Ein Programmierer Programmiert durchschnittlich 15 Code Zeilen pro Tag Wir sind hier doch nicht bei SAP!!! Aber wir habens bald
	Zitat

Furtbichler (Gast) n/a Beiträge	#8 AW: Laufzeit von Stored Procedure verkürzen 4. Okt 2012, 15:56 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.
	Zitat