SqlClr outperforms T-SQL (7 times faster!)
time to read 4 min | 711 words

Okay, a couple of months ago I posted about Finding Use for SqlClr for date processing, since it was much easier to do it in .Net than in SQL. Since then I retracted my statement about it.

If you read my blog for a while, you probably noticed that I'm doing quite a bit of performance tuning lately. After making the switch from SqlClr to T-Sql, I noticed a sudden drop in performance. Some investigation lead me to the change from SqlClr function to T-Sql functon.

Here is the source code for the .Net function (yes, I occasionally do VB.Net):

<SqlFunction()> _

Public Shared Function FirstDayOfMonth(ByVal year As Integer, ByVal month As Integer) As DateTime
    Return New DateTime(year, month, 1)
End Function

And here is the T-Sql function:

CREATE

 FUNCTION FirstDayOfMonth(@year int, @month int)
RETURNS DATETIME WITH SCHEMABINDING AS
BEGIN
    RETURN DATEADD(MONTH,@month-1,DATEADD(YEAR,@year-1900,0))
END

As you can see, none of them do anything terribly complicated. I then run the following query on a table with 40 million records:

SELECT

 1 FROM BigTable
WHERE SqlClr.FirstDayOfMonth(YEAR(Date),MONTH(Date)) = '20010101'

This query has no meaning whatsoever, it just make sure that the function is running for a long time. This query run for 34 seconds, before returning (an empty result set).

SELECT

 1 FROM BigTable
WHERE [T-Sql].FirstDayOfMonth(YEAR(Date),MONTH(Date)) = '20010101'

This query run for 3 minutes and 29 seconds, 7 times slower than SqlClr! I'm not sure how to take this news, since I was completely convinced that it would be the other side that would win the race. Anyone got an explanation?


Tweet Share Share 0 comments
Tags: