AGB  ·  Datenschutz  ·  Impressum  







Anmelden
Nützliche Links
Registrieren
Zurück Delphi-PRAXiS Programmierung allgemein Datenbanken Laufzeit von Stored Procedure verkürzen

Laufzeit von Stored Procedure verkürzen

Ein Thema von Andidreas · begonnen am 4. Okt 2012 · letzter Beitrag vom 18. Okt 2012
Antwort Antwort
Seite 1 von 6  1 23     Letzte » 
Benutzerbild von Andidreas
Andidreas

Registriert seit: 27. Okt 2005
1.110 Beiträge
 
Delphi 10.1 Berlin Enterprise
 
#1

Laufzeit von Stored Procedure verkürzen

  Alt 4. Okt 2012, 15:08
Datenbank: MSSQL • Version: 2008 • Zugriff über: Excel / UniDAC
Hallo zusammen,

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

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

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

SUM(CONVERT(FLOAT, ALL_AvailableStock))

Kann dies die Ursache für die lange Laufzeit meiner Stored Procedure sein?
Ein Programmierer Programmiert durchschnittlich 15 Code Zeilen pro Tag
Wir sind hier doch nicht bei SAP!!!

Aber wir habens bald
  Mit Zitat antworten Zitat
Furtbichler
(Gast)

n/a Beiträge
 
#2

AW: Laufzeit von Stored Procedure verkürzen

  Alt 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?
  Mit Zitat antworten Zitat
Benutzerbild von Andidreas
Andidreas

Registriert seit: 27. Okt 2005
1.110 Beiträge
 
Delphi 10.1 Berlin Enterprise
 
#3

AW: Laufzeit von Stored Procedure verkürzen

  Alt 4. Okt 2012, 15:20
Welcher PC?, RAM? HD?
Spielt das eine Rolle bei einer Stored Procedure?


Wie sieht die Query bzw. SP aus?
Was ist mit SP gemeint?

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...
Ein Programmierer Programmiert durchschnittlich 15 Code Zeilen pro Tag
Wir sind hier doch nicht bei SAP!!!

Aber wir habens bald
  Mit Zitat antworten Zitat
Benutzerbild von p80286
p80286

Registriert seit: 28. Apr 2008
Ort: Stolberg (Rhl)
6.659 Beiträge
 
FreePascal / Lazarus
 
#4

AW: Laufzeit von Stored Procedure verkürzen

  Alt 4. Okt 2012, 15:29
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)
Programme gehorchen nicht Deinen Absichten sondern Deinen Anweisungen
R.E.D retired error detector

Geändert von p80286 ( 4. Okt 2012 um 15:33 Uhr)
  Mit Zitat antworten Zitat
Benutzerbild von Bernhard Geyer
Bernhard Geyer

Registriert seit: 13. Aug 2002
17.169 Beiträge
 
Delphi 10.4 Sydney
 
#5

AW: Laufzeit von Stored Procedure verkürzen

  Alt 4. Okt 2012, 15:31
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

Was ist mit SP gemeint?
SP = Store Procedure
Windows Vista - Eine neue Erfahrung in Fehlern.
  Mit Zitat antworten Zitat
Benutzerbild von Andidreas
Andidreas

Registriert seit: 27. Okt 2005
1.110 Beiträge
 
Delphi 10.1 Berlin Enterprise
 
#6

AW: Laufzeit von Stored Procedure verkürzen

  Alt 4. Okt 2012, 15:32
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
  Mit Zitat antworten Zitat
Benutzerbild von p80286
p80286

Registriert seit: 28. Apr 2008
Ort: Stolberg (Rhl)
6.659 Beiträge
 
FreePascal / Lazarus
 
#7

AW: Laufzeit von Stored Procedure verkürzen

  Alt 4. Okt 2012, 15:34
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
Programme gehorchen nicht Deinen Absichten sondern Deinen Anweisungen
R.E.D retired error detector
  Mit Zitat antworten Zitat
Benutzerbild von Andidreas
Andidreas

Registriert seit: 27. Okt 2005
1.110 Beiträge
 
Delphi 10.1 Berlin Enterprise
 
#8

AW: Laufzeit von Stored Procedure verkürzen

  Alt 4. Okt 2012, 15:44
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
'Warehouseas Storage,
'SKUsas 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
'Consias Storage,
'SKUsas 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
'Warehouseas Storage,
'Piecesas 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
'Consias Storage,
'Piecesas 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
'Warehouseas Storage,
'DoHas 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
'Consias Storage,
'DoHas 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
Ein Programmierer Programmiert durchschnittlich 15 Code Zeilen pro Tag
Wir sind hier doch nicht bei SAP!!!

Aber wir habens bald
  Mit Zitat antworten Zitat
Furtbichler
(Gast)

n/a Beiträge
 
#9

AW: Laufzeit von Stored Procedure verkürzen

  Alt 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.
  Mit Zitat antworten Zitat
Benutzerbild von Uwe Raabe
Uwe Raabe

Registriert seit: 20. Jan 2006
Ort: Lübbecke
10.934 Beiträge
 
Delphi 12 Athens
 
#10

AW: Laufzeit von Stored Procedure verkürzen

  Alt 4. Okt 2012, 15:59
Ü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.
Uwe Raabe
Certified Delphi Master Developer
Embarcadero MVP
Blog: The Art of Delphi Programming
  Mit Zitat antworten Zitat
Themen-Optionen Thema durchsuchen
Thema durchsuchen:

Erweiterte Suche
Ansicht

Forumregeln

Es ist dir nicht erlaubt, neue Themen zu verfassen.
Es ist dir nicht erlaubt, auf Beiträge zu antworten.
Es ist dir nicht erlaubt, Anhänge hochzuladen.
Es ist dir nicht erlaubt, deine Beiträge zu bearbeiten.

BB-Code ist an.
Smileys sind an.
[IMG] Code ist an.
HTML-Code ist aus.
Trackbacks are an
Pingbacks are an
Refbacks are aus

Gehe zu:

Impressum · AGB · Datenschutz · Nach oben
Alle Zeitangaben in WEZ +1. Es ist jetzt 23:55 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