Programmierern aus der Welt der objektorientierten oder prozeduralen Sprachen dürfte es beim Blick in so manche Microsoft-SQL-Datenbank die Haare zu Berge stehen lassen. Die gleiche Programmlogik wird häufig immer und immer wieder geschrieben und dies an verschiedensten Stellen in der Datenbank. Das ist umso erstaunlicher, weil es bereits seit der SQL-Server-Version 2005 sogenannte benutzerdefinierte Funktionen gibt, die genau dies verhindern sollen. Warum also werden diese nicht verwendet? Die Ursache ist ihre teilweise katastrophale Performance. Nutzt man diese Skalarwertfunktionen zur Zentralisierung des Quellcodes muss man wohl oder übel ein schlechtes Laufzeitverhalten in Kauf nehmen. Von daher hat man die Wahl zwischen Pest oder Cholera – saubere Architektur oder gute Performance.
Zum Glück ist aber auch die Datenbankwelt nicht ganz so schlecht wie es auf den ersten Blick scheint. Mit ein paar wenigen Handgriffen kann man tatsächlich den Quellcode zentralisieren und das bei sehr gutem Laufzeitverhalten. Wie das geht, zeigen wir in folgendem Blogbeitrag.
Die Testumgebung
Schaffen wir uns zunächst eine Testumgebung mit zwei Testtabellen, um unsere Behauptungen zu untersuchen.
In der ersten, einspaltigen Testtabelle „T_FunctionTest“ fügen wir 10.000 Datensätze mit einem beliebig zusammengesetzten Text ein. Dies könnte „im echten Leben“ beispielsweise eine Produktnummer oder ähnliches sein. Die zweite Tabelle „T_FunctionTest_Counter“ enthält pro Textbaustein je zwei Datensätze mit einem Zähler, in Summe also 20.000 Datensätze. Dies könnten in einer Realumgebung beispielsweise Produktpreise sein. Folgendes Skript erledigt die Aufgabe für uns:
--(Re-)Create test tables
IF object_id('T_FunctionTest') IS NOT NULL DROP TABLE T_FunctionTest
go
CREATE TABLE T_FunctionTest
(Col1 VARCHAR(200))
go
INSERT T_FunctionTest(Col1)
SELECT REPLICATE(' ', CAST(RAND() AS decimal(1,0))) +
CAST(NEWID() AS VARCHAR(50)) +
REPLICATE(' ', CAST(RAND() AS decimal(1,0)))
FROM (SELECT TOP 10000 sv.number * sv2.number AS number
FROM master..spt_values sv
CROSS JOIN master..spt_values sv2
WHERE sv2.number <= 1
AND sv.[type] = 'P') AS a
go
--SELECT * FROM T_FunctionTest
--go
IF object_id('T_FunctionTest_Counter') IS NOT NULL DROP TABLE T_FunctionTest_Counter
go
SELECT *
INTO T_FunctionTest_Counter
FROM T_FunctionTest
,(SELECT 1 Col2 UNION ALL SELECT 2 col2 /*UNION ALL SELECT 3 col2 UNION ALL SELECT 4 col2 UNION ALL SELECT 5 col2*/) Rec
go
--SELECT * FROM T_FunctionTest_Counter
--go
Nehmen wir weiter an, wir wollen zu unseren Produkten den jeweils höchsten in der Vergangenheit erzielten Preis ermitteln. Wir suchen aus den Testtabellen also zu einem bestimmten Produkt den maximalen Preis. Als Preis nehmen wir für den Test einfach den Datensatzzähler. Die einfache Funktion sieht folgendermaßen aus:
--(Re-)Create scalar valued function
IF object_id('FSV_MaxCount') IS NOT NULL DROP FUNCTION FSV_MaxCount
go
CREATE FUNCTION FSV_MaxCount(@Col1ID varchar(250))
RETURNS int
AS
BEGIN
DECLARE @RetVal int
SELECT @RetVal = max(Col2)
FROM T_FunctionTest_Counter
WHERE Col1 = @Col1ID
RETURN @RetVal
END
go
Die Logik wird also in einer Funktion gekapselt und nicht kreuz und quer im Quellcode verteilt.
Testen wir nun die Funktion und suchen uns zu jedem der Produkte in Tabelle T_FunctionTest den maximalen Zähler aus T_FunctionTest_Counter erleben wir die Ernüchterung.
Folgende Abfrage benötigt auf unserem Rechner ca. 20,5 Sekunden (CPU-Zeit 20,1 s):
--Clear Cache DBCC freeproccache DBCC dropcleanbuffers ---Scalar SELECT dbo.FSV_MaxCount(col1) ColRet FROM T_FunctionTest
Das ist für diese mageren Tabellen natürlich völlig inakzeptabel! Was also tun?
Wer braucht schon Objektorientierung?
Die echten Raubeine unter den Datenbankprogrammierern würden nun vermutlich einfach die Objektorientierung durch Performanceorientierung substituieren und die Abfrage als Unterabfrage definieren. Dies ist ja bekanntlich der schnellst mögliche, wenn auch uneleganteste, Ausführungsweg:
--Clear Cache DBCC freeproccache DBCC dropcleanbuffers ---Inline SELECT (SELECT max(Col2) MaxCount FROM T_FunctionTest_Counter WHERE Col1 = ft.Col1) MaxCount FROM T_FunctionTest ft
Und tatsächlich in knapp 170 Millisekunden (CPU-Zeit 80 ms) ist das Abfrageergebnis da. Das muss also unser Benchmark für alle folgenden Betrachtungen sein.
Ein bisschen Theorie muss sein
Wo genau ist nun der Unterschied zwischen beiden Abfragetypen und was passiert „unter der Haube“? Um dies herauszufinden bedienen wir uns eines netten Assistenten im SQL Server, dem sogenannten Profiler. Diesen starten wir und aktivieren die Option „SQL:BatchCompleted“.
Dann führen wir beide Abfragen noch einmal aus (bei vorher geleertem Cache). Das Ergebnis sollte ungefähr folgendermaßen aussehen:
Neben den sehr deutlichen Abweichungen in der CPU- und Gesamtlaufzeit fällt noch die Spalte „Reads“ ins Auge. Dabei handelt es sich um die Anzahl der logischen Lesezugriffe auf die jeweilige Festplatte. Die Anzahl der Lesevorgänge ist bei Nutzung der Funktion fast 6.000 Mal höher als bei der grobschlächtigen Variante! Das ist natürlich immens. Der IO auf unserer Festplatte wird zum Engpass. Der Grund hierfür liegt in der internen Verarbeitung der Skalarwertfunktionen durch den Abfrageoptimierer des SQL Servers. Dieser kann die Funktionsaufrufe nicht sauber optimieren. Die Folge: die erstellte Funktion wird für jede der 10.000 Zeilen einmal aufgerufen und gegen die Datenbank abgesetzt. Da innerhalb der Funktion eine weitere Abfrage auf eine Tabelle durchgeführt wird, vervielfacht sich die Anzahl der logischen Lesevorgänge massiv. Bei der Inline-Variante funktioniert die Optimierung wesentlich besser. Die Counter-Tabelle wird dabei nur ein einziges Mal abgefragt und anschließend mit der anderen Tabelle vereinigt. Die notwendigen Lesevorgänge sind hier minimal. Wie bekommen wir jetzt aber beide Vorteile zusammen?
Die Lösung heißt „Tabellenwertfunktion“. Der SQL Server unterscheidet bei den benutzerdefinierten Funktionen nämlich zwischen Skalarwertfunktionen (diese Variante haben wir oben angelegt) und Tabellenwertfunktionen. Diese Unterscheidung wird auch deutlich, wenn man im Management-Studio den Order „Funktionen“ aufklappt. Dort existiert zu beiden Typen je ein Unterordner. Die unterschiedliche Benennung kommt vom jeweiligen Rückgabetyp der Funktionen. Eine liefert einen skalaren Wert zurück, die andere eine ganze Tabelle. Tabellenwertfunktionen können auch tatsächlich wie Tabellen hinter einem FROM-Befehl verwendet werden (Achtung, auch hier an das Schema vor dem Funktionsnamen denken). Bemüht man zu dem Thema die Google-Suchmaschine sind sich auch die Gelehrten einig, dass Tabellenwertfunktionen den Skalarwertfunktionen möglichst vorzuziehen sind. Es sei denn bei den Skalarwertfunktionen handelt es sich um sehr einfache Konstruktionen (z.B. einfache Konvertierungen oder Textoperationen).
In unserem vorliegenden Beispiel genügen wenige Handgriffe, um die Skalarwertfunktion durch eine Tabellenwertfunktion zu ersetzen.
Abfrageturbo Tabellenwertfunktion?
Zunächst erstellen wir eine solche Tabellenwertfunktion. Die Syntax ist ähnlich zu einer Skalarwertfunktion, allerdings wird als Rückgabetyp eben nicht ein fixer Wert definiert, sondern eine ganze Tabelle. Diese ist ähnlich eines Create-Table-Befehls genau zu spezifizieren:
--(Re-)Create table valued function (Multi-statement)
IF object_id('FTV_MaxCount') IS NOT NULL DROP FUNCTION FTV_MaxCount
go
CREATE FUNCTION FTV_MaxCount(@Col1ID varchar(250))
RETURNS @RetTab TABLE
(
MaxCount int NOT NULL
)
AS
BEGIN
INSERT INTO @RetTab (MaxCount)
SELECT max(Col2) MaxCount
FROM T_FunctionTest_Counter
WHERE Col1 = @Col1ID
RETURN
END
GO
Etwas ungewöhnlich ist schließlich der Aufruf der Funktion. Intuitiv würde man erstmal einen JOIN versuchen, was jedoch in allen mir bekannten Fällen fehlschlägt. Erfolgversprechend ist lediglich das Einbinden der Funktion als SUB-SELECT in dem äußeren SELECT-Befehl:
--Clear Cache DBCC freeproccache DBCC dropcleanbuffers ---Table (Multi statement) SELECT (SELECT MaxCount From dbo.ftv_MaxCount(Col1)) ColRet FROM T_FunctionTest
Die Ausführung der Abfrage holt uns aber schnell wieder auf den Boden der Tatsachen zurück. Die Abfrage dauert mit 21,3 Sekunden (CPU-Zeit 21 s) sogar noch länger als der erste Versuch mit der Skalarwertfunktion?! Täuscht sich also die ganze Internetgemeinde? Nicht ganz!
Tabellenwertfunktion <> Tabellenwertfunktion
Das Gemeine: es existieren zwei verschiedene Typen von Tabellenwertfunktionen und diese werden auch noch von Microsoft inkonsistent bezeichnet. Bemüht man das Microsoft Developer Network, kurz MSDN erhält man zu dem Suchbegriff „Tabellenwertfunktion“ (oder englisch „table valued function“) einen Abschnitt namens „Benutzerdefinierte Tabellenwertfunktionen“. Hier werden diese auch genau nach dem oben gezeigten Schema angelegt.
Der zweite Funktionstyp ist unter dem Begriff „Benutzerdefinierte Inlinefunktionen“ zu finden. Verwirrend wird es, weil zum einen diese Funktionen auch im selben Unterordner des Managementstudios, als „Tabellenwertfunktionen“ abgelegt werden. Weiterhin schreibt Microsoft sogar selbst in seiner Hilfe „Diese Funktionen werden als Tabellenwertfunktionen bezeichnet“. Und im Grunde haben sie auch Recht – auch bei diesen Funktionen wird als Rückgabetyp eine Tabelle geliefert, lediglich die Syntax innerhalb der Formel ist anders.
Merken Sie sich einfach: es gibt zwei Typen von Tabellenwertfunktionen. Die „normalen“ oder „Multi-Statement-Tabellenwertfunktionen“ (die Bösen) und die „Inline-Tabellenwertfunktionen“ (die Guten).
Die Inline-Tabellenwertfunktionen können intern optimiert werden, ähnlich dem gezeigten „echten“ Aufruf der Unterabfrage in der Hauptabfrage. Die Multi-Statement-Tabellenwertfunktionen werden intern ähnlich zeilenbasiert behandelt wie „echte“ Skalarwertfunktionen. Von daher bringt das in unserem Test keinen wirklichen Vorteil.
Tabellenwertfunktion ja, aber bitte inline
Der Quellcode der Inline-Funktion sieht der anderen Variante ähnlich. Die wesentlichen Unterschiede bestehen in dem Weglassen der Tabellendefinition und der BEGIN-END-Klammer. Hinter dem RETURN wird direkt das SELECT formuliert und damit implizit die Tabelle definiert.
In unserem Beispiel sieht das folgendermaßen aus:
--(Re-)Create table valued function (inline)
IF object_id('FTV_MaxCount2') IS NOT NULL DROP FUNCTION FTV_MaxCount2
go
CREATE FUNCTION FTV_MaxCount2(@Col1ID varchar(250))
RETURNS TABLE
RETURN
SELECT max(Col2) MaxCount
FROM T_FunctionTest_Counter
WHERE Col1 = @Col1ID
GO
Der Ausführungstest bringt nun endlich die erhofften Ergebnisse:
--Clear Cache DBCC freeproccache DBCC dropcleanbuffers ---Table (inline) SELECT (SELECT MaxCount From dbo.ftv_MaxCount2(Col1)) ColRet FROM T_FunctionTest
Mit einer Ausführungszeit von 180 Millisekunden (CPU-Zeit 65 ms) liegen wir quasi auf der Höhe der Raubein-Version als Unterabfrage. Die Untersuchung des Profilers zeigt erwartungsgemäß knapp 400 Reads an. Mit dieser Variante schaffen wir es also unsere Programmlogik innerhalb einer Funktion zu kapseln, aber die Performance auf sehr hohem Niveau zu halten.
Schnell ja, aber bitte mit Stil
Wir geben uns aber nur ungern mit 90%-igen Lösungen zufrieden. Daher holen wir auch hier jetzt noch das letzte bisschen raus und sorgen dafür, dass unser Quellcode auch noch optisch ansprechend aussieht. SUB-SELECTS sind grundsätzlich nicht ansprechend…
Auch hier gibt es wieder ein sehr elegantes Konstrukt, was im Übrigen auch hilft, den Quellcode innerhalb einer Abfrage zu zentralisieren. Die Rede ist vom CROSS APPLY. Mit diesen zwei kleinen Wörtern kann man eine Konvertierungslogik oder eben den Aufruf einer Tabellenwertfunktion zentral an einer Stelle einer Abfrage hinterlegen und in der ganzen Abfrage darauf zugreifen. Angewendet wird das CROSS APPLY dabei ähnlich wie ein JOIN hinter der abzufragenden Tabelle:
--Clear Cache DBCC freeproccache DBCC dropcleanbuffers ---Table (inline) + Cross Apply SELECT t.MaxCount ColRet FROM T_FunctionTest CROSS APPLY dbo.ftv_MaxCount2(Col1) t
Wie schon erwähnt, kann man CROSS APPLY auch für normale Konvertierungen oder Unterabfragen nutzen, wie zum Beispiel für unsere erste optimierte Variante. Dabei muss lediglich ein SUB-SELECT mit gültigem Spaltennamen formuliert werden und dem ganzen (geklammerten) Konstrukt ein Tabellenalias vergeben werden:
--Clear Cache DBCC freeproccache DBCC dropcleanbuffers ---Inline + Cross Apply SELECT ftc.MaxCount FROM T_FunctionTest ft CROSS APPLY (SELECT max(Col2) MaxCount FROM T_FunctionTest_Counter WHERE Col1 = ft.Col1) ftc
Damit erreicht man zumindest eine Zentralisierung des Quellcodes, da man an fast jeder Stelle der Abfrage auf das Ergebnis zugreifen kann. Leider eben nur innerhalb der Abfrage.
Inline-Tabellenwertfunktion for ever?
Sollte man jetzt alle Skalarwertfunktionen durch Tabellenwertfunktionen ersetzen? Die Antwort ist: jein. In dem obigen Beispiel ist der Performancegewinn eklatant. Wird innerhalb der Funktion zeilenabhängig auf eine andere Tabelle zugegriffen, sollte man in jedem Fall den Umbau auf eine Inline-Tabellenwertfunktion in Betracht ziehen.
Wir haben den Test allerdings auch schon einmal mit wesentlich mehr Daten mit einem schlichten LTRIM(RTRIM()) und den verschiedenen Funktionstypen durchgeführt. Hier konnten wir jedoch keinen merklichen Unterschied in den Abfragezeiten feststellen. Auffällig war allerdings, dass die Variante mit den Inline-Tabellenwertfunktionen zwar die gleiche Anzahl Reads verursacht hat, aber nur knapp ein Zehntel so viel CPU-Zeit. Allerdings war die CPU wohl in dem Fall nicht die Bremse. Obendrein hat das einfache SELECT * exakt genauso lange benötigt, um das Ergebnis zu liefern. Von daher war hier wohl die Skalarwertfunktion so schnell, dass ein Umbau nichts gebracht hätte. Die Verringerung der CPU-Zeit könnte sich aber in dem einen oder anderen Szenario durchaus bemerkbar machen. Wie so oft muss man es also im Einzelfall entscheiden und kann keine allgemeingültige Antwort liefern.
Wenn Sie also am Zweifeln sind – lieber Ihren Bissantz-Berater des Vertrauens fragen, bevor Sie alles umsonst umbauen…
