Unter dem Strich steht jede Art von Funktion auf meiner persönlicher Giftliste. Als Administrator, der evtl. nicht zu den
perfekten Codeschreibern gehören mag, würde ich jede Funktion auf den Prüfstand
stellen lassen.
Lediglich die Inline Tabellenwert Funktion kann seitens SQL
Server optimiert ausgeführt werden. Hier
noch mal der Code zur Erinnerung:
CREATE FUNCTION dbo.fInlTabValfunction(@par1 int)
RETURNS TABLE
AS RETURN
(
Select * from Tabelle where Sp = @par1
)
GO
Woran liegt es, dass die Funktionen so schlecht abschneiden?
Oft genug hört man , dass Funktionen ebenso wie Prozeduren kompiliert werden
würden und daher doch auch schnell sein müssten. Verabschieden Sie sich von
diesem Gedanken. Beides falsch! Grundsätzlich wird bei einer Prozedur lediglich
der Ausführungsplan beim ersten Aufruf festgelegt. Die Angabe der Parameter beim
ersten Aufruf entscheiden also über den Plan, der dann immer wieder verwendet
wird – auch nach Neustart. Insofern spart man sich das Kompilieren des Planes,
das durch auch etwas aufwendig werden kann, die Ausführung der Prozedur läuft
wie gewohnt prozedural ab.
Sie können das gerne messen. SET STATISTCS TIME ON gibt
nicht nur Dauer und CPU Zeit in ms aus, sondern auch die Analyse und
Kompilierzeit. Probieren Sie das doch einfach mal folgendes aus:
Meine Datenbank entspricht der Northwind Datenbank, besitzt aber pro Tabelle 2 Mio eindeutige Datensätze.
set statistics time on
select top 10
country
, city
, sum(freight) over (partition
by employeeid
order by freight)
from customers c inner join
orders o
on
c.customerid = o.customerid
Ergebnismeldung
SQL Server-Analyse- und Kompilierzeit:
, CPU-Zeit = 1062 ms, verstrichene Zeit
= 1184 ms.
(10
Zeilen betroffen)
SQL Server-Ausführungszeiten:
,
CPU-Zeit = 1484 ms, verstrichene Zeit = 775 ms.
Nach zweiter Ausführung
SQL
Server-Analyse- und Kompilierzeit:
, CPU-Zeit = 0 ms, verstrichene Zeit =
0 ms.
Warnung:
Ein NULL-Wert wird durch einen Aggregat- oder sonstigen SET-Vorgang gelöscht.
SQL Server-Ausführungszeiten:
, CPU-Zeit = 1656 ms,
verstrichene Zeit = 455 ms.
Dem Schotten ist das ein La ola wert.
Zurück zu unserer Funktion. Warum schneidet diese immer so
schlecht ab? Nehmen wir einfach mal folgenden Fall an:
select * from customers
where companyname like 'A%'
select * from customers
where left(companyname,1) = 'A'
Ja, es komm in beiden Fällen das richtige Ergebnis heraus.
Aber schon im Plan fällt auf, das SQL Server bei Verwendung der Funktionen
keinen Indexvorschlag anbietet.
Nach dem Setzen des Index sieht der Plan gar so aus:
Bei Verwendung einer Funktion kann der SQL Server vorab nicht das Ergebnis der Funktion kennen. Daher ist er
gezwungen, die Tabelle bzw den Index in diesem Fall komplett durchzugehen (à SCAN), während die
Abfrage mit dem LIKE einen SEEK nutzen könnte.
Stellen Sie sich vor, Sie suchen in einem Telefonbuch nach Mustermann
Max, dann werden Sie sehr schnell fündig. Nehmen wir mal an , Sie suchen den
einzigen Menschen im Telefonbuch dessen Namen aus 13 Buchstaben besteht, dann
müssen Sie jeden einzelnen Namen durchsuchen. Funktionen sind also vorab
schlecht schätzbar, doch genau das ist wichtig für die Indexwahl.
Multi-Statement Tabled-Value Function - SQL Server 2017
Mit SQL Server 2017 wurde schon eine Optimierung an
Multi-Statement Tabled-Value Function eingeführt. Zur Erinnerung:
CREATE FUNCTION fMultTabValue (@p1 int, @p2 char)
RETURNS
@Table_Var
TABLE (c1 int, c2 int)
AS
BEGIN
Insert into
@Table_Var (c1,c2
select col1, col3 from tabelle
RETURN
END
GO
SELECT Sp,.. from dbo. fMultTabValue(10,20) …
Normalerweise, würde SQL Server mit dem Select beginnen,
also auch die Planschätzung die aber zu dem Zeitpunkt nicht möglich ist, da der
INSERT noch nicht geschehen ist. Ergo, SQL Server setzt an statt dessen die
Zahl der geschätzten Zeil auf 100 fest (oder auch 1 je nach Version). Ab SQL
2017 allerdings kann der INSERT zuerst ausgeführt werden und im Anschluss die Funktion. So kann SQL Server die Anzahl der Zeilen beim Ausführen der
Funktion sehr exakt einschätzen und daher auch korrekte Indizes verwenden und auch den Plan selbst optimieren.
Skalarfunktion unter SQL Server 2019
Gehen wir wieder ein paar Zeilen zurück zu der
Skalarfunktion und der Suche nach Max Mustermann.
set statistics io, time on
ALTER DATABASE [NwindBig] SET COMPATIBILITY_LEVEL = 140
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE ;
Go
set statistics io , time on
select dbo.frngsumme(orderid) from orders where orderid < 100000
Die Datenbank läuft im Kompabilitätmodus SQL Server 2017 und
liefert folgendes Meldung und Messung:
Tabelle:
"Orders". Anzahl von Überprüfungen: 1, logische Lesevorgänge: 2287,
..
(1 Zeile
betroffen)
SQL Server-Ausführungszeiten:
, CPU-Zeit = 10063 ms,
verstrichene Zeit = 10216 ms.
Auffällig ist das Fehlen der Tabelle Order Details , in der
die Rechnungssumme erstellt werden müssen! Die Abfrage braucht 10 Sekunden…
Tatsächlich wird im geschätzten Ausführungsplan angezeigt,
dass die Funktion separat behandelt wird und für jeden Datensatz somit einzeln
immer wieder ausgeführt werden muss.
Nun die Abfrage in SQL 2019 Kompabilitätsmodus:
SQL
Server-Analyse- und Kompilierzeit:
,
CPU-Zeit = 0 ms, verstrichene Zeit = 3 ms.
(99999
Zeilen betroffen)
Tabelle:
"Order Details". Anzahl von Überprüfungen: 99999, logische
Lesevorgänge: 319618, …
Tabelle:
"Orders". Anzahl von Überprüfungen: 5, logische Lesevorgänge: 2402,…
(1 Zeile
betroffen)
SQL Server-Ausführungszeiten:
, CPU-Zeit = 453 ms,
verstrichene Zeit = 683 ms.
Grandios! Unter 1 Sekunde…!! Die Meldungen geben nun die richtigen Messungen aus und der Plan zeigt, dass die Funktion Bestandteil der Abfrage geworden ist und somit in die Verarbeitung miteinbezogen werden kann.
SQL Server 2019 kann unter folgenden Voraussetzungen eine
Skalarfunktion optimieren:
- Keine Tabellen Variablen verwenden
- Keine Partitionsfunktion
- Keine Datumsfunktion enthalten wie Getdate()
- Kein Order by in der Funktion
- Keine CTE enthalten
um nur ein paar Bedingungen zu nennen
Fazit:
Der Einsatz von Funktionen ist nun nicht mehr ganz so
bedenklich einsetzbar. SQL Server 2017 und SQL Server 2019 leisten hier schon
mal gute Pionierarbeit. Dennoch lohnt
sich das Überprüfen von Funktionen weiterhin..Nach wie vor können viele Funktionen noch nicht automatisch optimiert werden.
Sie wollen mehr wissen, wie man SQL Server optimiert? Dann
schauen Sie doch mal hier rein:
https://ppedv.de/schulung/kurse/SQLServer2011-Performance-Optimierung-Sicherheit-Indizes-Verwaltung-Komprimierung-Verbesserung.aspx
https://ppedv.de/Schulung/Kurse/SQL-Server-2005-Development-Entwicklung-Transact-Notification-NET.aspx