Tuesday, November 15, 2016

Salesforce SOQL tricks

General Governor Limits

Max query records 50,000
Max update records 10,000
Max Batch Size 2,000
SOQL for loop 200
Sensitive Selection Threshold 200,000
Number of SOQL (Sync) 100
Number of SOQL (Batch) 200


For testing, we have 200,000+ records in EventQueue__c table


Scenario 1

Query Editor
Select id from EventQueue__c

Anonymous Block
List<EventQueue__c> eventQueues = [Select id from EventQueue__c];

Issue
  • The Query from Query Editor would work fine
  • Apex anonymous block will error out as it will return more than 50k records.

Possible Fix

Limit
List<EventQueue__c> eventQueues = [Select id from eventQueue__c limit 50000];  

SOQL for loop
It is better as it batches up in 200. Internally it uses batch api if you look at the logs
for(List eventQueues : [Select id from EventQueue__c limit 50000] ) {
     System.debug(' eventQueues ' + eventQueues.size() );
}




Scenario 2

Query Editor
select count() from EventQueue__c

Anonymous Block
Integer eventCount = Database.countQuery('select count() from EventQueue__c');

Issue
  • The Query from Query Editor would work fine
  • Apex anonymous block will error out as count() has to work against 50000+ rows

Possible Fix

Limit
Integer eventCount = Database.countQuery('select count() from EventQueue__c limit 50000');




Scenario 3

Anonymous Block
List eventQueues = new List();
for(List eventQueuesBatch : [Select id from EventQueue__c limit 50000] ) {
 eventQueues.addAll( eventQueuesBatch );
}
update eventQueues;

Issue
  • Apex anonymous block will error out on update statement because we can not update more than 10k records in DML.

Possible Fix

Limit to 10k
List eventQueues = new List();
for(List eventQueuesBatch : [Select id from EventQueue__c limit 10000] ) {
 eventQueues.addAll( eventQueuesBatch );
}
update eventQueues;





Scenario 4

Query Editor
select count() from EventQueue__c where deleteFlag__c = true limit 1

Anonymous Block
Integer eventCount = Database.countQuery('select count() from EventQueue__c where deleteFlag__c = true limit 1');

Issue
  • The Query from Query Editor would work fine
  • Apex anonymous block will error out because when object has more than 200,000+ records, select clause has to be to efficient. E.g. in above query deleteFlag__c is not indexed and more than half of the records have deleteFlag__c.
  • Error Message : Non-selective query against large object type (more than 200000 rows). Consider an indexed filter or contact salesforce.com about custom indexing. Even if a field is indexed a filter might still not be selective when: 1. The filter value includes null (for instance binding with a list that contains null) 2. Data skew exists whereby the number of matching rows is very large (for instance, filtering for a particular foreign key value that occurs many times) 

Possible Fix
As suggested, Use indexed column (e.g. lookup field - foreign key to constraint the return results) as suggested in error.

No comments: