SQL Server: Performance von DATEADD ist ne katastrophe

[Phoenix]

Hi,

hier benutzen wir die DATEADD-Funktion, um rekursiv über eine Tabelle Werte zur Laufzeit zu berechnen.
Nur leider ist das ausführungstechnisch ne Katastrophe.

Allein der DATEADD-Aufruf in der rekursiv aufgerufenen Funktion macht bei einem Query einen Unterschied zwischen 0,x und ca. 6-8 Sekunden Laufzeit aus. Und es wurde tatsächlich nur der eine Aufruf auskommentiert, das heisst die Rekursionstiefe der Funktion wird jedes mal erreicht - die ist also nicht schuld.

Kann man DATEADD irgendwie anders / performanter im SQL Server implementieren?

[nahpets]

Hallo,

der Unterschied zwischen

SQL-Code:

select 
--DateAdd(YY,5,Datum), 
Datum from tabelle

und

SQL-Code:

select 
DateAdd(YY,5,Datum), 
Datum from tabelle

liegt bei unserer Datenbank bei ca. 250000 Sätzen unter 1 Sekunde.
Mit was für Datenmengen arbeitet Ihr. Führt Ihr das SQL pro Datensatz aus, sind die zu ändernden Felder in einem Index, der mitgepflegt werden muss? Gibt es Trigger, die auf die Änderung des Datumsfeldes reagieren?

[Phoenix]

Hrm.. etwas nähere Investigation hat ergeben, dass es an was anderen liegt. Was, ist allerdings nicht wirklich raus.
Das wird in einer UDF aufgerufen, die rekursiv Parent-Beziehungen hinaufgeht und von einem Startdatensatz aus Werte weiterkalkuliert. Nehmen wir die Kalkulation heraus, ist das Ding sofort fertig - und die Kalkulation besteht aus

SQL-Code:

SET @newValue = DATEADD(day, @offset, @oldValue)

Interessanterweise ist es aber so, dass er genau dann schnell ist, wenn er in der Rekursion im Startwert NULL zurück bekommt und aufgrund dieses Wertes dann eben nicht in die Berechnung läuft, bei einem anderen Wert ist er aber langsam.

Als ob der SQL Server in dem Moment, in dem er den Startwert = NULL ermittelt hat, die weitere Berechnung wegoptimieren und gleich alle Ergebnisse mit NULL besetzen würde, anstelle jedem nochmal einen zusätzlichen Wert (und sei es anstelle von Dateadd ein Konstanter Zeitwert) zuweisen müsste.

Ergo: Weise ich einen Wert zu (egal ob Berechnet oder Konstant), braucht die SP ~7 Sekunden. Weise ich keinen Wert zu, ist es in unter 1 Sekunde abgehandelt. Vorausgesetzt, der Rekursiv ermittelte Startwert ist nicht NULL. Ist er NULL, ist er in beiden Fällen in unter 1 Sekunde durch.

Die Rekursion muss er aber in beiden Fällen vollends auflösen, deswegen wundert es mich, dass dann die reine Zuweisung von egal was an den Ausgangswert so einen derben Einfluss auf die Geschwindigkeit hat.

[omata]

Kannst du mal näher auf die Rekursion eingehen. Ist die überhaupt nötig? Vielleicht kann man da ja etwas anders machen.

[alzaimar]

Ich möchte nochmal etwas zur Performance der DATEADD-Funktion beitragen:

SQL-Code:

select count (distinct datum) from Tabelle
select count (distinct DateAdd(yy,5,datum)) from Tabelle

Ergibt beim Query-Plan ein Verhältnis von 1:5 (13% zu 87%), letzteres ist also 5x langsamer.

[Phoenix]

Zitat:

Zitat von omata

Kannst du mal näher auf die Rekursion eingehen. Ist die überhaupt nötig? Vielleicht kann man da ja etwas anders machen.

