Frank Kalis

Dauer: 45 Minuten

Inhalt: SQL Server 2005 bringt eine Vielzahl neuer und verbesserter Diagnosetools mit, die die Erkennung, Vermeidung und Behebung von Problemen sowie das Performancetuning vereinfachen. In diesem CommunityCast zeige ich sowohl die einfach zugänglichen grafischen Tools wie die Serverberichte, den Activity Viewer und die neue Log-Ansicht als auch die detaillierte Diagnose in T-SQL mit den neuen Dynamic Management Views. Zum Abschluss demonstriere ich das Performance Tuning mit dem Profiler und dem Database Tuning Advisor.

Ganze Geschichte »

Wie man die effektive Verzinsung aus der Nominalverzinsung errechnet, haben wir in diesem Beitrag gesehen. Neulich hat mich aber ein Posting in den MSDN Newsgroups auf die Excel Funktion NOMINAL() aufmerksam gemacht, die genau das Gegenteil macht. Also die Errechnung der Nominalverzinsung bei gegebenem Effektivzins. Um es leichter nachvollziehbar zu machen, verwenden wir die Gegebenheiten aus dem oben bereits referenzierten Beitrag.
Zur Erinnerung: Der von uns errechnete jährliche effektive Zinssatz lag bei 20,27% bei 12 Zinszeitpunkten im Jahr. Also, monatlicher Zins. Diese beiden Angaben (Effektivzins, Perioden) benötigen wir, um daraus den Nominalzinssatz errechnen zu können. Dies geschieht nun folgendermassen:

DECLARE @apr FLOAT
DECLARE @frequency FLOAT

SET @apr = 20.270504548765487/100
SET @frequency = 12

SELECT @frequency * POWER(1+@apr, 1/@frequency)-@frequency AS Nominal_Jahreszins

Nominal_Jahreszins   
-------------------- 
0.18599999999999994

(1 row(s) affected)

In Excel würde das Ganze so

=NOMINAL(A1;A2)

aussehen, wobei in Zelle A1 der Zinssatz und in Zelle A2 die Anzahl der Perioden stehen würde.

Vor einiger Zeit kam eine interessante Frage in der Newsgroup auf. Gesucht wurde der am häufigsten vorkommende Preis pro Artikelgruppe. Also der Modus je Artikelgruppe. Gegeben ist das folgende Ausgangsszenario:

CREATE TABLE #t (ArtikelGruppe CHAR(2), Preis DECIMAL(8,2))
INSERT INTO #t VALUES('SP', 1.2)
INSERT INTO #t VALUES('SP', 1.2)
INSERT INTO #t VALUES('SP', 2.1)
INSERT INTO #t VALUES('GR', 2.2)
INSERT INTO #t VALUES('GR', 2.3)
INSERT INTO #t VALUES('GR', 2.3)
INSERT INTO #t VALUES('GR', 2.3)

Gemäß der Aufgabenstellung soll nun

ArtikelGruppe Preis      Anzahl      
------------- ---------- ----------- 
SP            1.20       2
GR            2.30       3

(2 row(s) affected)

als Ergebnis erscheinen. Doch diese an und für sich scheinbar einfache Frage kann sich jedoch zu einem mittelschweren Brainteaser entwickeln. Den am häufigsten vorkommenden Preis über sämtliche Artikel kann man noch relativ leicht und intuitiv bestimmen mittels:

SELECT TOP 1 WITH TIES ArtikelGruppe, Preis, COUNT(*) Anzahl
  FROM #t
 GROUP BY ArtikelGruppe, Preis
  ORDER BY Anzahl DESC

ArtikelGruppe Preis      Anzahl      
------------- ---------- ----------- 
GR            2.30       3

(1 row(s) affected)

Doch wie kommt nun die Gruppierung nach Artikelgruppen hier ins Spiel? Christoph Muthmann kam zu folgendem Lösungsansatz:

--Christoph's Ansatz
SELECT ArtikelGruppe, Preis, COUNT(*) AS Anzahl
  FROM #t a
 GROUP BY artikelgruppe, preis
  HAVING COUNT(*) >= 
   (SELECT MAX(Anzahl) 
      FROM 
      (SELECT artikelgruppe, preis, COUNT(*) AS Anzahl
         FROM  #t b
        WHERE b.artikelgruppe = a.artikelgruppe
         GROUP BY artikelgruppe, preis) c)

ArtikelGruppe Preis      Anzahl      
------------- ---------- ----------- 
SP            1.20       2
GR            2.30       3

(2 row(s) affected)

während mein ursprünglicher Ansatz folgendermaßen aussah:

--Mein ursprünglicher Ansatz
SELECT ArtikelGruppe,Preis, COUNT(*) Anzahl
  FROM #t a 
 GROUP BY ArtikelGruppe,Preis 
  HAVING NOT EXISTS 
   (SELECT *
      FROM #t b 
     WHERE b.ArtikelGruppe = a.ArtikelGruppe 
     GROUP BY b.Preis
     HAVING COUNT(b.ArtikelGruppe) > COUNT(a.ArtikelGruppe)) 

ArtikelGruppe Preis      Anzahl      
------------- ---------- ----------- 
SP            1.20       2
GR            2.30       3

(2 row(s) affected)     

Vergleicht man nun die Ausführungspläne wird man bei meinem Ansatz einen "Lazy Spool" entdecken. Für gewöhnlich ist dies ein schlechtes Omen und man sollte versuchen, die Abfrage so umzuformulieren, daß dieser logische Operator nicht mehr auftaucht. Da ich aber Christoph's Ansatz sowieso für eleganter halte, formuliere ich lieber diesen ein bißchen um, um einen deutlich schlankeren und damit wahrscheinlich effektiveren Ausführungsplan zu erhalten:

