SQL Server Performance: Upper and Lower functions and Database collation.

Issue:

SQL Server performance issue: possible high CPU or other resources utilization and  slow performance of a specific query, with execution time more than expected.

Example of the Query:

(@P0 nvarchar(4000))SELECT column1, column2,..
FROM Mytable WHERE (UPPER( mycolumn1 ) = UPPER(@P0))

Scenario:

• Mytable is huge and contains thousands or millions of rows.
• There is an Index on the column mycolumn1.
• The Query is running from an Application, possible with Auto generated script.

Like Entity Framework : EdmFunctions.ToUpper(DbExpression) Method (System.Data.Entity.Core.Common.CommandTrees.ExpressionBuilder) | Microsoft Docs

Troubleshooting steps:

• Determine Database collation:

select DATABASEPROPERTYEX('mydatabase','collation')

As per the result: The collation “SQL_Latin1_General_CP1_CI_AS” for example means the collation is case insensitive:

Collation and Unicode support - SQL Server | Microsoft Docs

Means if you select with where mycolumn1= ‘XYZ’ or mycolumn1= ‘XyZ’ or mycolumn1= ‘xyz’  then it should give the same result. You can try that by removing the Upper function.

• Check if the column is configured with special collation:

Set or Change the Column Collation - SQL Server | Microsoft Docs

• Test Queries result before and after the update:

If you test that both queries below is giving the same result then you can update you code to remove the Upper function:

1. (@P0 nvarchar(4000))SELECT column1, column2,..
   FROM Mytable WHERE (UPPER( mycolumn1 ) = UPPER(@P0))

2. (@P0 nvarchar(4000))SELECT column1, column2,..
   FROM Mytable WHERE mycolumn1  = @P0
   

In my scenario, there is no need for the Upper function, and removing it will not change the result of the Query.

But the function will force the Query Optimizer to implicitly convert all the column values inside the table (to upper case) and process the comparison. Which means a table or index scan instead of index seek.

Resolution:

Remove the Upper function.

In general, using Functions in the where clause may cause performance issues: Trying if possible, to avoid them by changing the Query statement or by using computed columns and computed column index.