Pete On Software

RSS Feed

Archive for January, 2008

What Cost, Functions?

This post is about Sql, specifically in the Sql Server RDBMS, but I believe that the principles will hold up across platforms.

If you are using a function in a WHERE clause in your SQL, like the following:

FROM some_table a
WHERE a.hash_value = fn_hash_function('value')

The function will be evaluated for every row in your table. This will result in performance decreasing exponentially as your table grows.

A better way to accomplish this query is to do the following:

DECLARE @input_hash varchar(1000)
SET @input_hash = fn_hash_function('value')

FROM some_table a
WHERE a.hash_value = @input_hash

This code will only evaluate the function one time and as long as you have the comparison column indexed properly, you will see very impressive results. On one query on my local machine, Sql Profiler showed that query that was taking over 7000ms begin to return in about 70ms.

The performance gains in that exact instance were less drastic on a Sql Server running on an actual server (it had just been powering its way through), but in time it would have gotten unbearably slow, as well.

Hope that helps someone.


Well, I’ve struck out on my own. After having a private blog over on Blogger and posting innumerable quizzes, movie reviews, and jokes, I started to get quite bored. I had the idea to start this blog (and even had the name picked out), but just didn’t really have the motivation to get everything set up.

I’ve finally gotten that motivation and now I’ve struck out on my own with my own installation of WordPress. I plan to blog mostly about everything technology-related in my life (though mostly about software).

It may not be ground-breaking, but hopefully it will be interesting!

  Recent Entries »