SOQL LIKE pattern starts with a wildcard
ID |
apex.performance.soql_like_leading_wildcard |
Severity |
high |
Remediation Complexity |
medium |
Remediation Risk |
medium |
Remediation Effort |
medium |
Resource |
Performance |
Language |
Apex |
Description
Reports a SOQL LIKE comparison whose literal pattern begins with %, and the '%' + value concatenation that builds one for a bind variable.
Rationale
An index is ordered by the start of the value, so a pattern that begins with a wildcard has nothing to seek on: the query becomes a full scan of the object. On a small sandbox that is invisible; on an object with a few hundred thousand rows the query is marked non-selective and the platform refuses to run it in a trigger or a batch, throwing "Non-selective query against large object type". The same query keeps working from a report or an anonymous window, which is what makes the failure look intermittent.
The following code illustrates the pattern detected by this rule:
public class AccountSearch {
public List<Account> endingWith(String suffix) {
// FLAGGED: SOQL LIKE pattern starts with a wildcard
return [SELECT Id, Name FROM Account WHERE Name LIKE '%Ltd'];
}
public List<Contact> containing() {
// FLAGGED: SOQL LIKE pattern starts with a wildcard
return [SELECT Id FROM Contact WHERE Email LIKE '%@example.com'];
}
public List<Account> startingWith(String prefix) {
return [SELECT Id, Name FROM Account WHERE Name LIKE 'Acme%'];
}
public List<Account> containingBound(String term) {
// FLAGGED: SOQL LIKE pattern starts with a wildcard
String pattern = '%' + term + '%';
return [SELECT Id, Name FROM Account WHERE Name LIKE :pattern];
}
public List<Account> startingWithBound(String term) {
String pattern = term + '%';
return [SELECT Id, Name FROM Account WHERE Name LIKE :pattern];
}
public String percentage(Decimal value) {
return String.valueOf(value) + '%';
}
public List<Account> exactly(String name) {
return [SELECT Id, Name FROM Account WHERE Name = :name];
}
}