Performance Issue with Alphanumeric Column

[madankarmukta]

Hi All,

One of the table in my project Database contains Alphanumeric column.I converted all data in that column to upper case using To_UPPER() function.

I found that the query using that Table get slowed..?

Is making the content to upper case might be the performance issue ..?

Please help me.

Thanks!

[rski]

@madankarmukta
Can you show the execution plan of both queries?

[amitpatel66]

There can be many reasons to why the query is taking long time to execute. Firstly, we would like to:

1. have a look at your query?
2. Amount of data in the table that you are using in yur query?
3. any indexes already created on the table?
4. DML operations frequently performed on those tables?

It would be great if you can let know on the above points for our experts to help.

[madankarmukta]

@amitpatel66
HI Amit ,

Thanks for the reply. The table which I am querying , have 10,000 records,Moreover it have the clustered index defined on the column which I am not using in the TO_Upper() funstion.

Regarding the DML operation being performed on the table in a day is quite frequent.. It may be 20 times in a day.

My curiosity lies in the fact that mere usage of To_UPPER function on alfanumeric column can make the query slow..?

In addition to this what if we have

1)Less frequent DML operation on the table...
2)we have non clustered index defined on the alphanumeric column .. the column i am using in To_UPPER()
3)we have clustered index defined on the alphanumeric column .. the column i am using in To_UPPER()

will any of these going to improve the performance and if yes .. how it will ..?

Thanks!

[amitpatel66]

As you said that the DML Operation is frequently performed on the table. Does the INDEX Rebuild frequently?

[madankarmukta]

@amitpatel66
Hi,
Thanks for the reply.

Yes .. we are using with recompile option every time..

Thanks!