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:
SELECT a.id, a.name
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')
SELECT a.id, a.name
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.
