THE PROBLEM
When designing a query, it is incredibly important for performance reasons that you keep in mind what columns are indexed. The SQL engine will use those indexes to quickly lookup the data you're requesting and return it faster with much less disk and memory overhead. But equally important is understanding how you can negatively affect that execution plan by simply putting a column of your where clause in a function. When you do that, your queries may run considerably slower, incur more file and memory I/O and maybe even stop working.
Consider the following query:
select
sys.resourceID, sys.client_version0
from dbo.v_R_System as sys
where netbios_name0 = 'COMPUTER'
The "netbios_name0" column is an indexed field and the query produces the following execution plan (CTRL+L in SQL Enterprise manager 2005):
QUERY EXECUTION COST: .0065704
Notice that it does an INDEX SEEK (=fast) and the cost is super low. This is because the SQL engine knows pretty much where the data is and just needs to go there and pull the records that are = 'COMPUTER'
Now consider this query:
select
sys.resourceID, sys.client_version0
from dbo.v_R_System as sys
where UPPER(netbios_name0) = 'COMPUTER'
The indexed "netbios_name0" column is now inside of a the UPPER() function. This produces the following execution plan:
QUERY EXECUTION COST: .678143
Notice that it does an INDEX SCAN, meaning SQL physically looks at every single row in the index (=slow). The cost (although still super low in this overly simple example) is 103 times higher than the previous example.
The problem in a nutshell is that when you put an indexed column inside of a function of any kind (built-in functions like UPPER or user defined functions like udf_TableTracker from my table growth article) the SQL engine doesn't take advantage of the indexes.
THE SOLUTION
Well, the solution is, "Don't put the column in a function...if you can help it." OK, that's easier said than done and sometimes you just can't avoid it. But you need to make sure there's not a better way of doing it first. You'll have to decide if you can avoid the function or not, but one possible option is to see if you can perhaps put it on the other side of the = sign (or whatever operator is in your WHERE clause).
For example, let's say your WHERE clause has the following line:
where UPPER(netbios_name0) = @PARAMETER
Instead of putting the UPPER on the netbios_name0 column to convert them to uppercase, consider if you can instead convert the @PARAMETER to lowercase.
where netbios_name0 = LOWER(@PARAMETER )
In this case, the netbios_name0 column is not encumbered by a function and you may get the same results. Of course it's not always as easy as that so you'll have to find fun ways around this.
Please feel free to comment. I love hearing from you and I like to know if anyone is getting anything out of my posts.
Number2 (John Nelson)
MyITForum - Forum Posts
MyITForum - Blog
