hi,

hi i need to know the difference for the following

in my query where clause to make my search case insensitive i used

UPPER(Customer_name)=Upper(?) .

is it advised and good way to use or i need to put a variable and assigning with value for comparison ex

String name = object.getCustName();
UPPER(customer_name)=Upper(name);

which one efficient and why?
plz clarify . thnx in advance.

using UPPER(customer_name) in the query is a performance killer ... Can you add a new column customer_name_upper to the table and make it GENERATED ALWAYS UPPER(cutomer_name)?

and then you can do
String name = object.getCustName();
String nameUpper=name.toUpperCase();
and in the query
customer_name_upper=nameUpper


HTH

Sathyaram

thanks sathya,
but the Table is an existing one and i can't touch it.
i need a option which will be efficient and also improve performance.

when i used UPPER(?) the query ran for 244ms and when i gave below from ur solution

String nameUpper=name.toUpperCase();
Upper(customer_name)=nameUpper; it took 150ms
so i opted this

thanks !!!

I assume you will be using prepared statements ...

If not, consider it for performance improvement

Sathyaram

We never have enough time or budget to fix the data. But we have all of the above and then some to find a costly solution.

Adding another column is always an option, but if you are transforming that column to UPPER case all of the time or most of the time. Then instead of adding new column or creating a work around you should get the data changed.

Reload the data using UPPER case and make sure that all new data coming in is in UPPER case. It would be a preferred method, at least to me Fix the data not the process.

Cougar,

I agree with your comment ..

But there are exceptions - when you want to capture and return the data in the same way the user input or sometimes, the data has to be in the right case Eg. Surname - McDonald or McDONALD and not MCDONALD.

I think this is more a business decision than a technical one ... Of course, business decisions do change if there is a cost associated with it

Cheers

Sathyaram

True, If that is the case and business has deep pockets or short sight then adding another column like you said is the way to go.