Datenbank: MSSQL • Version: 2008 • Zugriff über: Excel / UniDAC
Laufzeit von Stored Procedure verkürzen
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.:
SQL-Code:
SUM(CONVERT(FLOAT, ALL_AvailableStock))
Kann dies die Ursache für die lange Laufzeit meiner Stored Procedure sein? |
AW: 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? |
AW: Laufzeit von Stored Procedure verkürzen
Zitat:
Zitat:
Zitat:
Sorry ich versteh grad nur Bahnhof... |
AW: Laufzeit von Stored Procedure verkürzen
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) |
AW: Laufzeit von Stored Procedure verkürzen
Zitat:
Es sind naturlich die Daten des Servers auf dem das DBMS läuft gefragt Zitat:
|
AW: Laufzeit von Stored Procedure verkürzen
Zitat:
Ist das mein Problem? |
AW: Laufzeit von Stored Procedure verkürzen
Zitat:
Gruß K-H |
AW: Laufzeit von Stored Procedure verkürzen
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 |
AW: Laufzeit von Stored Procedure verkürzen
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. |
AW: Laufzeit von Stored Procedure verkürzen
Ü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.
|
AW: Laufzeit von Stored Procedure verkürzen
Zitat:
fnSplit Teilt Werte wie z.B. "Test1;Test2;Test3" so wieder auf das es in einer Where Bedingung mit IN verwendet werden kann... |
AW: Laufzeit von Stored Procedure verkürzen
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 ... |
AW: Laufzeit von Stored Procedure verkürzen
Zitat:
|
AW: Laufzeit von Stored Procedure verkürzen
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 |
Ich persönlich glaube nicht, das das Convert hier die Bremse ist, sondern die mehrfache Verwendung von Sub-Sub-Sub-selects.
Natürlich trägt CONVERT dazu bei, das das Ganze als legitimer Exekutionsgrund vor jedem Richter durchgeht. Ich bin mal so frei: Der, der das verzapft hat, ist ein Cretin. Ein ausgemachter Vollpfosten. Außer Du warst das, andidreas. Dann: Schäm dich und frag nächstes Mal. |
AW: Laufzeit von Stored Procedure verkürzen
Zitat:
Was hier gesplittet wird, ist nicht ein DB Wert, sondern eine "Parameter Liste". Also nichts anderes als ein dynamischer Filter... |
AW: Laufzeit von Stored Procedure verkürzen
Achso, ich hatte das so verstanden, dass es sich um Daten eines einzelnen Feldes handelt. Mea culpa.
|
AW: Laufzeit von Stored Procedure verkürzen
Zitat:
Ich halte eher die Subselect für harmlos. Die n "Unions" bedeuten schon mal n +1 scans der gesamten Daten, die "Or" machen es nicht besser. |
AW: Laufzeit von Stored Procedure verkürzen
Zitat:
Zitat:
Zitat:
Zum besseren Verständnis mal noch die folgenden Infos... Das es sich um eine reine ASCII Tabelle handelt und das dass nicht optimal ist weiß ich auch... Bei der kompletten Datenbank handelt es sich um eine Quick & Dirty Lösung bei der keiner daran dachte das sie länger verwendet wird bzw. soll. Naja mittlerweile wissen wir das wir damit noch länger arbeiten müssen und dürfen uns auch überlegen wie wirs besser machen... Wie ich bereits erwähnt hab befinden sich in der Tabelle im Moment ca. 1 Mio. Datensätze... Monatlich wird ca. die gleiche Menge hinzukommen sodass nach einem Jahr Maximal 12 Mio. Datensätze sich in der Tabelle befinden... So und nun zu meiner Aufgabe (und ich sags gleich, ich bin kein DB Spezialist!)... Wir haben meherer Reports in Excel die auf diese Tabelle zugreifen und das soll performanter werden... In einem Report werden für diverse Lagerkennzahlen Summen auf PLC (Product Life Cycle) Statusen errechnet... Da ich die Informationen nur Satz für Satz in der DB stehen hab ist mir nichts besseres eingefallen wie je PLC über Subselects die Werte zu ermitteln... Hat hier jemand Verbesserungsvorschläge? Die Stored Procedure mit den Unions war nur ein Test um den Source im VBA (Excel) übersichtlicher zu halten, also das ich dort nur eine Stored Procedure aufrufen muss anstatt sechs! |
AW: Laufzeit von Stored Procedure verkürzen
Ich gehe mal davon aus, dass die Tabelle für die Auswertung nur gelesen werden soll.
Datensätze werden einmal eingetragen und dann nicht mehr verändert. Dann würde es sich für die Übergangszeit empfehlen auf diese Tabelle einen Insert-Trigger zu legen, der die Daten beim Einfügen in eine bessere Tabelle überführt. Das Einfügen von Daten wird sich minimal verändern, aber die Abfragen werden erheblich schneller (werden können). Somit werden beide Abfragewege noch funktionieren und du kannst in Ruhe alle Zugriffe auf die neue Tabelle umstellen. Wenn das erledigt ist, dann den Import der Daten anfassen. |
AW: Laufzeit von Stored Procedure verkürzen
Zitat:
Meinst Du also ich soll mir eine 2. Tabelle machen in der ich die korrekten Datentypen verwende und diese dann mit Insert Trigger befülle? |
AW: Laufzeit von Stored Procedure verkürzen
Ich hab die Stored Procedure mal aufgteilt und zwar so, dass ich die Selects nicht mehr über Unions verbinde, sondern für jeden Select eine eigene Stored Procedure hab... das würde die Laufzeit schon mal auf 1:02 min. reduzieren...
Heißt wohl das die Unions ein nicht unerheblicher Zeitfresser sind... |
AW: Laufzeit von Stored Procedure verkürzen
mit jedem union werden Deine Mio Datensätze erneut abgefragt.
Weiß nicht genau wie es unter MS ist, aber ein "Union" prüft implizit auf Distincte Ergenisse - also werden alle Teilergebniss noch einmal untereinander verglichen. Ein "Union All" macht das nicht und ist vorzuziehen, wenn man sicherstellen kann, dass sich die Ergebnisse nicht überschneiden. Wenn die Einzelergebnisse jeweils große Mengen sind, könnte das also auch was bringen. |
AW: Laufzeit von Stored Procedure verkürzen
Ne im Prinzip erhalte ich durch die Stored Procedure als Ergebniss 6 Datensätze...
Eine Stored Procedure die alles abdeckt wäre halt "eleganter" gewesen wenn ich an meinen VBA Code denke, aber wenn ich durch einen getrennten Aufruf der Stored Procedures schneller die Ergebnisse erhalte dann muss ich halt diesen weg gehn... Union ALL wäre noch eine Alternative die ich mal Testen könnte... |
AW: Laufzeit von Stored Procedure verkürzen
Die erwähnten Temptables wären auch eine Möglichkeit ...
|
AW: Laufzeit von Stored Procedure verkürzen
Zitat:
Alleine dadurch solltestt Du schon einiges gewinnen. Gruß K-H |
AW: Laufzeit von Stored Procedure verkürzen
Zitat:
|
AW: Laufzeit von Stored Procedure verkürzen
Zitat:
Zitat:
|
AW: Laufzeit von Stored Procedure verkürzen
Zitat:
|
AW: Laufzeit von Stored Procedure verkürzen
nur schematisch ....
Code:
Create Table #pl
( Value Varchar(20) ) insert into #pl SELECT Value FROM [inventory].[dbo].[fnSplit](@Productline, ';') .... Select ALL_MaterialGrid,ALL_Brand,ALL_ProductLine,ALL_LowestPLC_SKU ,(CONVERT(Float, REPLACE(ALL_AvailableStock, ',', '.')) as ALL_AvailableStock .... .... ... into #tmp1 from inventory.inv_all Where ALL_ProductLine in (Select * from #pl) and .... Ceate Table #Result ( Storage varchar(30), Typ varchar(20), [PLC_<25] int, ........ ) insert into #Result Select 'Warehouse' as Storage, 'SKUs' as Typ, (Select Count(*) from #tmp1 where ... ... ... and ALL_AvailableStock > 0) as [PLC_<25], ...... insert into #Result ...... --final: Select * from #Result |
AW: Laufzeit von Stored Procedure verkürzen
Ich verstehs nicht ganz...
Würdest Du jeden ermittelt Wert in eie TempTable schreiben um Performance zu sparen, oder würdest du jeden Select in die TempTable schreiben um die Unions zu vermeiden? |
AW: Laufzeit von Stored Procedure verkürzen
ich würde
-die Results der Prozeduren in Temptabellen übernehmen ([fnSplit](@Brand und [fnSplit](@Productline) -die gecasteten Felder, nur über die bis dahin bestehenden Temptabellen (ALL_Brand , ALL_ProductLine )gefiltert, in Temptabellen übernehmen diese 6 Tabellen (#tmp1-6) würde ich für diese ALL_Pir(x)>0 etc und ALL_LowestPLC_SKU in in die Resultabelle kumulieren |
AW: Laufzeit von Stored Procedure verkürzen
Ich habe den ersten Teil Deiner Unionabfrage umgebaut, so dass schätzungsweise ein Table Scan reicht.
Ist in Oracle gemacht, dürfte aber keine große Rolle spielen. Die Feldtypen habe ich natürlich "optimiert". Statt der Split Funktion habe ich aus Bequemlichkeit eine normale Tabelle verwendet, geht ja nur um ein Beispiel. Die Tabelle hat keine Indizes oder Constraints, knapp 1 Mio Datensätze. Als Test habe ich noch eine Datumsspalte im 2. Select verwendet. Wirst Du wahrscheinlich auch haben / brauchen.
Code:
10:01:02 SQL> select * from fnsplit;
BRAND PRODUCTLINE ---------- ----------- Mercedes Carrera Mercedes B-Klasse Porsche Cayenne Porsche Boxter Porsche Carrera Executed in 0,125 seconds 10:01:19 SQL> 10:01:46 SQL> Select 'Warehouse' as Storage, 'SKUs' as Typ, 2 sum(PLCMIN25), sum(PLC_25), sum( PLC_30), sum( PLC_40 ), sum(PLC_50_55), 3 sum(PLC_60), sum(PLC_70), sum(PLC_TOTAL) from ( 4 select 5 case when ALL_LowestPLC_SKU IN ('#', '00', '10', '20') 6 then 1 else 0 end as PLCmin25, 7 case when ALL_LowestPLC_SKU = ('25') 8 then 1 else 0 end as PLC_25, 9 case when ALL_LowestPLC_SKU = ('30') 10 then 1 else 0 end as PLC_30, 11 case when ALL_LowestPLC_SKU = '40' 12 then 1 else 0 end as PLC_40, 13 case when ALL_LowestPLC_SKU IN ( '50', '55') 14 then 1 else 0 end as PLC_50_55, 15 case when ALL_LowestPLC_SKU = '60' 16 then 1 else 0 end as PLC_60, 17 case when ALL_LowestPLC_SKU = '70' 18 then 1 else 0 end as PLC_70, 19 1 as PLC_total, 20 a.all_date 21 From inv_all a 22 Where ALL_Brand In (SELECT Brand FROM fnSplit) 23 And ALL_ProductLine In (SELECT Productline FROM fnSplit) 24 AND (ALL_AvailableStock > 0 OR 25 ALL_Pir > 0 OR 26 ALL_Pir1 > 0 OR 27 ALL_Pir2 > 0 OR 28 ALL_Pir3 > 0))x 10:01:46 SQL> / STORAGE TYP SUM(PLCMIN25) SUM(PLC_25) SUM(PLC_30) SUM(PLC_40) SUM(PLC_50_55) SUM(PLC_60) SUM(PLC_70) SUM(PLC_TOTAL) --------- ---- ------------- ----------- ----------- ----------- -------------- ----------- ----------- -------------- Warehouse SKUs 231 282634 183282 98890 98846 183942 110 999889 Executed in 1,155 seconds 10:01:50 SQL> delete from fnsplit s where s.brand ='Mercedes'; 2 rows deleted Executed in 0,047 seconds 10:03:05 SQL> 10:03:22 SQL> Select 'Warehouse' as Storage, 'SKUs' as Typ, 2 sum(PLCMIN25), sum(PLC_25), sum( PLC_30), sum( PLC_40 ), sum(PLC_50_55), 3 sum(PLC_60), sum(PLC_70), sum(PLC_TOTAL) from ( 4 select 5 case when ALL_LowestPLC_SKU IN ('#', '00', '10', '20') 6 then 1 else 0 end as PLCmin25, 7 case when ALL_LowestPLC_SKU = ('25') 8 then 1 else 0 end as PLC_25, 9 case when ALL_LowestPLC_SKU = ('30') 10 then 1 else 0 end as PLC_30, 11 case when ALL_LowestPLC_SKU = '40' 12 then 1 else 0 end as PLC_40, 13 case when ALL_LowestPLC_SKU IN ( '50', '55') 14 then 1 else 0 end as PLC_50_55, 15 case when ALL_LowestPLC_SKU = '60' 16 then 1 else 0 end as PLC_60, 17 case when ALL_LowestPLC_SKU = '70' 18 then 1 else 0 end as PLC_70, 19 1 as PLC_total, 20 a.all_date 21 From inv_all a 22 Where ALL_Brand In (SELECT Brand FROM fnSplit) 23 And ALL_ProductLine In (SELECT Productline FROM fnSplit) 24 AND (ALL_AvailableStock > 0 OR 25 ALL_Pir > 0 OR 26 ALL_Pir1 > 0 OR 27 ALL_Pir2 > 0 OR 28 ALL_Pir3 > 0))x 10:03:22 SQL> / STORAGE TYP SUM(PLCMIN25) SUM(PLC_25) SUM(PLC_30) SUM(PLC_40) SUM(PLC_50_55) SUM(PLC_60) SUM(PLC_70) SUM(PLC_TOTAL) --------- ---- ------------- ----------- ----------- ----------- -------------- ----------- ----------- -------------- Warehouse SKUs 231 199650 127974 71214 71170 128590 110 709379 Executed in 1,466 seconds 10:03:26 SQL> 10:04:05 SQL> Select 'Warehouse' as Storage, 'SKUs' as Typ, to_char(all_date, 'YYMM'), 2 sum(PLCMIN25), sum(PLC_25), sum( PLC_30), sum( PLC_40 ), sum(PLC_50_55), 3 sum(PLC_60), sum(PLC_70), sum(PLC_TOTAL) from ( 4 select 5 case when ALL_LowestPLC_SKU IN ('#', '00', '10', '20') 6 then 1 else 0 end as PLCmin25, 7 case when ALL_LowestPLC_SKU = ('25') 8 then 1 else 0 end as PLC_25, 9 case when ALL_LowestPLC_SKU = ('30') 10 then 1 else 0 end as PLC_30, 11 case when ALL_LowestPLC_SKU = '40' 12 then 1 else 0 end as PLC_40, 13 case when ALL_LowestPLC_SKU IN ( '50', '55') 14 then 1 else 0 end as PLC_50_55, 15 case when ALL_LowestPLC_SKU = '60' 16 then 1 else 0 end as PLC_60, 17 case when ALL_LowestPLC_SKU = '70' 18 then 1 else 0 end as PLC_70, 19 1 as PLC_total, 20 a.all_date 21 From inv_all a 22 Where ALL_Brand In (SELECT Brand FROM fnSplit) 23 And ALL_ProductLine In (SELECT Productline FROM fnSplit) 24 AND (ALL_AvailableStock > 0 OR 25 ALL_Pir > 0 OR 26 ALL_Pir1 > 0 OR 27 ALL_Pir2> 0 OR 28 ALL_Pir3> 0) 29 )x 30 group by to_char(all_date, 'YYMM') 31 / STORAGE TYP TO_CHAR(ALL_DATE,'YYMM') SUM(PLCMIN25) SUM(PLC_25) SUM(PLC_30) SUM(PLC_40) SUM(PLC_50_55) SUM(PLC_60) SUM(PLC_70) SUM(PLC_TOTAL) --------- ---- ------------------------ ------------- ----------- ----------- ----------- -------------- ----------- ----------- -------------- Warehouse SKUs 1103 63 49242 31428 17682 17672 31590 30 174355 Warehouse SKUs 1102 63 44234 28096 16022 16010 28270 30 157647 Warehouse SKUs 1101 63 54450 34902 19422 19410 35070 30 193467 Warehouse SKUs 1104 42 51724 33548 18088 18078 33660 20 183910 Executed in 0,889 seconds 10:04:08 SQL> |
AW: Laufzeit von Stored Procedure verkürzen
@jobo
Vielen Dank für deine mühe! Gehst Du in deiner Lösung davon aus das es Felder wie z.B. "PLC_25", "PLC_30" usw. in der Datenbank gibt? |
AW: Laufzeit von Stored Procedure verkürzen
Nein, ich erzeuge sie über die Case Anweisungen. Eine statische Kreuztabelle sozusagen.
Die Transformation und auch Aggregation (aus Listenform in Spaltenform und Summierung) könnte man allerdings auch in einem Excelmakro durchführen, wenn Excel sowieso als Reportausgabe dient. Dann muss das SQL jenachdem etwas mehr Daten ausspucken. |
AW: Laufzeit von Stored Procedure verkürzen
Ok habs verstanden...
Die Abfrage ist bei mir auch rasend schnell... Aber leider stimmt das Ergebnis nicht... da muss ich nochmal zeit investieren um das richtig hinzubekommen... Aber die erste Abfrage war auch nicht mein zeitliches Problem... Ich denke mal deine Lösung hilft mir bei den späteren Abfragen die Mengen sumiert werden z.B. bei WHs Stock |
AW: Laufzeit von Stored Procedure verkürzen
@jobo
Die Idee mit der Kreuztabelle ist genial :thumb: Ich hab die Stock Berechnungen darauf angepasst... Die dauern jetzt nur noch 8 Sekunden, davor waren es 31 Sekunden! |
AW: Laufzeit von Stored Procedure verkürzen
Zitat:
Da ich keine Tabellenvorlage hatte und es eher ums Prinzip ging, ist das korrekte Ergebnis eher Nebensache gewesen. Außerdem wolltest Du ja Laufzeit verkürzen, von richtigem Ergebnis war keine Rede ;) Ich drück Dir die Daumen |
AW: Laufzeit von Stored Procedure verkürzen
Ich hab in meiner großen Stored Procedure die Unions eliminiert und führe jeden Select in einer separaten Stored Procedure durch.
Des Weiteren habe ich die Statements an das Bsp. von jobo (?) angepasst... Die Laufzeit liegt nun bei ca. 40 Sek. Anbei die SQL Statements... Evtl. entdeckt jemand noch Verbesserungs möglichkeiten:
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 Off Topic Das man hier als Cretin oder Vollpfosten bezeichnet wird hat mich jetzt nicht so gestört, es ist ja niemand Allwissend (naja manche meines es vielleicht)... Aber das der Werte Herr (oder Dame) arrogant sowas behauptet und dann keine Hilfestellung geben kann finde ich sehr schwach... |
AW: Laufzeit von Stored Procedure verkürzen
Zitat:
Zitat:
Zitat:
Der Weg (temporäre Tabellen, Kreuztabelle/Pivot) ist ja schon mehrfach vorgegeben worden. Wenn Du auf eine Performancesteigerung angewiesen bist, dann steht es dir frei, professionelle Hilfe mit Erfolgsgarantie (Kein Erfolg-kein Geld) anzufordern. Wenn Du kein Geld ausgeben willst oder kannst, dann rate ich dir, nicht so künstlich nachtragend zu sein und diesen Blödsinn ('Herr oder Dame') zu lassen. Deiner direkten Antwort auf mein Statement jedenfalls ist nicht zu entnehmen, das Du -ob der Arroganz- verletzend zusammengebrochen ist. Ich zitiere. Zitat:
Hättest Du dich echauffiert (Mißverständnisse kommen ja vor), hätte ich mich natürlich entschuldigt und das richtig gestellt. Ich bin nämlich irrtümlicherweise davon ausgegangen, das du die Suppe auslöffeln musst, die ein wirklicher Volldrömel angestellt hat ("Herr Andidreas! Machen Sie das mal schneller!"). In diesem Sinne: Viel Spaß noch beim Haareraufen. |
Alle Zeitangaben in WEZ +1. Es ist jetzt 08:03 Uhr. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
LinkBacks Enabled by vBSEO © 2011, Crawlability, Inc.
Delphi-PRAXiS (c) 2002 - 2023 by Daniel R. Wolf, 2024 by Thomas Breitkreuz