Performance of formatDateTime SQLCLR function
Following my previous post I have been asked on the performance of the function I wrote. This is an area I am keen to press on people.
Any one questioning SQLCLR performance should have come to my launch event.
CLR function is slower than built in system functions on a pure date conversion, however doing a complex date format as I showed in the post the you would probably want to use a udf to simplify the code.
UDFs are slower than the CLR equivalent.
I have yet to find a well written one that isn’t. In the date example, the difference is 50%.
Now you also have to look at the fact that with my tests on a laptop the SQLCLR function ran 80,000 times in 800ms compared to 100ms for the core system functions. That’s very quick. If you are having worry between using a function that takes 0.00001s or one that takes 0.00008s, you either have too much time on your hands or you are looking in the wrong place for your performance problems.
So it then comes down to ease of use. Which is easier, to code, understand and support.
- Datename(yyyy,getdate()) + ' ' + DATENAME(mm,getdate())+ ' ' + datename(d,getdate())
- dbo.formatdatetime(getdate(),'yyyy MMM d')
Personally I vote for no.2 and will take the hit.
-