MS SQL Coldfusion fine tuning query

A coworker and I are trying to fine tune a ver开发者_开发百科y simple query. He said he had read somewhere that using lower vs lcase could affect server performance and that one method was more efficient than the other but we can't find the article and I thought I'd ask here what your thoughts were. Should we spend our time looking elsewhere to to tweak performance?

Coldfusion 8 Enterprise MS SQL Server 2000 Recordset: 500,000+ records

Original Query:

select last_name lname
from phone
where uid = <cfqueryparam value="#attributes.email#" cfsqltype="cf_sql_varchar" maxlength="50">

Option A

select last_name lname
from phone
where lower(uid) = <cfqueryparam value="#attributes.email#" cfsqltype="cf_sql_varchar" maxlength="50">

Option B

select last_name lname
from phone
where uid = <cfqueryparam value="#lcase(attributes.email)#" cfsqltype="cf_sql_varchar" maxlength="50">

Option C

select last_name lname
from phone
where uid = lower(<cfqueryparam value="#attributes.email#" cfsqltype="cf_sql_varchar" maxlength="50">)

The database is indexed and what not but does contain about 500,000+ records. Even this is s a simple query, we are looping over this around 150 times so anything we can do to shave off a second or two would help.

Using lower on the indexed column will cause MS SQL to not use your index. That could cause a significant performance hit in a table of your size. You should make sure the UID is always lower case when you insert it, or can try this: lower function on indexed column

In your case, you are searching against an email field, which is most of the time full of lower case characters than uppercase. So converting to lower case can (tests will tell you) speed up things.

As to apply this to your query, you have to convert to lowercase both the field and the email variable (for them to be comparable):

select last_name lname
from phone
where lower(uid) = <cfqueryparam value="#lcase(attributes.email)#" 
                                 cfsqltype="cf_sql_varchar"
                                 maxlength="50">

(converting #attributes.email# inside the cfqueryparam tag is better because it will be done only once before sending the query to sql-server)

more infos in this article: Performance Tip: Upper Case vs Lower Case

There may be no point in doing all this LCase/LOWER as:

SQL Server Text Matching Is Case INSENSITIVE

How to perform case sensitive searches in SQL Server?:

A default SQL Server installation is case insensitive, which means that SQL Server will not differentiate between upper and lower case characters/letters.