I am running some performance test and found a result I can't explain. I've implement the same logic in two ways: T-SQL UDF and .Net CLR Function. Here is the source code:
Scalar UDF:
create or alter function dbo.ParticularDateFormat(@inputDate date) returns char(14) as begin declare @return char(14) set @return = cast(DATEPART(dd, @inputdate) as char(2)) + ' ' + choose(datepart(mm, @inputDate), 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dez') + ' ' + cast(DATEPART(yyyy, @inputDate) as char(4)) return @return end
.Net CLR Function
[SqlFunction(IsDeterministic = true)] public static SqlString NewDateStyle(SqlDateTime inputDate) { return ((DateTime)inputDate).ToString("dd MMM yyyy"); }
Then I run both functions on a 1 million rows table. When I run the scalar UDF I get this statistics time feedback:
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. (1000000 row(s) affected) Table 'SmallTable'. Scan count 1, logical reads 1616, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. (1 row(s) affected) SQL Server Execution Times: CPU time = 3734 ms, elapsed time = 4587 ms.
And this is the statistics time feedback when I run the .Net CLR function:
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server parse and compile time: CPU time = 141 ms, elapsed time = 191 ms. (1000000 row(s) affected) Table 'SmallTable'. Scan count 1, logical reads 1616, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. (1 row(s) affected) SQL Server Execution Times: CPU time = 1312 ms, elapsed time = 3586 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.
As expected the .Net CLR function run way faster.
What I can't get my mind around is the reason for the elapsed time to be so high, almost twice the time to process the query (CPU time).
Any ideas?