SELECT ArtikelGruppe, Preis, COUNT(*) AS Anzahl
  FROM #t t
 GROUP BY ArtikelGruppe, Preis
  HAVING COUNT(*) =
  (SELECT MAX(Anzahl)
     FROM
      (SELECT ArtikelGruppe, Preis, COUNT(*) AS Anzahl
         FROM #t
        GROUP BY ArtikelGruppe, Preis) x
         WHERE ArtikelGruppe = t.ArtikelGruppe)

ArtikelGruppe Preis      Anzahl      
------------- ---------- ----------- 
SP            1.20       2
GR            2.30       3

(2 row(s) affected)

Christoph hat dann die drei obigen Statements mal an einem repräsentativen Datenbestand ausprobiert. Die Tabelle hat 250.000 Zeilen und umgesetzt auf das Beispiel 8 Artikelgruppen mit 358 verschiedenen Preisen. Ohne Verwendung von Indexes kommen dabei folgende Laufzeiten heraus:

• Christoph's Ansatz: 5.390 Millisekunden
• Mein Ansatz: 19.033 Millisekunden
• Neuer Ansatz: 436 Millisekunden

Erstellt man jetzt einen Index auf (ArtikelGruppe, Preis) kommt es zu folgenden Laufzeiten:

• Christoph's Ansatz: 153 Millisekunden
• Mein Ansatz: 500 Millisekunden
• Neuer Ansatz: 110 Millisekunden

Beeindruckend kann man erkennen, wie stark manche Abfragen durch geeignete Indexes beschleunigt werden können.

Der Vollständigkeithalber kann man alternativ zu MAX() zwar auch diverse TOP Varianten einsetzen:

SELECT artikelgruppe, preis, COUNT(*) AS Anzahl
  FROM #t a
 GROUP BY artikelgruppe, preis
  HAVING COUNT(*) = 
   (SELECT TOP 1 COUNT(*) 
      FROM #t
     WHERE a.ArtikelGruppe = ArtikelGruppe
     GROUP BY ArtikelGruppe, Preis
     ORDER BY COUNT(*) DESC) 

SELECT ArtikelGruppe, Preis, COUNT(*) AS Anzahl
  FROM #t t
 GROUP BY ArtikelGruppe, Preis
  HAVING COUNT(*) =
  (SELECT TOP 1 Anzahl
     FROM
      (SELECT TOP 100 PERCENT ArtikelGruppe, Preis, COUNT(*) AS Anzahl
         FROM #t
        GROUP BY ArtikelGruppe, Preis 
         ORDER BY Anzahl DESC) x
        WHERE ArtikelGruppe = t.ArtikelGruppe)

die ebenfalls das gewünschte Ergebnis bringen, aber auch keine entscheidende Verbesserung im Vergleich zu den beiden in der Newsgroup geposteten Statements bzgl. der Ausführung bringen.

Wie in einem der letzten Beiträge dieser Kategorie angekündigt, möchte ich hier auf der Website meine Implementierung der Berechnung der Yield to maturity vorstellen. Andere Begriffe für Yield to maturity sind auch arithmetische Rendite oder Effektivzins. Sämtliche Begriffe sind durchaus gebräuchlich und werden verwendet, um ein und diesselbe Sache zu beschreiben. Und so werden auch in diesem Beitrag diese Begriffe synonym verwendet.
Einige Prämissen gleich vorweg: Wir betrachten hier in diesem Beitrag nur den einfachsten Fall der Effektivzinsberechnung und beschränken uns auf die Diskussion eines festverzinslichen Wertpapieres mit jährlicher Zinszahlung ohne eingebettete Derivate mit einem Rückzahlungskurs von 100%. Aus Gründen der Vereinfachung liegen die erwarteten Cash Flows bereits vor. In der Praxis würde man diese wahrscheinlich erst zur Laufzeit generieren. Auch beschränken wir uns auf die am deutschen Kapitalmarkt mittlerweile eher unübliche 30/360 Zinsmethode. Die Erweiterung für die diversen Tageskonventionen überlasse ich dem geneigten Leser, dem ich mit dem hier vorgestellten Skript schon genug Basisarbeit abgenommen habe. ;-)

Das fundamentale Prinzip jeglicher Bewertung ist, daß der Wert einer Kapitalanlage dem Present Value (oder Barwert) der erwarteten Zahlungsströme entspricht. Dabei spielt es keinerlei Rolle, welcher Art diese Kapitalanlage ist. Von daher setzt sich jeder Valuierungsprozeß aus folgenden 3 Schritten zusammen:

1. Bestimmung der (erwarteten) Cash Flows.
2. Festlegung eines angemessenenen Diskontsatzes (bzw. angemessener Diskontsätze).
3. Berechnung des Present Values der Zahlungsströme aus 1. anhand der Werte aus 2.

So, zunächst einmal hört sich das nicht weiter schwer an. Ist es eigentlich auch nicht. Aber man muß sich verdeutlichen, daß dies ein iterativer Prozeß ist, der nur bedingt mit dem mengenbasierten Ansatz relationaler Datenbanken zu vereinbaren ist. Man berechnet die Rendite jedes einzelnen Wertpapieres indem man solange eine Schleife durchläuft, bis das Ergebnis hinreichend genau ist. Jawohl, hinreichend genau. Das Ganze ist ein Trial-and-Error Prinzip.

Genug der Worte. Ein Beispiel: Gegeben sei ein Wertpapier mit einem jährlichen Zinskupon von 5,00% und einer Endfälligkeit in genau 4 Jahren. Der Preis für das Papier beträgt 95,92. Frage: Welche Yield-to-maturity hat dieses Papier? Antwort: ca.6,1824% Lösung: Gemäß unserem obigen Schema, benötigen wir zuerst die Cash Flows.

