SOQL query has neither WHERE nor LIMIT
ID |
apex.performance.nonrestrictive_queries |
Severity |
high |
Remediation Complexity |
medium |
Remediation Risk |
medium |
Remediation Effort |
medium |
Resource |
Performance |
Language |
Apex |
Description
Reports a SOQL query with no WHERE clause and no LIMIT. Aggregate queries and the query passed to Database.getQueryLocator - where reading the whole object is the point - are not reported.
Rationale
An unfiltered query asks for every row of the object, and a synchronous transaction may retrieve 50 000 of them before the platform throws LimitException: Too many query rows. The query works in the sandbox that holds a few hundred test records and fails in production, where it also takes the heap with it: 50 000 records of a wide object will exhaust the 6 MB heap before the row limit is reached. Both failures arrive as an aborted transaction with no partial result, and they arrive on the day the object crosses the threshold, not on the day the code was written.
The following code illustrates the pattern detected by this rule:
public class AccountReport {
public List<Account> all() {
// FLAGGED: SOQL query has neither WHERE nor LIMIT
return [SELECT Id, Name FROM Account];
}
public List<Contact> everyContact() {
// FLAGGED: SOQL query has neither WHERE nor LIMIT
List<Contact> contacts = [
SELECT Id, FirstName, LastName
FROM Contact
];
return contacts;
}
public List<Account> byOwner(Id ownerId) {
return [SELECT Id, Name FROM Account WHERE OwnerId = :ownerId];
}
public List<Account> recent() {
return [SELECT Id, Name FROM Account ORDER BY CreatedDate DESC LIMIT 50];
}
public Integer total() {
return [SELECT COUNT() FROM Account];
}
public Database.QueryLocator everything() {
return Database.getQueryLocator([SELECT Id FROM Account]);
}
}Remediation
Filter the query to what the code actually uses - the ids in hand, the records changed since a watermark, the owner’s records - and add a LIMIT where the caller can work with a bounded page. Where the job genuinely has to walk the whole object, do it in a batch: Database.getQueryLocator streams the rows in chunks instead of materializing them.