When Dealing with Large Data your SOQL query may return so many Objects that the limit on heap size is exceeded and an error occurs. To resolve, use a SOQL query for loop instead, since it can process multiple batches of records through the use of internal calls to query and queryMore.
For example, if the results are too large, the syntax below causes a runtime exception:
Instead, use a SOQL query for loop as in one of the following examples:
// Use this format if you are not executing DML statements
// within the for loop
for (Account a : [SELECT Id, Name FROM Account
WHERE Name LIKE ‘Acme%’]) {
// Your code without DML statements here
}
// Use this format for efficiency if you are executing DML statements
// within the for loop
for (List accts : [SELECT Id, Name FROM Account
WHERE Name LIKE ‘Acme%’]) {
// Your code here
update accts;
}
The following example demonstrates a SOQL query for loop used to mass update records. Suppose you want to change the last name of a contact across all records for contacts whose first and last names match a specified criteria:
public void massUpdate() {
for (List contacts:
[SELECT FirstName, LastName FROM Contact]) {
for(Contact c : contacts) {
if (c.FirstName == ‘Barbara’ &&
c.LastName == ‘Gordon’) {
c.LastName = ‘Wayne’;
}
}
update contacts;
}
}
Instead of using a SOQL query in a for loop, the preferred method of mass updating records is to use batch apex, which minimizes the risk of hitting governor limits.
For best performance, SOQL queries must be selective, particularly for queries inside of triggers. To avoid long execution times, non-selective SOQL queries may be terminated by the system. Developers will receive an error message when a non-selective query in a trigger executes against an object that contains more than 100,000 records. To avoid this error, ensure that the query is selective.
However, there are other complex scenarios in which custom indexes won’t be used. Contact your salesforce.com representative if your scenario isn’t covered by these cases or if you need further assistance with non-selective queries.
To better understand whether a query on a large object is selected or not, let’s analyze some queries. For these queries, we will assume there are more than 100,000 records (including soft-deleted records, that is, deleted records that are still in the Recycle Bin) for the Account object.
Query 1:
The WHERE clause is on an indexed field (Id). If SELECT COUNT() FROM Account WHERE Id IN ( ) returns fewer records than the selectivity threshold, the index on Id is used. This will typically be the case since the list of IDs only contains a small amount of records.
Query 2:
Since Account is a large object even though Name is indexed (primary key), this filter returns most of the records, making the query non-selective.
Query 3:
Here we have to see if each filter, when considered individually, is selective. As we saw in the previous example the first filter isn’t selective. So let’s focus on the second one. If the count of records returned by SELECT COUNT() FROM Account WHERE CustomField__c = ‘ValueA’ is lower than the selectivity threshold, and CustomField__c is indexed, the query is selective.
Source: http://developer.salesforce.com
For any support, please contact us
What does an SOQL do for your data? Learn about it in our blog.