Abfrage mit Summe optimieren

[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 von Dejan Vu:

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.