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.