THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server'

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

This blog has moved! You can find this content at the following new location:

Published Saturday, August 25, 2012 12:07 AM by Hugo Kornelis

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



a.m. said:

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).

August 24, 2012 6:08 PM

AlexK said:

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?

August 25, 2012 10:03 PM

Hugo Kornelis said:

@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!

August 26, 2012 4:50 AM

a.m. said:

@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?

August 26, 2012 8:56 AM

AlexK said:

@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?

August 26, 2012 3:26 PM

a.m. said:

@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...

August 26, 2012 5:17 PM

Danny Cordell said:

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

December 28, 2012 12:35 PM

JP said:

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.

January 9, 2013 10:52 AM

Hugo Kornelis said:

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.

January 10, 2013 4:47 AM

Leave a Comment


About Hugo Kornelis

Hugo is co-founder and R&D lead of perFact BV, a Dutch company that strives to improve analysis methods and to develop computer-aided tools that will generate completely functional applications from the analysis deliverable. The chosen platform for this development is SQL Server. In his spare time, Hugo likes to visit the SQL Server newsgroups, in order to share and enhance his knowledge of SQL Server.
Privacy Statement