Salesforce Developer Soql Limits

salesforce-developer-soql

http://resources.docs.salesforce.com/206/20/en-us/sfdc/pdf/salesforce_app_limits_cheatsheet.pdf
https://developer.salesforce.com/page/Governors_in_Apex_Code
https://developer.salesforce.com/page/SFDC:ApexGovernorLimits
https://developer.salesforce.com/docs/atlas.en-us.204.0.apexcode.meta/apexcode/apex_gov_limits.htm
https://salesforce.stackexchange.com/questions/34741/getting-record-count-through-webservice-bypassing-limits - some good solution
http://www.vividsoftware.com/blog/2015/may/26/large-data-sets-and-aggregate-functions - done reading, emphasize on using the REST API approach
https://salesforce.stackexchange.com/questions/60164/please-help-first-error-too-many-query-rows-50001-in-my-batch-job - done reading
http://stackoverflow.com/questions/9984349/limitexception-too-many-query-rows-50001-from-with-count-aggregate-function - done reading
https://developer.salesforce.com/forums?id=906F000000090NlIAI - done reading
https://success.salesforce.com/ideaView?id=08730000000Br7TAAS - done reading first page
https://mihirshahalwayonline.wordpress.com/2016/04/09/hack-query-limit-50001-error/ - done reading
https://force746.wordpress.com/2014/08/28/count-large-number-of-records-more-than-50000/ - done reading
http://www.salesforcetutorial.com/manage-salesforce-governor-limits/
http://www.infallibletechie.com/2014/10/triggers-best-practices-in-salesforce.html - done reading
http://salesforcecloudcrm.blogspot.com/2015/01/apex-and-visual-force-page-in.html

Salesforce - Developer - SOQL - Limits:

An Apex request can gets a maximum of:

1. 100 SOQL queries.
2. 150 DML operations

We can only have 5 queued or active batch jobs at any one time.

A list of governor limits specific to the @future annotation: 

1. No more than 10 method calls per Apex invocation
2. No more than 200 method calls per Salesforce license per 24 hours
3. The parameters specified must be primitive dataypes, arrays of primitive 
   datatypes, or collections of primitive datatypes.
4. Methods with the future annotation cannot take sObjects or objects as 
   arguments.
5. Methods with the future annotation cannot be used in Visualforce controllers 
   in either getMethodName or setMethodName methods, nor in the constructor. 

Storage limit:
Set Up -> Administration Setup -> Storage Usage 

Limits.getQueryRows(): Returns the number of records that have been returned
  by issuing SOQL queries.
Limits.getLimitQueryRows(): Returns the total number of records that can be 
  returned by issuing SOQL queries.  This is the 50000 limit on the number of 
  rows that our code can cover.

Limits.getQueries(): Number of Queries used in this apex code so far.
Limits.getLimitQueries(): Total Number of SOQL Queries allowed in this apex 
  code context.

Limits.getDmlRows(): Number of rows queried in this apex code so far.
Limits.getLimitDmlRows(): Total Number of records that can be queried  in 
  this apex code context.

Limits.getDmlStatements(): Number of script statements used so far. 
Limits.getLimitDmlStatements(): Total Number of DML statements allowed in 
  this apex code context

Limits.getLimitScriptStatements(): Total Number of script statements allowed 
  in this apex code context.

Limits.getAggregateQueries(): Returns the number of aggregate queries that 
  have been processed with any SOQL query statement.

Limits.getLimitAggregateQueries(): Returns the total number of aggregate 
  queries that can be processed with SOQL query statements.

Limits.getCallouts(): Returns the number of Web service statements that have 
  been processed.

Limits.getLimitCallouts(): Returns the total number of Web service statements 
  that can be processed.

Limits.getCpuTime(): Returns the CPU time (in milliseconds) accumulated on 
  the Salesforce servers in the current transaction.

Limits.getLimitCpuTime(): Returns the time limit (in milliseconds) of CPU usage 
  in the current transaction.

Limits.getEmailInvocations(): Returns the number of email invocations (such 
  as sendEmail) that have been called.
Limits.getLimitEmailInvocations(): Returns the total number of email invocation 
  (such as sendEmail) that can be called. 

Limits.getFutureCalls(): Returns the number of methods with the future annotation 
  that have been executed (not necessarily completed).
Limits.getLimitFutureCalls(): Returns the total number of methods with the future 
  annotation that can be executed (not necessarily completed).

Limits.getHeapSize(): Final heap size.  Returns the approximate amount of memory
  (in bytes) that has been used for the heap.
Limits.getLimitHeapSize(): Returns the total amount of memory (in bytes) that can 
  be used for the heap.

Limits.getQueryLocatorRows(): Returns the number of records that have been 
  returned by the Database.getQueryLocator method.
Limits.getLimitQueryLocatorRows(): Returns the total number of records that have been 
  returned by the Database.getQueryLocator method.

