using function to process string makes query very slow
Posted on 2011-05-02
I'm using SQL 2005. I have a query that returns a string from a table that is usually the name of the County. But a few of the entries have something added to the County field that I want to strip out. So I made a function to process the string. If one of a couple of patterns are matched, an altered string is returned from the function, ELSE the passed in string is returned unaltered. This lets me use the function in several queries and have only one point of maintenance.
But the query takes 100 times longer to run with the function call in the query. Seems like the query is processing all the strings through the function first, then running applying the where clause to restrint the result set. This is the case whether or not the function processed string is included in the where clause.
Any ideas how I can either make this function perform well or otherwise create reusable code to alter these County names, other than more data (another column to the original table with the corrected County name in it, or a lookup table with the wrong name and the corrected name in two columns, etc.)?