QA1006: UPPER or LOWER in String Comparison

This advisor message is returned when a query uses the UPPER or LOWER function in string comparisons. The message clarifies the reason and provides guidance to improve query performance.

Properties

Cause

The query uses UPPER or LOWER in a string comparison in the WHERE clause.

Rule Description

UPPER and LOWER are deterministic functions, but don't use the index. If you plan to do frequent case insensitive comparisons, these functions could consume a significant number of request units (RUs).

Recommendation

Define a computed property for the uppercase or lowercase property and define an index on the computed property.

For more information on computed properties, see Computed properties.

Example

Original query:

SELECT * 
FROM c 
WHERE LOWER(c.name) = "abc"

Create a compute property on LOWER(c.name)

{ 
  "computedProperties": [ 
	{ 
		"name": "cp_lowerName", 
		"query": "SELECT VALUE LOWER(c.name) FROM c" 
	} 
  ] 
}

Index cp_lowerName and the use the following updated query:

SELECT * 
FROM c 
WHERE cp_lowerName = "abc"