Re: Case Insensitive Query Performance

Hi Dean,

The trouble with case-insensitive queries is that they always need functions applied to the attributes used in the where clause, so bang goes any use of indexes.

A common way is to hold the data as upper case or lower case then apply the functions to the driving information, and again apply the functions to the data after it has been retrieved, eg upper, lower, initcap etc. In this way the important parts of the where clause do not have functions applied to them so index usage is as expected and any performance enhancements are retained.

In other words keep the data in the table in the most performance enhancing way and apply any cosmetic massaging after retrieval.

The index creation wouldn't work because the index is of the data in the table. The view wouldn't work because again the data retrieval is based on the retrieval characteristics of the underlying table.

Hope this helps,   

cheers

Rod

Lane Associates
Lane_Associates_at_Compuserve.com
http://www.Lane-Associates.com

Dean Mah wrote:
>
> This must be a FAQ....
>
> I have a table with a surname field which is mixed case. I want to do a
> case insensitive search against this field. I have used the UPPER
> function to force both the surname and my search criteria to uppercase.
> Obviously, this results in poor performance.
>
> I have tried to create an index with UPPER(surname) which didn't work.
> I've tried to create a view with UPPER(surname) which didn't work. I
> don't want to create another field with the surname in uppercase because
> there are >400K records in the table and it is growing.
>
> Any hints, ideas, solutions, ... would be greatly appreciated.
> Dean
Received on Thu Apr 16 1998 - 03:26:59 CDT