T-SQL User-Defined Functions: the good, the bad, and the ugly (part 4)

re: T-SQL User-Defined Functions: the good, the bad, and the ugly (part 4)

Adam Machanic — Fri, 24 Aug 2012 22:08:35 GMT

Hugo, I wish you would have written to me before publishing so I could help you get that loopback code working. You need to do the loopback call in a different thread -- which is why I built the threading library (doing that work properly, so that you don't cause problems for yourself down the road, is not trivial).

re: T-SQL User-Defined Functions: the good, the bad, and the ugly (part 4)

AlexK — Sun, 26 Aug 2012 02:03:20 GMT

Hi Hugo,

Typically we get better performance with ContainsKey method instead of catching exceptions, as you did in your TripleCache class. What do you think?

re: T-SQL User-Defined Functions: the good, the bad, and the ugly (part 4)

Hugo Kornelis — Sun, 26 Aug 2012 08:50:10 GMT

@Adam: Yup, I got that. And I did look at trying to implement the threading library. But after seeing the code and the supplied demos, I realised that for me (with my limited understanding of CLR), it would take a lot of time to get to understand it sufficiently before I could adapt it to work as part of a scalar UDF. And I did not want to postpone publishing this post even more, it was already delayed more than I wanted.

@Alex: As I said before, I am absolutely not an expert in CLR. I wrote the code by adapting a snippet I found (somewhere in the MSDN library) through Google. I was not aware that it's possible to use ContainsKey instead of Try Catch. But now that I do, I'll have to play around with it and see how it affects performance. Thanks for the suggestion!

re: T-SQL User-Defined Functions: the good, the bad, and the ugly (part 4)

Adam Machanic — Sun, 26 Aug 2012 12:56:42 GMT

@Alex: I think that should only improve performance if you have a large number of "misses" (i.e. lookups for keys that don't exist). But shouldn't TryGetValue be best in every case?

re: T-SQL User-Defined Functions: the good, the bad, and the ugly (part 4)

AlexK — Sun, 26 Aug 2012 19:26:17 GMT

@Adam: the cache is the slowest when it is just warming up. This is when it needs speeding up most, and this is exactly when we have most misses.

I agree that TryGetValue might be a very good choice. However, I would expect big difference between throwing exceptions and avoiding them, and somewhat smaller differences between different ways of avoiding exceptions.

I have not benchmarked it recently, so I could be wrong.

What do you think?

re: T-SQL User-Defined Functions: the good, the bad, and the ugly (part 4)

Adam Machanic — Sun, 26 Aug 2012 21:17:15 GMT

@Alex: Depends on how you design the cache. A lazy loading cache might get a lot of misses at the beginning; a more ambitious pre-loading scheme may avoid that. If it's possible to do. It all depends...

re: T-SQL User-Defined Functions: the good, the bad, and the ugly (part 4)

Danny Cordell — Fri, 28 Dec 2012 17:35:54 GMT

I am looking forward to the article on table-valued UDF's.

re: T-SQL User-Defined Functions: the good, the bad, and the ugly (part 4)

JP — Wed, 09 Jan 2013 15:52:39 GMT

Are you still planning a fifth part to the series? This has been a great summary on the pros and cons of user defined functions.

re: T-SQL User-Defined Functions: the good, the bad, and the ugly (part 4)

Hugo Kornelis — Thu, 10 Jan 2013 09:47:16 GMT

JP: I definitely want to continue the series. But I cannot commit myself to any time frame at this time. I am now working on something else that takes up almost all the time I have available for community work.

Once that is done, I hope to find enough time and energy to resume my blogging.