CREATE TABLE Cash_Flows 
( 
 cashflow DECIMAL(8,2), 
 valuta DATETIME 
) 
INSERT INTO Cash_Flows VALUES (-95.92,'20060115') 
INSERT INTO Cash_Flows VALUES (5,'20070115') 
INSERT INTO Cash_Flows VALUES (5,'20080115') 
INSERT INTO Cash_Flows VALUES (5,'20090115') 
INSERT INTO Cash_Flows VALUES (105,'20100115')

So, am Anfang steht ein negativer Cash Flow (d.h. eine Auslage), da wir diesen Preis bezahlen müssen, um das Papier zu erwerben. Danach erhalten wir 4 Jahre lang einmal pro Jahr die Kuponzahlung iHv 5 und zusätzlich im letzten Jahr den Nominalbetrag (= 5 + 100 = 105). Den eigentlichen Code wird man zweckmäßigerweise in eine UDF packen. Diese könnte folgendermaßen aussehen:

CREATE FUNCTION yield_to_maturity(@issue_date SMALLDATETIME)
RETURNS DECIMAL(15,14) 
AS 
BEGIN 
 DECLARE @ytm_tmp FLOAT
 DECLARE @ytm FLOAT
 DECLARE @pv_tmp FLOAT
 DECLARE @pv FLOAT

 SET @ytm_tmp = 0
 SET @ytm = 0.1
 SELECT @pv_tmp = SUM(cashflow) FROM Cash_Flows
 SET @pv = 
 (SELECT SUM(cashflow/POWER(1.0+@ytm,(DATEDIFF(month,@issue_date, valuta)* 30 + DAY(valuta)-DAY(@issue_date) 
  - 
  CASE 
   WHEN(@issue_date)>=30 AND DAY(valuta) = 31 
   THEN 1 
   ELSE 0 
  END)/  360.0 ))
    FROM Cash_Flows)

 WHILE ABS(@pv) >= 0.000001
 BEGIN
  DECLARE @t FLOAT
  SET @t = @ytm_tmp
  SET @ytm_tmp = @ytm
  SET @ytm = @ytm + (@t-@ytm)*@pv/(@pv-@pv_tmp)
  SET @pv_tmp = @pv
  SET @pv =
  (SELECT SUM(cashflow/POWER(1.0+@ytm,(DATEDIFF(month,@issue_date, valuta) * 30 + DAY(valuta)-DAY(@issue_date) 
   -
   CASE
    WHEN(@issue_date)>=30 AND DAY(valuta) = 31 
    THEN 1 
    ELSE 0 
   END) /360.0))
     FROM Cash_Flows)
 END
 RETURN @ytm 
END
GO

Da das Ganze ein Näherungsverfahren ist, hat man einen gewissen Spielraum bei der Wahl der oberen Grenze bei der Initialisierung der Variablen. 0,1 (=10%) ist idR ein guter Startwert. Danach läuft man solange durch die WHILE Schleife, bis die Abbruchbedingung erfüllt ist. Und hier erkennt man dann auch sehr schön den iterativen Ansatz dieses Verfahrens, für das dann andere Programmiersprachen wie C++ oder VB wesentlich besser geeignet sind. Aufgerufen wird die UDF dann mit

SELECT dbo.yield_to_maturity('20060115')
                  
----------------- 
.06182374295818

Rechnet man unser Beispiel mit einer spezialisierten Software nach wird man unter Umständen ab irgendwelchen Nachkommastellen Abweichungen bemerken. In der Regel betrifft dies die 6.te bis 8.te Nachkommastelle. Aber die fallen dann auch nicht mehr wirklich dramatisch ins Gewicht. Differenzen ab der 6.ten Nachkommastelle ignoriere ich einfach. Obwohl ich nicht wirklich weiß, woran dies liegt. Ich schiebe dies dann gerne auf die Implementierung des FLOAT Datentypen in SQL Server.

< rant mode on >
So, zum Schluß ein Wort in eigener Sache: Direkt in den Tagen nachdem ich in diesem Beitrag angekündigt hatte, eine T-SQL basierte Yield-to-maturity Formel zu veröffentlichen, habe ich gleich mehrere Mails von Leuten erhalten, die entweder genau wissen wollten, wann es denn soweit sei oder ob diese oder jene Besonderheit eingebaut sei und ob ich auch beabsichtige, noch weitere Renditemaße zu veröffentlichen. Ich habe zwar geahnt, daß diese Formel für viele vielleicht interessant sein könnte, war aber vollkommen von der teilweisen Dreistigkeit dieser Mails überrascht.
Ich möchte deshalb an dieser Stelle eines ganz klar stellen:

• Ich habe lange gezögert, ob ich nun diesen Beitrag veröffentlichen soll oder nicht, aber nun mache ich meine damals publizierte Ankündigung wahr. Nicht mehr, aber auch nicht weniger. Versprochen ist versprochen ... usw...
• Ich habe meinen Originalalgorithmus aber nach diesen Mails absichtlich noch weiter verschlankt, damit diejenigen Leute, die es offensichtlich selber nicht fertigbringen, diese Formel abzubilden, wenigstens noch einiges an eigenem Gehirnschmalz investieren müssen, um die Funktion tatsächlich praxistauglich zu machen.
• Ich werde keine weiteren Renditemaße veröffentlichen (was ich bis zu diesen Mails tatsächlich vorhatte).

Ich sehe nicht ein, daß ich hier über das hinaus, was ich eh schon mache, kostenlos Wissen vermittle, womit andere Leute potentiell eine Menge Geld verdienen können. Ich habe kein Problem damit, hier Algorithmen zu veröffentlichen, die Anderen Zeit, Nerven und Geld sparen. Ganz im Gegenteil! Aber diese Mails gaben mir das Gefühl, nur ein Ideenlieferant zu sein, den man ausnutzen kann. Aus diesem Grund schiebe ich dem einen Riegel vor, und behalte mein Wissen in diesem Bereich ab sofort für mich.

