Abfrage mit Summe optimieren

[EarlyBird]

Hallo,
ich möchte folgende Abfrage optimieren

Code:

SELECT lfd, wert, (SELECT sum(wert) from Tabel1 where lfd <= Table1Stamm.lfd) as WertSum from Table1 as Table1Stamm order by Table1Stamm.lfd

ich möchte den Summenwert nicht fest in einem Feld speichern sonder bei jeder Abfrage soll die Summe ermittelt werden.
Das Feld lfd ist indexiert.
Die Abfrage funktioniert so, aber ist nicht sehr performant.

Habt jemand einen Tipp wie ich das optimieren kann?

[mkinzler]

Ich würde das asl eine SP implementieren, in der ich die Werte in einer lokalen Variable kummuliere und zurückgeben würde.

[jobo]

Du willst die Summe aller "vorigen" Werte?

Wenn man dieses Statement einfach in eine SP packt, wird es wahrscheinlich nicht wesentlich schneller. Dann müsste man in der SP die Werte mit einem eigenen Algo selber zusammenrechnen.

Oder

Seit 2005 gibt es Window Functions (partition over, rank, dense rank, rownumber, usw.)- ist vielleicht noch nicht optimal das Angebot in der Version-, damit würde ich es versuchen. Die sind eigentlich für sowas da (wenn ich die Aufgabe richtig verstanden habe)

[Dejan Vu]

SQL-Code:

select *,0 as summe 
  into #tmp 
  from Tabelle1 
 order by lfd 

declare @summe int = 0
update #tmp
  set @summe = @summe + wert
      summe =  @summe

select * from #tmp

Das geht natürlich auch mit einer Tabellenvariable bzw. weniger Spalten. Es würde auch reichen, nur den PK und die Summe in der Tmp-Tabelle zu halten, so etwa

SQL-Code:

select 0 as summe, wert, PKderTabelle
  into #tmp 
  from Tabelle1 
 order by lfd 

declare @summe int = 0
update #tmp
  set @summe = @summe + wert
    , summe =  @summe

select t.*, x.summe 
  from Tabelle1 t 
       join #tmp x on t.PKderTabelle = x.PKderTabelle

Ich persönlich würde das mit einer Tabellenvariablen machen, weil man sonst die temporäre Tabelle u.U. vorher löschen muss...

Bei den Windowingfunktionen, die jobo erwähnt hat, müsste man probieren, ob es etwas bringt.

[mkinzler]

Da lfd+1 ja lfd einschliesst, sollte es doch einfach so funktionieren ( in FB PSQL)

SQL-Code:

SET TERM ^^ ;
CREATE PROCEDURE SUMMWERTE returns (
  LFD BigInt, 
  WERT Double Precision, 
  WERTSUM Double Precision) AS
BEGIN
  SUSPEND;
END ^^
SET TERM ; ^^
SET TERM ^^ ;
ALTER PROCEDURE SUMMWERTE returns (
  LFD BigInt, 
  WERT Double Precision, 
  WERTSUM Double Precision) AS 
declare w double precision;
begin
  wertsum = 0; 
  w = 0;
  for select 
        lfd, wert from STAMM order by lfd into :lfd, :wert do
        begin 
          for select wert from WERT where lfd = :lfd into :w do
          begin
            wertsum = :wertsum + :w;
          end 
          suspend;
        end  
end ^^
SET TERM ; ^^

Mit einer einfachen Testdatenbak scheint es zu funktionieren.

[Dejan Vu]

Das ist aber kein T-SQL...

[mkinzler]

Ja, habe ich ja auch geschrieben ( FB PSQL). Prinzipiell sollte es aber auch unter T-SQL so funktionieren.

[EarlyBird]

Danke schon mal für die Anregungen.

Wie mir (partition over, rank, dense rank, rownumber, usw.) helfen sollen erschließt sich mir nicht.
Ich benötige ja die Summe der Werte und keine Zeilennummern

Ich habe es jetzt mit der Temporären Tabelle getestet und das funktioniert super.
Geschwindigkeit ist optimiert von 19008 ms zu 470ms
das nenne ich mal super optimiert

Vielen Dank

Eine Frage habe ich noch zur temporären Tabelle.
Kann ich die am Ende der SP einfach per DROP TABLE #tmp löschen?
Oder gibt das Probleme

So sieht die SP jetzt aus

Code:

create PROCEDURE [dbo].[SumTest](@LFDVar Int)
AS
BEGIN
declare @summe int
set @summe = 0
SELECT Wert, LFD,  0 as summe into #tmp from Table1 where (not (Wert is Null)) order by LFD
update #tmp
  set summe = @summe
    , @summe = @summe + wert 

select t.wert, t.LFD, x.summe 
  from Table1 t 
       join #tmp x on t.LFD = x.LFD  where t.LFD > @LfdVar order by t.LFD

DROP TABLE #tmp
END

[Dejan Vu]

sollte keine Probleme machen. Kann nur sein, das dein Provider (ADO o.ä.) mit der message vom 'xxx rows affected' probleme bekommt, weil 'DROP TABLE' etwas anderes sagt. Musst Du ausprobieren. Normalerweise macht man das am Anfang der SP.

Code:

IF OBJECT_ID ('tempdb..#TMP') IS NOT NULL 
  DROP TABLE #TMP

@mkinzler: Die vorgeschlagene Lösung (Schleife) wird auch funktionieren, aber dein vorgestellter Code ist 99% FB eigen, und taugt daher als Ansatz für TSQL nicht... Das ist vollkommen anders, als in TSQL. Da wäre das ne While-Schleife.

[jobo]

Zitat von EarlyBird:

Wie mir (partition over, rank, dense rank, rownumber, usw.) helfen sollen erschließt sich mir nicht.
Ich benötige ja die Summe der Werte und keine Zeilennummern

Das ist eine unvollständige Aufzählung der Windowfunktions unter 2005 server. Wenn sich da nichts erschließt ist es wohl Pech.