QA1002: Contains function performance effect

This advisor message is returned when a query uses the CONTAINS function. The message clarifies the reason and provides guidance to improve query performance.

Properties

	Value
RuleID	QA1002
Title	Contains
Category	Performance

Cause

The query uses the CONTAINS function.

Rule Description

CONTAINS can be expensive and slow because it requires a full scan.

Recommendation

If the intention of using CONTAINS is to match on a string prefix, use STARTSWITH instead, which performs a precise index scan and can be more cost-efficient.

If you're performing a case-insensitive string search, consider defining and indexing a computed property that projects LOWER(c.stringSearchProperty) and use case-sensitive string search instead.

Example

Original query:

SELECT * 
FROM c 
WHERE CONTAINS(c.category, "Historical")

The intention of the query is to search for category that starts with Historical. In this case, the following query has better performance with computed property c.cp_category:

SELECT * 
FROM c 
WHERE STARTSWITH(c.cp_category, "historical")