Sorry, aber mal wieder haben einige schwarze Schafe der Gesamtheit die Suppe versalzen!
< rant mode off >

JOIN Hints können in einer Abfrage verwendet werden, um den JOIN Typ festzulegen, den der Query Optimiser für den Ausführungsplan verwenden soll: Folgende JOIN Optionen stehen zur Verfügung:
- Loop
- Merge
- Hash

Die Syntax für einen JOIN Hint ist (am Beispiel eines INNER JOINs):

FROM tabelle_1 INNER (LOOP | MERGE | HASH) JOIN tabelle_2

Hier ist ein Beispiel:

FORM header_tabelle INNER LOOP JOIN detail_tabelle

Wie man sehen kann, wird der JOIN Hint zwischen der Angabe des JOIN Typs (hier der INNER) und dem JOIN Schlüsselwort. Nur ein JOIN Hint kann zu einer Zeit pro JOIN in einer Abfrage verwendet werden. Ferner können JOIN Hints nur verwenden werden zusammen mit der ANSI JOIN Syntax, nicht mit der älteren Microsoft JOIN Syntax.

Die obige Syntax ist nicht die einzige Möglichkeit, um einen JOIN Hint in einer Abfrage zu verwenden. Man kann auch die OPTION Klausel verwenden. Diese bewirkt, daß der Hint während der gesamten Abfrage verwendet wird. Es können mehrere Hints in der OPTION Klausel auftauchen, jeder Hint jedoch nur genau 1x. Es gibt nur eine OPTION Klausel pro Abfrage.

Hier ist ein Beispiel der Verwendung der OPTION Klausel:
OPTION (INNER) oder OPTION (MERGE) oder OPTION (HASH)

Welche Methode SQL Server verwendet, um Tabellen zu joinen, hängt von zahlreichen Einzelfaktoren ab. In der Mehrzahl der Fälle wird man aber einen Nested Loop Join beobachten. Bevor man nun aber hingeht, und versucht, den JOIN durch Einsatz eines Hints zu beeinflußen, sollte man immer erst die zugrundeliegende Abfrage und die Indexes auf den zugrundeliegenden Tabellen betrachten und versuchen, diese zu optimieren, bevor man SQL Server durch den Hint in seinem Spielraum einengt und die Verarbeitungsmethode starr vorgibt. Unter Umständen kann man durch den Einsatz des Hints die Performance in den Keller ziehen, wenn z.B. unter geänderten Rahmenbedingungen der Hint nicht mehr angebracht ist; SQL Server jedoch gezwungen ist, diesen zu beachten. Von daher sollte man stets vorher unter realistischen Bedingungen testen, ob der Hint das bringt, was man erwartet.

*****

Tips und Tricks, die die Performance von Trigger erhöhen können:

Die Zeit, die ein Trigger zur Ausführung benötigt, ist eine Funktion der Anzahl der innerhalb des Triggers referenzierten Tabellen und der Anzahl der vom Trigger Code betroffenen Zeilen. Daher sollte man stets bestrebt sein, die Anzahl der im Trigger involvierten Tabellen und die Anzahl der betroffenen Zeilen zu minimieren.

Zusätzlich sollte der Code eines Triggers auf ein Minimum reduziert werden, um Overhead zu vermeiden. Dies ist wichtig, da Trigger typischerweise durch INSERT's, UPDATE's und DELETE's ausgelöst werden, die in OLTP Applikation häufig vorkommen. Je mehr Code in einem Trigger ausgeführt werden muß, umso langsamer ist jedes INSERT, UPDATE und DELETE, das stattfindet.

*****

Falls der Trigger eine WHERE Klausel beinhaltet, sollte man nicht vergessen, einen geeigneten Index zu erstellen, der verwendet werden kann. WHERE Klauseln, die sich in Triggern verstecken, werden leicht übersehen, aber wie jedes andere SELECT Statement können auch sie signifikant durch einen geeigneten Index beschleunigt werden.
Ein Weg um dies zu testen ist, den Trigger Code im Query Analyzer laufen zu lassen und den daraus resultierenden Ausführungsplan zu untersuchen. Dadurch kann man sehr schnell feststellen, ob man einen geeigneten Index hinzufügen muß oder nicht.

*****

Hat man ein INSERT, UPDATE oder DELETE Statement daß länger zu benötigen scheint als angemessen, sollte man überprüfen, ob ein Trigger auf der beteiligten Tabelle definiert ist. Das Performance Problem, das man beobachtet, könnte sehr wohl auf den Trigger zurückzuführen zu sein, nicht auf das DML Statement selber.
Man sollte nicht vergessen, Trigger Code genauso zu optimieren wie man es mit jedem anderen Code auch macht. Aufgrund der Tatsache, daß Trigger Code im Hintergrund läuft, scheinen viele Leute zu vergessen, daß der Code existiert, und potentiell für Performance Probleme verantwortlich sein kann.
Man kann Profiler und Query Analyzer verwenden, um herauszufinden, wie die Trigger in einer Datenbank arbeiten.

*****

Verwenden Sie keinen Trigger, um referenzielle Integrität sicherzustellen, wenn Sie die Option haben, auf die in SQL Server eingebauten Funktionen zur Sicherstellung referenzieller Integrität zurückzugreifen. Die Verwendung der hoch optimierten eingebauten referenziellen Integrität ist deutlich schneller als die Verwendung eines Triggers und interpretiertem Code, um die gleiche Aufgabe zu erfüllen.

*****

Wenn Sie die Wahl haben zwischen einem Trigger und einer CHECK Constraint, um Regeln oder Standards in der Datenbank sicherzustellen, sollte man sich generell für die CHECK Constraint entscheiden, da sie schneller sind als Trigger, wenn sie die gleiche Aufgabe erfüllen sollen

*****

