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 :-D

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:

Zitat von EarlyBird (Beitrag 1269996)

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.

[Dejan Vu]

Zeig doch mal deinen Ansatz. Ich glaube, es ist nicht klar, wo der Vorteil von ROW_NUMBER ggü dem Feld 'lfd' ist.

Edit: Ich sehe gerade

SQL-Code:

SELECT *,
  SUM(wert) OVER(ORDER BY lfd
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
          AS Summe
  FROM Table1

:shock: ungetestet (soll ANSI-SQL sein)

Und woher hab ich das?
http://stackoverflow.com/questions/8...l-in-sqlserver

[EarlyBird]

Danke Dejan Vu
Das sind Ansätze die weiterhelfen.
Werde es gleich Testen

[EarlyBird]

Zitat:

Zitat von Dejan Vu (Beitrag 1270004)

SQL-Code:

SELECT *,
  SUM(wert) OVER(ORDER BY lfd
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
          AS Summe
  FROM Table1

Das funktioniert in SQL 2005 leider noch nicht
Habe es gerade auf einem Sql 2012 Server getestet da klappt es.

Das optimiert unter SQLServer 2012 noch mal von 316ms auf 79ms
(Temporäre Tabelle gegenüber select sum over)

Leider kann ich es unter Sql 2005 noch nicht nutzen :(
Vielen Dank
(auch an jobo)

[EarlyBird]

ein keines Problem gibt es noch:

SQL-Code:

SELECT *,
  SUM(wert) OVER(ORDER BY lfd
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
          AS Summe
  FROM Table1 where lfd > 111

füge ich ein where in die Sqlabfrage ein dann summiert die over funktion erst ab dem ersten wert der Abfrage und nicht nach dem ersten wert in der Tabelle
ich habe es jetzt so gelöst:

SQL-Code:

select * from ( select lfd, wert,
SUM(wert) OVER(ORDER BY lfd
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
          AS Summe
  FROM Table1) as tabl1 where tabl1.lfd > 111

Aber das bremst das ganze wieder aus
Ich habe die Over Funktion mit verschiedenen Parametern getestet leider ohne Erfolg.
Gibt es eine Möglichkeit mit der Over Funktion die Summe ab dem Ersten Datensatz aus der Tabelle zu bilden?

[jobo]

Was brauchst Du denn jetzt, 2005 oder 2012?

Falls ich oben gemeint war, ich habe nie geschrieben, dass row_number die Lösung ist.
Es war lediglich eine Erläuterung, um die entsprechenden Funktionen (Window Funktions) zu finden und einzuordnen. Dass ich selber Zweifel hatte, ob die Window Function Implementierung in Version 2005 bereits ausreicht, das zu lösen, hab ich ja angedeutet.

In dem Link von Dejan Vu ist ein Beispiel für 2005, allerdings mit Cross Apply, wie das performed, kann ich nicht sagen, es sollte jedenfalls funktionieren:

Code:

select
     t.lfd,
     t.wert ,
    rt.runningTotal
  from Table1 t
 cross apply (select sum(wert) as runningTotal
             from Table1
             where lfd <= t.lfd
          ) as rt
order by t.lfd

Mit einer reinen SQL Lösung sparst Du jedenfalls das Handling der Temp Table und brauchst keine SP.
Wenn es schnell genug ist, würde ich das vorziehen.

Das Problem mit der Where Bedingung habe ich nicht verstanden...

Zitat:

erst ab dem ersten wert der Abfrage und nicht nach dem ersten wert in der Tabelle

??

[EarlyBird]

ich brauch die Lösung für den SQL Server 2005
Ich habe aber einen 2012 auf dem ich auch Testen kann.
Und ich hoffe das der 2005er bald ausgetauscht wird.

Dein Tipp mit OVER war ja richtig ich habe es nur nicht sofort erkannt.:oops:

Die 2005er Lösung mit cross apply habe ich getestet.
Das ist genauso langsam wie mein ursprüngliche Abfrage.

Ich werde es dann wohl mit der Temp Tabelle machen.

Zu meinem Problem mit der Where Bedingung.
Wenn ich folgende Abfrage ausführe:

SQL-Code:

SELECT *,
  SUM(wert) OVER(ORDER BY lfd
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
          AS Summe
  FROM Table1 where lfd > 3

Dann erhalte ich in der Spalte Summe nur die Werte summiert die in der Abfrage enthalten sind.
Die ersten 3 Werte werden nicht mit summiert.
Ich benötige aber immer die Summe aller Werte aus der Tabelle
Ich hoffe so ist es etwas verständlicher

[jobo]

Gut, im Prinzip würde ich das mit einem verschachtelten Select machen, so wie Du es bereits probiert hast. Wenn das zu einem Performanceeinbruch führt, deutet das auf ein Optimizerproblem hin. Vlt kann man da mit hints arbeiten, die das System zwingen, schön brav erst innen, dann außen durchzuführen.
Kann aber auch sein, dass bei einer sehr großen Datenmenge, das innere Ergebnis so groß ist, dass er Kopfschmerzen kriegt bei der äußeren Eingrenzung (index auf lfd nicht mehr wirksam)
Vielleicht kann mit UNBOUNDED PRECEDING noch spielen, denn es geht ja eigentlich um diese Einschränkung. Ich arbeite in der Praxis nicht mit MSSQL, vielleicht gibt's noch Möglichkeiten.
Oder je nach Anwendungsfall den Zugriff auf indexed views umstellen. Bietet sich aber wohl nur an, wenn die Grundmenge nur nach oben hin wächst und nicht ständig vollständig neu aufgebaut werden muss.
Ist doch aber alles egal, wenn Du eh 2005 nimmst, oder?

[Dejan Vu]

Der Aufwand bei einem 'naiven' Subselect wird O(n^2) bleiben, denn für jede Zeile wird die Summe ausgerechnet. Bei einem einmaligen 'manuellen' Rechnen dagegen ist der Aufwand O(n). Ich kann mir jetzt nicht vorstellen, das ein Optimizer begreift, das man in einem Subselect ein 'running total' ausrechnet und das auch in O(1) ginge (Ergebnis der letzten Zeile + Wert). Aber Optimizer vollbringen wirklich wahre Wunder, vielleicht auch hier.
Tipp zur gefilterten Anzeige :

SQL-Code:

select * from (
  SELECT *,
         SUM(wert) OVER(ORDER BY lfd
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
          AS Summe
  FROM Table1
) x where lfd>3

Diese Query erzeugt zuerst die laufende Summe über alle Zeilen und zeigt dann nur die Zeilen mit 'lfd' > 3 an. Schnell genug sollte das auch sein.

Die laufende Summe (bzw. die Pflege) könnte man auch über einen Trigger lösen, aber derart redundante Information hat normalerweise nichts in einer Produktivdatenbank (3NF) verloren.

[jobo]

Zitat:

Zitat von Dejan Vu (Beitrag 1270074)

Tipp zur gefilterten Anzeige :

SQL-Code:

select * from (
  SELECT *,
         SUM(wert) OVER(ORDER BY lfd
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
          AS Summe
  FROM Table1
) x where lfd>3

Ich glaub das hatte er schon, ist zu langsam.
Daher meine Vermutung, große Datenmenge (hier dann als zwischenergebnis), das harmlose id>3 macht einen Fullscan auf dem Zwischenergebnis (das auf Platte liegt) usw usw. Aber eben vielleicht auch nur ein doofer Optimizer Pfad.

[Sir Rufo]

Wofür wird das gebraucht, bzw. wie sieht die weitere Verarbeitung aus?

Es kann durchaus Sinn machen, diese Informationen erst in der nachgelagerten Verarbeitung zu ermitteln.

[EarlyBird]

@jobo
Das verschachtelte Select funktioniert auch sehr gut.
Ich dachte nur das es vielleicht auch direkt mit der Over Funktion klappt.
Mit UNBOUNDED PRECEDING habe ich schon alles versucht was ich in der Dokumentation dazu gefunden habe.
Und, es Stimmt, eigentlich ist es egal da ich es für 2005 nicht nutzen kann.
Aber es interessiert mich doch sehr, wieviel performanter man es in neuren Versionen lösen kann.

@Dejan Vu
Das Subselecct habe ich ja schon getestet.
Der Optimizer funktioniert da wirklich super.
folgende Abfrage benötigt nur noch 22ms

SQL-Code:

Select * from(
select lfd, wert, 
SUM(wert) OVER(ORDER BY lfd
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
          AS Summe
  FROM Table1) as tbl1 where (tbl1.lfd > 20920)

und das bei über 22000 Datensätzen.
Ich denke noch schneller geht es kaum.

Das Subselect ist nur unbedeutend langsamer.
Ich wollte nur die Abfrage möglichst optimieren und die OVER Funktion richtig verstehen und auch nutzen.

[Sir Rufo]

Hier ein Artikel zu dem Thema mit einer interessanten Alternative
http://www.codeproject.com/Articles/...-in-SQL-Server

Die Abfrage würde dann wie folgt sein

SQL-Code:

SELECT 
  a.lfd, 
  a.wert,
  SUM( b.wert ) as SumWert
from 
  Table1 a,
  Table1 b
where
  b.lfd <= a.lfd
group by
  a.lfd, a.wert
order by 
  a.lfd

[EarlyBird]

Danke Sir Rufo
Auch ein sehr interessanter Ansatz.
Und bei der Abfrage weniger Datensätze auch sehr performant.
Aber bei vielen Datensätzen doch deutlich langsamer wie die SP mit Temp Table
Und viel langsamer wie die OVER Funktion

[Sir Rufo]

Das war auch durchaus zu erwarten, diese Abfrage ist lediglich schneller als die mit dem SubSelect aus dem ersten Thread.