Assume you have a large database with millions of records. One of the columns is an id number made of upper and lower case letters, numbers, and other symbols. And to make it easy on your users you merely convert thier data entry and the data to upper case. When you run your queries however you get very poor performance from your system. It seems that the processor effort to convert the id to upper case for the entire table takes quite a while, compared to not converting it. To measure the impact I used coldfusions CFQuery and the result attribute to capture ExecutionTime.
<cfquery name="q1" datasource="DSN" result="q1result">
Select * from table
where UPPER(my_id) = '#ucase(Form.my_id)#'
</cfquery>
...
<cfquery name="q2" datasource="DSN" result="q2result">
Select * from table
where my_id = '#Form.my_id#'
</cfquery>
...
<cfoutput>
q1: #q1result.ExecutionTime#, q2: #q2result.ExecutionTime#
</cfoutput>
Yields: q1: 16034, q2: 91 (time in Milliseconds)
That is huge difference. I assume the same happens for forcing to lower case. I suppose the database could have an upper or lower case index which would be much faster.
