SQL tuning: Wildcard usage

A search condition might include the LIKE operator or keywords that perform a similar function such as Starts With or the non-CBR version of CONTAINS. Which keywords are used and the placement of the wildcard operator in the search value determines whether the query can use a database index.

A Starts With search is implemented as a LIKE query on 'value%"; that is, with the wildcard operator at the end. If there are enough unique characters at the beginning of the search string, an index on the property can be used by the query. Conversely, if the property search uses the CONTAINS keyword, the query cannot use the database index. Some characters at the front of the search value are required to traverse an index, which is not the case with the CONTAINS keyword: it is implemented as a LIKE search on '%value%'. A CONTAINS search with no other viable predicates results in a table scan and with concurrency can severely impact performance.

The following actions might solve or mitigate performance problems with these queries:
  • Perform CONTAINS searches off-hours.
  • Minimize CONTAINS searches in the application; for example, make the default behavior STARTS WITH.
  • Possibly use a CBR query instead of a query with a CONTAINS search.

For more information, see the "Best Practices for Searches" section in the topic Working with Queries.