Performance Test der CLR User Aggregation FirstString und LastString
Die Implementierung einer eigenen CLR Aggregation, wie FirstString / LastString aus meinem Blog, ist sehr einfach umzusetzen. Nun stellt sich mir noch die Frage: Wie performant ist das gegenüber den integrierten Aggregationen wie MIN / MAX?
Also machen wir mal einen Test. Basis soll eine einfache Tabelle mit einem VarChar Feld sein ohne jeden Index; es soll eh über alles aggregiert werden und da wird sowieso ein Full-Table-Scan ausgelöst. Zunächst eben die Tabelle mit 50.000 Einträgen anlegen, auf meinem Notebook dauert das ca. 55 Sekunden.
-- Performance-Test für CLR Aggregation FIRST / LAST
-- gegenüber den integrierten MIN /MAX bei Strings
SET STATISTICS TIME OFF;
GO
CREATE TABLE AggrTest (String varchar(20));
GO
SET NOCOUNT ON;
DECLARE @loop int; SET @loop = 0;
WHILE @loop < 100000
BEGIN
INSERT INTO AggrTest
VALUES (RIGHT(REPLICATE('0', 20) + CONVERT(varchar(20), @loop), 20));
SET @loop = @loop + 1;
END -- Dauer ca.
GO
Da ich schon ahne, was auftreten wird, habe ich einmal den Sql Server Dienst neu gestartet, damit die damit mein Assembly OlafHelper.SqlServer.Aggregate auch nicht geladen. Ist.
Hier nun das Test-Script, zunächst Min/Max und dann FirstString/LastString. Dazwischen wird der Procedure und Daten-Cache gelöscht.
-- Cache & Co löschen
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
GO
PRINT 'Min/Max'
SET STATISTICS TIME ON
GO
-- Nun die Zeitmessung
SELECT MIN(String), MAX(String)
FROM AggrTest
GO
SET STATISTICS TIME OFF;
GO
-- Cache & Co löschen
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
GO
PRINT 'First/LastString'
SET STATISTICS TIME ON;
GO
SELECT dbo.FirstString(String), dbo.LastString(String)
FROM AggrTest
GO
SET STATISTICS TIME OFF;
-- Aufräumen
DROP TABLE AggrTest
Ergebnis der ersten Ausführung:
Min/Max
SQL Server-Analyse- und Kompilierzeit: CPU-Zeit = 16 ms, verstrichene Zeit = 16 ms.
SQL Server-Ausführungszeiten: CPU-Zeit = 218 ms, verstrichene Zeit = 252 ms.
SQL Server-Analyse- und Kompilierzeit: CPU-Zeit = 0 ms, verstrichene Zeit = 0 ms.
First/LastString
SQL Server-Analyse- und Kompilierzeit: CPU-Zeit = 578 ms, verstrichene Zeit = 1317 ms.
SQL Server-Ausführungszeiten: CPU-Zeit = 374 ms, verstrichene Zeit = 449 ms.
SQL Server-Analyse- und Kompilierzeit: CPU-Zeit = 0 ms, verstrichene Zeit = 0 ms.
Ergebnis der zweiten Ausführung:
Min/Max
SQL Server-Analyse- und Kompilierzeit: CPU-Zeit = 0 ms, verstrichene Zeit = 26 ms.
SQL Server-Ausführungszeiten: CPU-Zeit = 188 ms, verstrichene Zeit = 257 ms.
SQL Server-Analyse- und Kompilierzeit: CPU-Zeit = 0 ms, verstrichene Zeit = 0 ms.
First/LastString
SQL Server-Analyse- und Kompilierzeit: CPU-Zeit = 0 ms, verstrichene Zeit = 25 ms.
SQL Server-Ausführungszeiten: CPU-Zeit = 265 ms, verstrichene Zeit = 330 ms.
SQL Server-Analyse- und Kompilierzeit: CPU-Zeit = 0 ms, verstrichene Zeit = 0 ms.
Der Grund für die großen Unterschiede zwischen der ersten und zweiten Ausführung ist einfach, denn nach dem Sql Server Dienst-Neustart muss zunächst einmal das CLR Assembly geladen werden. Einmal im Speicher ist die Ausführung gleich schneller und