Man sollte es vermeiden, einen Trigger zurückrollen zu müssen aufgrund des verursachten Overheads. Anstelle den Trigger ein Problem finden zu lassen und evtl. eine Transaktion zurückrollen zu müssen, sollte man den Fehler vorher versuchen abzufangen, falls das aufgrund des involvierten Codes möglich ist. Einen Fehler abzufangen (bevor der Trigger ausgelöst wird), verbraucht wesentlich weniger Server Resourcen als den Trigger zurückrollen zu müssen.

*****

Manchmal erscheint es aus Performancegrunden angebracht, denormalisierten Daten zu halten. Beispielsweise mag man aggregierte ( so z.B. kumulierte Daten) in einer Tabelle halten, da es einfach zu zeitaufwendig ist, sie on-the-fly innerhalb des SELECT Statements zu generieren. Ein Weg, diese denormalisierten Daten zu pflegen, ist die Verwendung von Triggern. Beispielsweise könnte ein Trigger immer dann ausgelöst werden, wenn ein neuer Verkauf zur Verkaufstabelle hinzugefügt wird und den Gesamtwert dieses Verkaufs zu einer Gesamtverkaufstabelle hinzufügen.

*****

Der Code, der in einem UPDATE Trigger enthalten ist, wird jedesmal ausgeführt, wenn seine zugrundeliegende Tabelle upgedatet wird. In den meisten UPDATE Triggern, betrifft der Code des Triggers nur einige wenige Spalten, nicht alle. Darum ist es nicht sinnvoll (und eine Verschwendung von SQL Server Resourcen) den gesamte Code des Triggers auszuführen, auch wenn die Spalten, an denen man ursprünglich interessiert war, überhaupt nicht aktualisiert worden sind. Mit anderen Worten, auch wenn eine Spalte aktualisiert wird, an der man nicht interessiert ist, wird der UPDATE Trigger ausgelöst und der Code ausgeführt.
Um nun die unnötige Ausführung von Code in einem UPDATE Trigger zu vermeiden, kann man eine der beiden folgenden Funktionen einsetzen: UPDATE() (verfügbar in SQL Server 2000) und COLUMNS_UPDATED() (verfügbar in SQL Server 7.0 und 2000).
Jede dieser beiden Funktionen kann verwenden werden, um zu testen, ob eine bestimmte Spalte sich verändert hat oder nicht. Daher kann man nun Code in seinem Trigger schreiben, der genau dies überprüft und falls sich diese Spalte nicht verändert hat, der Code halt nicht ausgeführt wird. Dies reduziert die Arbeit, die der Trigger ausführen muß und verbessert die allgemeine Performance der Datenbank.
Die UPDATE() Funktion wird verwendet, um die Veränderung einer einzelnen Spalte zu einer Zeit zu überprüfen. Die COLUMNS_UPDATED Funktion kann verwendet werden, um gleichzeitig mehrere Spalten zu überprüfen.

*****

Falls Sie kaskadierende referentielle Integrität (zum Beispiel kaskadierende DELETEs) in SQL Server 2000 Datenbanken, verwenden Sie kaskadierende referentielle Integritäts Constraints anstelle von Triggern, die die kaskadierenden DELETEs ausführen, da Constraints wesentlich effizienter sind und dadurch die Performance verbessern können. Falls man ältere (7.0 oder früher) Applikationen hat, die man auf SQL Server 2000 portiert hat und die Trigger benutzen, um kaskadierende DELETEs, sollte man in Erwägung ziehen, die Trigger, falls möglich zu entfernen und kaskadierende referentielle Integrität zu verwenden.

*****

Während INSTEAD OF trigger technisch identisch mit AFTER Triggern sind, liegt der Hauptgrund für die Verwendung von INSEAD OF Trigger darin, daß man mit ihnen bestimmte View Typen aktualisieren kann. Was bedeutet dies nun im Hinblick auf Performance? Unter der Annahme, daß die meisten, der von Ihnen geschriebenen Trigger, nur selten Transaktionen zurückrollen, kann man getrost weiterhin AFTER Trigger verwenden. Der Overhead eines INSTEAD OF Triggers ist größer als der eines AFTER Trigers. Ist es hingegen an der Regel, daß ROLLBACKs durchgeführt werden (mehr als in 50% aller Fälle), dann kann ein INSTEAD OF Trigger die bessere Alternative sein, da das ROLLBACK eines INSTEAD OF Triggers weniger Overhead verursacht als das eines AFTER Triggers. Also sollte man die meiste Zeit konventionelle AFTER Trigger verwenden, und sich die INSTEAD OF Trigger sparen, um damit Views upzudaten.

*****

SQL Server 2000 ermöglicht in gewissem Maße, Kontrolle über die Reihenfolge in der Trigger auszuüben. In gewissem Maße heißt, daß man angeben kann, welcher Trigger als Erster und welcher als Letzter ausgeführt wird. hat man jedoch mehr als zwei Trigger für eine Tabelle definiert, hat man keine Kontrolle über die Reihenfolge, in der die restlichen ausgeführt wird.
So, wie nun kann die Selektion der Reihenfolge, in der die Trigger ausgeführt werden, dabei helfen, die Performance einer Applikation zu verbessern? Um Trigger Performance zu optimieren, sollte man denjenigen Trigger, bei dem ein ROLLBACK am wahrscheinlichsten (aus welchem Grund auch immer), als ersten auszuführenden Trigger spezifizieren. Auf diese Weise ist nur dieser eine Trigger betroffen, wenn ein ROLLBACK durchgeführt werden muß.
Mal angenommen, man hat drei Trigger auf einer Tabelle definiert, aber anstelle dem am wahrscheinlichsten als ersten zu definieren, sei dieser als letzter definiert. Wird in diesem Szenario nun ein ROLLBACK ausgelöst, dann müssen alle drei Trigger. Wäre dieser Trigger aber als Erster definiert worden, hätte nur dieser Trigger zurückgerollt werden müssen. Verringert man die Anzahl der Trigger, die zurückgerollt werden müssen, reduziert man SQL Server's Overhead und verbessert die Performance.