// Proactively determine if there are too many Opportunities to update and 
// avoid governor limits
if (opptys.size() + Limits.getDMLRows() > Limits.getLimitDMLRows()) {
  System.debug('Going to be hitting a governor limit.');
  for (Account a: Trigger.new) {
    a.addError('You are attempting to update the addresses of too many accounts
      at once. Please try again with fewer accounts.');
  }
} else {
  System.debug('Not going to hit DML governor limits.');
  for (Account a : Trigger.new) {
    for (Opportunity o: opptys) {    
      if (o.accountid == a.id) {
        o.description = 'testing';
      }
    }
  }
  update opptys;
}

SOQL statements cannot exceed 20,000 characters in length.  If it does, 
Salesforce will throw a MALFORMED_QUERY exception.  Long, complex SOQL statement 
such as statements that contains many formula fields can sometimes result in a 
QUERY_TOO_COMPLICATED error.  This error occurs because the Salesforce expands 
this statement internally when it process this statement, even though the 
original SOQL statement is under the 20,000 character limit.  To avoid this, 
reduce the complexity of our SOQL statement.

The WHERE clause cannot exceed 4,000 characters.

We cannot use a field that represents a long text area, encrypted data, or 
base64-encoded as part of the WHERE clause.

As part of the WHERE clause, we must specify values whose data type matches the 
field type of the specified field.  We must supply a native value.  Other field 
names or calculations are not permitted.  If quotes are required, use single 
quotes.  Using double quotes will result in an error.  Single quotes are 
required if the value is not a date or a number.

How can we get around the 'Too many query rows: 50001' limit?

  1. Modify your SOQL queries to be more selective and to only bring back the records required for the Contacts in the scope. For example, you could modify the query for ytd_total_cpgn_mb to only select CampaignMember records where the ContactId is present for one of the Contacts in the scope. The typical approach here is to create a Set of ContactIds to use in the SOQL where clauses.
  2. Apply a limit to the query.
    1. Select id from Account limit 50000. This is not a reliable option. Logic may fail due to required record not fetched.
    2. select count() from Account LIMIT 50000 OFFSET :total
    3. If you don't have offset enabled for your organization, you will need to request it through a case. The maximum offset is 2000 rows.
  3. Guess I can use Limits.getQueryRows() and Limits.getLimitQueryRows() to disable the SOQL querys if required.
  4. If you use a VF Page and Controller, and set the VF page to readonly on the page, you can query up to 1 000,000 rows.
  5. I read somewhere that we can use a sub-query.
  6. Use Batch Processing. Implement batch class and process the record in batch. More details for batch apex. See https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_batch_interface.htm. Limitation: Batch class runs asynchronous. If the expected result is real time, this option will not work.
  7. ReadOnly Annotation: Salesforce has offered @readonly option to come over the limitation. A controller method, webservice method can be defined as readonly. Any SOQL inside this method can fetch more than 50,000 row. See https://mihirshahalwayonline.wordpress.com/2016/04/09/hack-query-limit-50001-error/. Limitation: To use the @ReadOnly annotation, the top level method must be readonly method . For example, if aVisualforce page calls a Web service that contains the @ReadOnly annotation, the request fails because Visualforce is the top level request, not the Web service.

To use the @readonly attribute:

The controller class:

public class StatsController {
    public Integer numberOfContacts {
        get {
            if (numberOfContacts == null) {
                numberOfContacts = [select count() from Contact];
            }
            return numberOfContacts;
        }
        private set;
    }
}

Visualforce page:

<apex:page controller="StatsController" readOnly="true">
    <p>Number of Contacts: {!numberOfContacts}</p>
</apex:page>

Then you will be able to see the result when you access this page in the browser. Note that the readOnly attribute has to be set to “true”, otherwise you will still get a Visualforce error complaining “Too many query rows: 50001 “. We may have to create a generic visualforce page, and then pass the query to this visualforce page and have this visualforce page run the query and returns the result.

Using Batchable class:

public class ContactBatchable implements Database.Batchable<sObject>, Database.Stateful {
    Integer total = 0;

    public Database.QueryLocator start(Database.BatchableContext BC){
        return Database.getQueryLocator('select Id from Contact');
    }

    public void execute(
            Database.BatchableContext BC,
            List<sObject> scope){
        total += scope.size();
    }

    public void finish(Database.BatchableContext BC){
        System.debug('total: ' + total);
    }
}

And then execute the following statement in developer console:

Database.executeBatch(new ContactBatchable(), 2000);

This is going to be an asynchronous apex job so it can take time. When it is finished, you will be able to see a log with category “Batch Apex” at the top. The debug statement prints info into that log. You will also see a few logs with category “SerialBatchApexRangeChunkHandler” which is the log for each batch. In the ContactBatchable the start method has to use the query locator, so that it can retrieve the records up to 50 million. (after 50 million? Ask Salesforce support). If you use an iterable in the batch class, the governor limit for the total number of records retrieved by SOQL queries (50,000 for the moment) is still enforced.

If the start method of the batch class returns a QueryLocator, the optional scope parameter of Database.executeBatch can have a maximum value of 2,000. If set to a higher value, Salesforce chunks the records returned by the QueryLocator into smaller batches of up to 2,000 records. This method cannot be applied to production orgs as you won’t be able to create an Apex class in Production org.

Approach: Make a http request using the REST API:

By far, this seems to be the simplest way of achieving this and it can be used in production orgs. In developer console, run the following statements:

HttpRequest req = new HttpRequest();
req.setEndpoint('https://'+URL.getSalesforceBaseUrl().getHost()+'/services/data/v20.0/query/?q=SELECT+Id+from+Contact');
req.setMethod('GET');

string autho = 'Bearer '+ userInfo.getsessionId();
req.setHeader('Authorization', autho);

Http http = new Http();
HTTPResponse res = http.send(req);
string response = res.getBody();
string total = response.substring(response.indexOf('totalSize":') + 11, response.indexOf(','));
system.debug('Total: '+ total);

We will need to add a remote site setting with URL set to your production org’s URL (say https://na11.salesforce.com) in the setup.

Approach: Use SOQL in For loop, to avoid 50001 limit error.

Consider:

Account[] accts = [SELECT id FROM account];

The above code will throw an exception if there are more than 50000 records.

for (List<Account> acct : [SELECT id, name FROM account WHERE name LIKE 'Test']) {

    // Your logic here

    update acct;
}

Does using loop help or adding the WHERE clause help?

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License