Mal schauen.
In einer Tabelle stehen Informationen zu dieser Vorwärtskalkulation (pro einzelnem Datensatz) von Daten (Muss berechnet werden? Wenn ja: von welchem Wert (anderer Datensatz) aus? Wieviel und was (Tage, Wochen, Monate, Jahre?).

In einer weiteren Tabelle stehen dann die Werte.

Für die Berechnung geht die Funktion dann her, selektiert zu dem abgefragten Datensatz den Info-Satz (Tabelle 1).
Wenn nicht kalkuliert wird, wird der eigentliche Wert in der Tabelle zurück gegeben.
Wenn kalkuliert wird, wird die selbe Funktion für den Parent aufgerufen (um ggf. den kalkulierten Wert des Parents zu ermitteln), und dann wird der so ermittelte Wert mit der entsprechenden Anzahl an Tagen / Wochen / etc. aufaddiert und zurück gegeben.

Wir haben in einem Beispiel 30 Datensätze, die jeweils vom Vordermann abhängig sind.
Das heisst also 30! Aufrufe. Da es drei solche Datumswerte gibt kann das also bis zu 3* 30! calls geben. Im Test haben wir zwei Reihen kalkulieren lassen.

Und jetzt kommt eben der Knackpunkt:
Die Anzahl der Aufrufe ist Konstant - wenn der erste Datensatz leer ist, dann gibt es nichts zu kalkulieren, und die View ist in unter 1 sekunde da. Ist der erste Wert in einer Berechnung belegt, braucht das ganze 6 - 7 Sekunden. Sind beide belegt entsprechend 12 - 15 Sekunden.

Interessant ist nun, dass der Aufwand für beide alternativen (es kommt beim Aufruf der Funktion 0 oder ein anderer Wert zurück) eigentlich im Test der gleiche ist. Kommt 0 zurück, weisen wir den Wert der Ausgabe zu, der aktuell im abgefragten Datensatz steht. Kommt ein DateTime Wert <> 0 zurück, so weisen wir diesen Wert der Ausgabe zu. (Das DateAdd wurde auskommentiert).

Dennoch ist dieser massive Unterschied in der Laufzeit da. Ich verstehe das einfach nicht. Es sollte dem SQL Server doch eigentlich egal sein, was da zugewiesen wird, oder?

[omata]

Ich bin nicht sicher, ob ich deine Beschreibung richtig verstanden habe. Trotzdem versuche ich mal einen Vorschlag zu machen. Vielleicht bringt er dich ja auf einen anderen Ansatz...

Tabellenstruktur...

SQL-Code:

CREATE TABLE [Daten] (
   [id] [int] NOT NULL ,
   [parent_id] [int] NULL ,
   [Datum] [datetime] NULL ,
   CONSTRAINT [PK_Daten] PRIMARY KEY  CLUSTERED 
   (
      [id]
   )  ON [PRIMARY] ,
   CONSTRAINT [FK_Daten_Daten] FOREIGN KEY 
   (
      [parent_id]
   ) REFERENCES [Daten] (
      [id]
   )
) ON [PRIMARY]

Tabelleninhalt...

Code:

id   parent_id    datum
1    NULL         21.10.2000
2    1            NULL
3    2            NULL
4    NULL         01.01.2008
5    4            NULL
6    3            30.11.2000

Abfrage...

SQL-Code:

DECLARE @id INT
SET @id = NULL -- NULL = alle letzten Datensätze
               -- Zahl = Nur den ausgewählten Datensatz

DECLARE @IDS   TABLE (id INT PRIMARY KEY, parent_id INT, Datum1 DATETIME, Datum2 DATETIME)
DECLARE @Done  BIT 

SET @Done = 0

INSERT @IDS(id, parent_id, Datum1, Datum2)
SELECT id, parent_id, NULL, Datum
FROM daten x
WHERE (   NOT EXISTS (SELECT *
                      FROM daten
                      WHERE parent_id = x.id)
       AND @id IS NULL)
  OR (id = @id AND @id IS NOT NULL)

IF @@ROWCOUNT = 0 SET @Done = 1
WHILE @Done = 0 BEGIN

  UPDATE @IDS
  SET parent_id = d.parent_id,
      datum1 = d.datum
  FROM @IDS ids
  INNER JOIN daten d
    ON ids.parent_id = d.id   
  WHERE ids.datum1 IS NULL
    AND ids.parent_id IS NOT NULL

  IF @@ROWCOUNT = 0 SET @Done = 1
END 

SELECT id, datum1, datum2, 
       COALESCE(DATEDIFF(dd, datum1, datum2), 0) tage, 
       COALESCE(DATEDIFF(wk, datum1, datum2), 0) wochen, 
       COALESCE(DATEDIFF(mm, datum1, datum2), 0) monate, 
       COALESCE(DATEDIFF(yy, datum1, datum2), 0) jahre
FROM @IDS

Vielleicht kannst du ja auch nochmal genauer und mit Beispieldaten zeigen was du hast und was du benötigst.

[Phoenix]

Danke, das muss ich mir mal in einer ruhigen Minute zu Gemüte führen. Im Moment blick ich grad ned durch, das sollte morgen wenn ich ausgeschlafen bin aber anders aussehen ;-)

Ich darf leider nicht weiter auf die Datenstruktur und/oder die Daten eingehen. Allerdings ist der aktuelle Stand jetzt eher, dass wir nach Änderungen an Daten, die auf diese Datumskalkulation einfluss haben, die Daten gleich live in einem Trigger vorberechnen und dann zu den regulären Datensätzen speichern, anstelle sie jedes mal beim auslesen neu zu berechnen. Das ist natürlich ein grösserer Aufwand beim Speichern, aber den auch nur einmal von der geänderten Stelle aus alle Pfade 'nach unten' anstelle jedes mal von unten nach oben hochzukalkulieren und dabei ggf. etliche Werte noch doppelt zu berechnen.