oder die Frage, wie berechne ich das Produkt einer Spalte einer Tabelle und warum gibt es eigentlich keine PROD() Aggregatfunktion...

Die Vorgeschichte zu diesem Beitrag:
Wir erhalten immer wieder Anrufe von irgendwelchen Vertrieblern, die uns in den buntesten Farben ausmalen, welche tolle Investmentangebote sie haben, welche astronomische Rendite erzielbar sind und warum wir unbedingt in diese Produkte investieren sollen. Meist erledigen sich solche Anrufe recht schnell von ganz alleine, aber hin und wieder ist es ganz amüsant, mal näher hinzuhören. Neulich pries so Jemand ein Produkt an, welches "wie eine Rakete" in den letzten 6 Monaten 40% zugelegt hätte und in den letzten 5 Jahren immerhin noch eine bemerkenswerte Rendite von 8% per annum aufweisen könne. Als auf meine Frage, ob diese Performance gemäß anerkannter Performance Presentation Standards ermittelt wurde, keine zufriedenstellende Antwort zurückkam, war auch dieses Gespräch schnell beendet. Dennoch blieb irgendwie bei mir die Frage hängen, wie würde man die Performance einer Geldanlage im SQL Server ermitteln? Und, vielleicht noch wichtiger, wie würde man es richtig machen?

Ausgangsszenario:
Wir investieren Anfang 2000 100,- €. In den nachfolgenden Jahren entwickelt sich der Wert unserer Anlage gemäß folgender Übersicht

Wenn man jetzt das 5-Jahres-Mittel errechnet, ergibt sich (-0,5+0,6+0,15+0,4+-0,25)/5 = 0,08 => 8,0% pro Jahr innerhalb dieser 5 Jahre. Ein lukratives Investment könnte man meinen. Und mathematisch fehlerfrei nachgewiesen. ;-)
Stimmt, nur verschleiert die Verwendung des arithmetischen Mittels bei der Angabe der Performance hier die Tatsache, daß wir Geld verloren hätten, wenn wir in diese Anlage investiert hätten. Beweis? Okay!

So, aus unserem Startkapital von 100 € sind in 5 Jahren 96,6 € geworden. Dies ist die traurige Wirklichkeit hinter der Hochglanzfassade. Und genau diese Wirklichkeit wird durch das arithmetische Mittel verschleiert. Zum Einsatz kommen muß hier das geometrische Mittel. Dieses bezogen auf unser Beispiel ergibt sich als ((0.5 * 1,6*1,15*1,4*0,75) ^ 1/5) - 1 = -0,0069...
Die Rendite über die gesamte Laufzeit daraus beträgt ~ -3,4%.
So, genug der Finanzmathematik. Wie könnte eine Lösung in T-SQL aussehen?
Hm, zunächst mal mag man sich an dieser Stelle beklagen, warum es keine PROD() Aggregatfunktion gibt. Alles wäre so einfach, wenn man, ähnlich wie SUM(), eine eingebaute Funktion hätte, welche die Werte einer Spalte einer Tabelle miteinander multipliziert. Aber nein...

Also muß man selber tätig werden.

IF OBJECT_ID('tempdb.#t') >0 
 DROP TABLE #t

CREATE TABLE #t
(
 fiscal_year INT
 , fund_return FLOAT
)
INSERT INTO #t VALUES(2000, -.5)
INSERT INTO #t VALUES(2001, 0.6)
INSERT INTO #t VALUES(2002, .15)
INSERT INTO #t VALUES(2003, 0.4)
INSERT INTO #t VALUES(2004, -.25)

ist die Ausgangsbasis. Die erste Lösung, die naheliegen könnte, ist der Einsatz eines Cursor, der durch die einzelnen Zeilen läuft und die Multiplikation vornimmt.

DECLARE @fund_return FLOAT
DECLARE @result FLOAT
SELECT @fund_return = 1, @result = 1

DECLARE slash_cursors CURSOR FOR 
 SELECT fund_return
   FROM #t

OPEN slash_cursors
FETCH NEXT FROM slash_cursors  INTO @fund_return
WHILE @@FETCH_STATUS = 0
BEGIN
 SET @result = (1+@fund_return) * @result
 FETCH NEXT FROM slash_cursors  INTO @fund_return
END

