TSQL–Funktionen sind super – oder doch nicht?

In diesem Blogeintrag werde ich die Performancehintergründe von Funktionen erleuchten und herausfinden, ob Funktionen wirklich zu den schwarzen Schafen gehören.

Funktionen können das Leben enorm erleichtern, da sie uns immer wiederkehrende Berechnungen oder Abläufe ersparen. Das mag soweit in anderen Programmiersprachen stimmen, in TSQL müssen wir mal etwas genauer hinschauen.

Funktionen im Allgemeinen

Ich persönlich war ein voller Fan von Funktionen, die Betonung liegt auf war. Allerdings sollte man Funktionen nach diesem Satz nicht vollständig verteufeln. Es gibt durchaus auch gute Funktionen.

Zuerst das Schlechte:
Es gibt drei Möglichkeiten, mit Funktionen zu arbeiten.

Funktionen in der Select-Liste

In der Select-Liste können nicht nur Spalten angegeben werden, sondern auch Funktionen, die vermutlich Spalten als Übergabeparameter entgegennehmen und dementsprechend manipulieren.
Die Funktionen müssen für jede verarbeitende Zeile aufgerufen werden, was natürlich zu höherer Last führt. Außerdem wird bei Funktionen die parallele Abfrageausführung verhindert.

Funktionen in der Where-Bedienung

Leider auch hier keine Performance-Vorteile. Wenn Funktionen als Prädikat verwendet werden, vereitelt das qualitative Kardinalsschätzungen. Außerdem kann es dazu führen, dass beispielsweise nicht auf einen Index zugegriffen werden kann.

create function f_test(@id int)
returns int
as
    begin
        return(@id + 10)
end


Hier meine Testfunktion.
Erster Test mit der Funktion. In der Where-Bedingung wird die Funktion verwendet. Auf der Spalte “ID” ist ein non clustered Index.

select ID from test
where dbo.f_test(id) > 80000


Schauen wir uns mal die dazugehörigen Stats und den Plan an.

Plan mit funktion

Wie hier zu erkennen ist, wird ein Index-Scan verwendet, wobei es ihm möglich sein müsste einen Index Seek durchzuführen. Allerdings kann der Index mit Daten, die von einer Funktion verarbeitet werden, nichts anfangen und somit nicht durch den B-tree gehen.

Hier noch die Auswertung der Statistics:

Stats mit funktion

Um den Vergleich zu sehen: jetzt das Beispiel ohne Funktion im Prädikat.

select ID from test
where id > 79990


Jetzt hier der Plan:

Plan ohne Funktion

Wie zu erwarten: der effiziente Index-Seek, zudem parametrisiert der SQL Server den Plan.
Da Zahlen mehr sagen als Bilder, hier die Auswertung der Stats der zweiten Select-Anweisung.

Stats ohne Funktion

Gleich zu Beginn ist zu sehen, dass die Anzahl der Seiten, die verwendet wurden, ein gutes Viertel ist im Gegensatz zu den Stats des ersten Selects. Das liegt überwiegend am Index-Seek. Außerdem ist bei jeder Ausführung des zweiten Selects die CPU-Zeit auf 0ms. Nicht so beim ersten Beispiel, hier liegt die CPU-Zeit immer bei ca. 350ms.
Da die Funktion für jede Zeile berechnen muss, kommt hier ein bisschen Zeit zusammen. Nun kann man aber nicht wirklich von einer komplexen Rechnung und viel Business-Logik in der Funktion reden, d.h. sobald die Funktion kryptischere Ausmaße annimmt, kann das Ganze schnell böse enden.

Das Licht am Ende des Tunnels

Nun muss es irgendwas geben, was für Funktionen spricht, sonst wäre des Feature zwar nett aber langsam.
Leider gibt es nichts, was für Funktionen spricht (performancetechnisch). Es gibt allerdings eine Art Funktion, die keine Nachteile hat, aber auch keine Vorteile, und deswegen bedenkenlos benutzt werden kann. In Bayern gibt es das Sprichwort: Ned gschimpft, is globt gnua Smiley mit herausgestreckter Zunge(Nicht geschimpft, ist gelobt genug).

Ich spreche hier von einer Inline-Tabellenwertfunktion. Kurz zur Erklärung: Eine Inline-Tabellenwertfunktion besteht lediglich aus einer Select-Anweisung, die in der Return-Klausel hinterlegt ist, mehr nicht. Es gibt keine Variablen-Deklaration, Fallunterscheidung oder Schleifen.

Kurze Demonstration:
Ich werde zwei Funktionen erstellen, die eine als Inline-Variante, die andere nicht.

Inline-Funktion

create function Kunden_Land(@Land varchar(30))
returns table
as
return(select companyname from Customers
    where Country like @Land)



Nicht Inline

create function Kunden_Land_nicht_Inline(@Land varchar(30))
returns @result table (Companyname varchar(50))
as
begin
    insert into @result
        select companyname from Customers
        where Country like @Land
    return
end


Die beiden Aufrufe

select * from Kunden_Land('%')
select * from Kunden_Land_nicht_inline('%')

 

und hier die Pläne

Plan inline

Beim “Inline-Plan” schätzt der SQL Server die Zeilen richtig ein und kann somit einen sehr optimalen Plan verwenden.

Plan nicht Inline

Hier haben wir ein Negativbeispiel. Der SQL Server schätzt die Anzahl von Zeilen auf EINS, allerdings kommen 91 Zeilen raus. Das liegt nicht mal an der Funktion, der Übeltäter ist ein anderer, nämlich die Tabellenvariable. Mit der kann der SQL Server nichts anfangen und schätzt die Zeilen bei jeder Tabellenvariable auf eins, selbst wenn sie nicht in einer Funktion benutzt werden.

Fazit

Leider fallen Funktionen unter den Bereich “hazardous”. Lediglich die Inline-Funktion kann ohne Bedenken verwendet werden. Sie dient allerdings dann nur sehr eingeschränkt als “dynamische Sicht”. Zumal wird es bei dem ein oder anderen Anwendungsfall nicht möglich sein, komplett auf Funktionen zu verzichten.

 

Wollen Sie mehr über “No Go’s” in TSQL wissen, buchen Sie unseren Kurs: SQL Tuning

Kommentare sind geschlossen