.Net CLR functions and Scalar UDF performance issue

179 Views Asked by At

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?

0

There are 0 best solutions below