SELECT 
 (POWER(@result,1.0/(SELECT COUNT(*) FROM #t))-1) * 100 AS [Etwas anderes als die propagierten 8,0%]
 , 100 + ((POWER(@result,1.0/(SELECT COUNT(*) FROM #t))-1) * 100) *
 (SELECT COUNT(*) FROM #t) AS [Was aus 100 € in 5 Jahren wurde]

CLOSE slash_cursors
DEALLOCATE slash_cursors
GO

Etwas anderes als die propagierten 8,0%               Was aus 100 € in 5 Jahren wurde 
----------------------------------------------------- ------------------------------- 
-0.68944126856026466                                  96.552793657198677

(1 row(s) affected)

So, nun haben wir erst einmal unser Ergebnis und können direkt eine Performanceoptimierung durch Eliminierung des Cursors angehen. Dazu transformieren wir die Tabelle und machen so aus 5 Zeilen und 1 Spalte 1 Zeile mit 5 Spalten.

SELECT 
 (POWER((x.t1*x.t2*x.t3*x.t4*x.t5), 1.0/(SELECT COUNT(fund_return) FROM #t)) - 1) 
 * 100 AS [Etwas anderes als die propagierten 8,0%]
 , 100 + ((POWER((x.t1*x.t2*x.t3*x.t4*x.t5), 1.0/(SELECT COUNT(fund_return) FROM #t)) - 1) * 100) * 5
   AS [Was aus 100 € in 5 Jahren wurde]
 FROM
 (SELECT
  MAX(CASE fiscal_year WHEN 2000 THEN 1+fund_return ELSE NULL END) AS t1
  , MAX(CASE fiscal_year WHEN 2001 THEN 1+fund_return ELSE NULL END) AS t2
  , MAX(CASE fiscal_year WHEN 2002 THEN 1+fund_return ELSE NULL END) AS t3
  , MAX(CASE fiscal_year WHEN 2003 THEN 1+fund_return ELSE NULL END) AS t4
  , MAX(CASE fiscal_year WHEN 2004 THEN 1+fund_return ELSE NULL END) AS t5
  FROM #t) x

Etwas anderes als die propagierten 8,0%               Was aus 100 € in 5 Jahren wurde 
----------------------------------------------------- ------------------------------- 
-0.68944126856026466                                  96.552793657198677

(1 row(s) affected)

Warning: Null value is eliminated by an aggregate or other SET operation.

So, den Cursor sind wir los, aber wirklich zufriedenstellen kam diese Lösung auch nicht, da sie zu unflexibel und starr ist. Mit dieser Konstruktion eine Performance über 30 Jahre zu ermitteln, ergibt ein ziemlich monströses CASE. Und auch in der Handhabung des Zeitraumes ist man starr und unflexibel, da dieser hartkodiert ist. Dies kann man zwar durch den Einsatz einer Variablen anstelle der fest eingegebenen Jahreszahlen vermeiden, ist aber, im Vergleich zu nächsten Variante, nicht wirklich prickelnd. Die letzte Variante profitiert von einer Eigenschaft des geometrischen Mittels. Und zwar kann man das geometrische Mittel auch darstellen, als e potenziert mit dem Durchschnitt der logarithmierten Einzelwerte.

SELECT (EXP(AVG(LOG(1+fund_return)))-1)  * 100 AS [Etwas anderes als die propagierten 8,0%]
 , 100 + (100 * (EXP(AVG(LOG(1+fund_return)))-1)  * COUNT(*)) AS [Was aus 100 € in 5 Jahren wurde]
  FROM #t
  
Etwas anderes als die propagierten 8,0%               Was aus 100 € in 5 Jahren wurde 
----------------------------------------------------- ------------------------------- 
-0.68944126856026466                                  96.552793657198677

(1 row(s) affected)

Was noch erwähnenswert ist, ist die Tatsache, daß der natürliche Logarithmus nur für Zahlen größer 0 definiert ist. Der Versuch, 0 oder eine negative Zahl zu verwenden, resultiert in einen Domänenfehler. Warum dies so ist, steht unter anderem hier.
Für unser Beispiel würde ein Domänenfehler genau dann auftreten, wenn ein Wert -1 wäre. Dies wäre dann auch gleichbedeutend mit einem Totalverlust des eingesetzten Kapital. Ist zwar sehr unwahrscheinlich, aber eine gute Implementierung sollte dies berücksichtigen und abfangen.

In den guten alten Tagen als Computer noch kaum vorhanden und noch weniger leistungsfähig waren, entwickelte sich die deutsche Zinsberechnungsmethode. Die Hauptbesonderheit dieser Methode besteht darin, daß jeder Monat fiktiv 30 Tage hat. Egal, wie viele Tage dieser Monat tatsächlich hat. Das Jahr hatte 360 Tage, egal ob Schaltjahr oder nicht. Dies vereinfachte natürlich die Zinsberechnungen, die damals manuell vorgenommen wurden, enorm. Die Ungleichmäßigkeiten dieser Methode sind mehr oder weniger signifikant. Gerade bei kürzeren Laufzeiten von Wertpapieren wirken sie sich jedoch deutlicher aus als bei längeren.

Mit Einführung des Euros hat man sich entschlossen, auf die ISMA-Regel 251 umzustellen. Diese Regel bezeichnet man auch als act/act Zinsberechnungsmethode. Sie berücksichtigt die tatsächliche Anzahl der Tage. Sowohl im Zähler als auch im Nenner. Mit Beginn der Doppelwährungsphase wurden viele der alten Wertpapieremissionen, die noch mit 30/360 berechnet wurden, Zug um Zug auf die neue Methode umgestellt. Neuemissionen auf dem Kapitalmarkt im Euroland finden in aller Regel heute nur noch mit act/act Regelung statt. Nichtsdestotrotz ist die 30/360 Regel in vielen (ausländischen) Märkten und Marktsegmenten noch präsent. Der amerikanische Kapitalmarkt wendet auch heute noch diese Regeln an. Allerdings mit einer Abweichung zur deutschen Regelung. In den USA wird der 31. eines Tages unter bestimmten Umständen mitgezählt. Die Amerikaner behandeln auch den Februar etwas anders, aber da wir im folgenden diese amerikanische Variante komplett aussen vor lassen interessieren uns diese Besonderheiten jetzt nicht. Wir sprechen hier nur von der deutschen Variante.

Frage: Wie viele Tage liegen zwischen dem 01.11.2005 und dem 01.12.2005?
Antwort: 30

Frage: Wie viele Tage liegen zwischen dem 01.11.2005 und dem 01.01.2006?
Antwort: 60

Frage: Wie viele Tage liegen zwischen dem 28.02.2005 und dem 01.03.2005?
Antwort: 3

Bevor jetzt jemand aufschreit. Stop, wir rechnen jeden Tag fiktiv mit 30 Tagen ab. Also hat auch der Februar 30 fiktive Tage. Somit liegen der 28.02.2005, der 29.02.2005 und der 30.02.2005 gemäß 30/360 Regelung zwischen beiden Tagen und somit ist 3 die richtige Antwort.

Dies ist etwas gewöhnungsbedürftig, aber korrekt. Vielleicht noch ein abschließendes Beispiel.

Frage: Wie viele Tage liegen zwischen dem 30.12.2005 und dem 31.12.2005?
Antwort: 0

Auch hier erst einmal in Ruhe nachdenken, bevor man protestiert. Gemäß unserer Konvention hat jeder Monat 30 Tage. Bei den Monaten mit 31 Tagen wird der 31. künstlich eliminiert. Somit reduziert sich diese Frage auf die Anzahl der Tage, die zwischen dem 30.12.2005 und dem 30.12.2005 liegen. Offensichtlich lautet die richtige Antwort 0.

Genug der Beispiele. Wir suchen nun also einen Algorithmus der dies abbildet.

DECLARE @dt_Start DATETIME
DECLARE @dt_Ende DATETIME
SELECT @dt_Start = '20051101', @dt_Ende = '20051201'
SELECT
    DATEDIFF(MONTH, @dt_Start, @dt_Ende) * 30
    + DAY(@dt_Ende)
    - DAY(@dt_Start)
    - CASE WHEN DAY(@dt_Ende) = 31 THEN 1 ELSE 0 END AS [Tage30/360] 

Tage30/360
-----------
30

(1 row(s) affected)

Betrachten wir einmal etwas näher dieses Statement.

Wir multiplizieren die Anzahl der Monate zwischen Ausgangs- und Enddatum mit 30 und eliminieren damit nahezu sämtliche Probleme mit den tatsächlichen Monatstagen. Anschließend addieren wir einfach die Tage des Enddatums hinzu und ziehen die Tage des Startdatums wieder ab. Auf diese Weise eliminieren wir das Problem, wenn das Startdatum nicht auch gleich einem Monatsanfang ist. Zum Schluß müssen wir noch das Problem adressieren wenn das Enddatum auf einen 31. fällt. Dies erfolgt durch den CASE Ausdruck, der bei Bedarf nochmal einen Tag subtrahiert.

Vielleicht noch einige Anmerkungen zur generellen Methodik. Vielfach kann man die wirresten Aussagen lesen, welche Tage nun verzinst werden und welche nicht mehr mitverzinst werden. Wird direkt der 1 Tag inklusive gerechnet oder nicht? Und was ist mit dem letzten Tag? Fakt ist, daß auf keinen Fall beide Tage eingerechnet werden.

Und zumindest im Wertpapiergeschäft ist es üblich, den 1. Tag einer Zinsperiode mitzuverzinsen, während der letzte Tag nicht mitverzinst wird. Dies mag in anderen Bereichen, wie bei der Berechnung von Verzugszinsen oder KK Krediten anders sein, wer aber dies auch für den Wertpapierbereich behauptet, mag sich mal in Ruhe Wertpapierurkunden von Banken durchlesen. Diese sind selbst für juristische Laien unzweideutig. Beginnend mit der Valutierung  (einschließlich) bis zum Tag der nächsten Kuponzahlung (ausschließlich) werden Zinsen gerechnet.

Eigentlich ganz einfach und auch logisch, aber immer wieder ein beliebtes Thema bei der Ermittlung von Zinsabgrenzungen im Rahmen des Jahresabschlusses. Wir hatten jahrelang Spaß mit unserer Wirtschaftsprüfungsgesellschaft. Da mit Ausnahme des Prüfungsleiters das Team fast jedes Jahr wechselte, konnte man dies den neuen Prüfern immer wieder erklären.

Warum dieser Beitrag, die deutsche Zinsberechnungsmethode spielt doch, gerade im europäischen Wertpapierhandel, kaum noch eine Rolle?

Nun, in den letzten Tagen habe ich mich entschlossen, meine T-SQL Implementierung der Yield-to-maturity (YTM) Berechnung als Beitrag zu veröffentlichen. Ein Faktor bei der YTM Bestimmung ist die anzusetzende Zinsberechnungsmethode, zu der dieser Beitrag das grundlegende Verständnis bilden soll. Die demnächst veröffentlichte YTM Variante wird allerdings etwas "abgespeckt" sein. Sie wird nur die YTM auf Basis 30/360 beinhalten. Die Implementierung der anderen Zinsberechnungsmethode, wie act/act, act/360 oder act/365 überlasse ich dem interessierten Leser.

Aus den Reaktionen auf meine ersten Beiträgen aus der Reihe "Finanzmathematik" weiß ich, daß es einige Leute gibt, die anscheinend mächtig scharf darauf sind, einen solchen Algorithmus in die Finger zu bekommen. Da ich aber keine Lust habe, sämtliche Arbeit diesen Leuten abzunehmen, werde ich nur das Skelett liefern, das Filet Mignon wird sich dann jeder selber basteln müssen. Ich halte es dennoch für einen äußerst fairen Deal. ;-)

Man sollte auch in der Lage sein, die Beispiel mit der Excel Funktion TAGE360 nachzurechnen. So sollte

=TAGE360(Startdatum;Enddatum;WAHR)

identische Ergebnisse liefern. Ich selber arbeite mit einer finanzmathematischen Formelsammlung und habe meine Ergebnisse mit den Ergebnissen der entsprechenden Formel aus dieser Bibliothek verglichen. Diese Ergebnisse stimmen überein. Und liest man sich die Referenzliste dieses Softwarelieferanten durch, rechnet fast die gesamte Finanzwelt Deutschlands mit diesem Programm. Zu diesem Who-is-who gehören die Deutsche Bundesbank, das Bundesfinanzministerium und nahezu sämtliche Großbanken. Zwar verifiziert dies nicht die Richtigkeit des Algorithmus, aber es ist zumindest tröstlich, das im Falle eines Falles alle gleichermaßen falsch rechnen würden. :-)

Warum diese besondere Betonung auf die Korrektheit der Ergebnisse? Nun, gerade in Excel Communities stößt man häufig auf inkorrekte, falsche und/oder unvollständige Aussagen zu der TAGE360 Formel.