An example of when Not to use a User Defined Function

First and foremost, I love user defined functions and think that they are one of the best features of ColdFusion and SQL Server. Custom functions are fantastic....

... until they are used in the wrong fashion. This is typically a sign of either inexperience or lack of fore thought on how the application would grow.

Recently I ran across a project that made poor use of UDFs. Briefly, the project has a function that when passed in a office location id, it queries the database and return the name of that location. Its a simple little function and when editing one record at a time is quite effective.

But what happens when you want to show a list of all employees and their office locations? Well if you were to loop through a recordset of your employees and while doing so you called this particular function, you would end up re-querying the database for each and every employee. In fact, that is exactly what was happening on this page. Did I mention that we had roughly 600 employee records (that's another story) on this page?

In this case, this was very poor programming. The page ran 601 queries against the database, when the results could have been gathered in 1 query. You see the employee table had the office location id, so a simple JOIN against the Location table provides us with the name of our location.

Moreover, calling the function on each iteration of the employee recordset resulted in the page taking a minimum of 625ms to return and sometimes much longer. Whereas by removing the call to the function and altering the query to perform the JOIN, the page now routinely loads in under 80ms.

Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
BlogCFC was created by Raymond Camden. This blog is running version 5.8.001.