Welcome to SFDC Share Point

Salesforce Blogs

Welcome to SFDC Share Point

Salesforce Blogs

Inner Query in Query Locator Bad Practice

Recently came across one code which had inner query in QueryLocator. For example some one has put that in Start method of Batch apex.

To give exact example of Query in Start Method of Batch,

global Database.QueryLocator start(Database.BatchableContext BC) {
    query = 'Select Id, (Select Id From Attachments) From Account'
    return Database.getQueryLocator(query);

I think this is not the right way of doing query.

To get into the reason why, i think this Link will help you.

In a SOQL query with parent-child relationship sub-queries, each parent-child relationship counts as an additional query.

The row counts from these relationship queries contribute to the row counts of the overall code execution.

In addition to static SOQL statements, calls to the following methods count against the number of SOQL statements issued in a request

  1. Database.countQuery

  2. Database.getQueryLocator

  3. Database.query

For example your org contain  only 2 Account and each have 10 Contacts.

If you execute this with sub Query like below

Select Id, Name, (Select Id, Name from Contacts) From Account

Then the total number of Query rows will be 22 (2+10+10).

From above analogy we can understand like this, If your org contain 40M accounts and each have 1 contact. Then in this scenario you can use sub query up to 25M only.

Like this Select Id, Name, (Select Id, Name from Contacts) From Account limit 25M