function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
dipudipu 

SOQL optimization guidelines

Is there a guideline document for SOQL optimization.? How much should I worrry about performance issues due to sub query?

 

Thanks,

Dipu 

 

Best Answer chosen by Admin (Salesforce Developers) 
Shashikant SharmaShashikant Sharma

No there is documentation

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

 

 http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_gov_limits.htm

 

You must be running this in annonymous block or debug log. When you will use this in Apex Class or Trigger you will see the count as 2.

All Answers

Shashikant SharmaShashikant Sharma

Some general Guidelines

 

1) No SOQL in for loop

See this for Example : http://forceschool.blogspot.com/2011/05/writing-apex-trigger-save-limits-in.html

 

2)Don't SOQL a Object twice in a single context (single thread or request to server)

By this I mean lets take a simple example

Account a = [Select id from Account where Name ='Test Account1'];
Account b = [Select id from Account where Name ='Test Account2'];

 

this should be writeen as

Account a;
Account b;
for(Account aObj : [Select id from Account where Name ='Test Account1' OR Name ='Test Account2'])
{
  if(a.Name == 'Test Account1')
    {
         a = aObj;
    }
  else 
    {
      b = aObj;
    }
}

 These are the basic things that you need to do. 

 

And About SubQuery : There is no issue in performance in using sub query and sub queries also get counted in SOQL Limit like a separate query. Means 

 

Accoaunt a = [Select id , (Select id from Contacts) from  Account limit 1];

it will be counted as 2 SOQL

and similar to

Accoaunt a = [Select id from Account limit 1];

List<Contact> c = [Select id from Contact where accountid =: a.id];

dipudipu

That is not exactly SOQL optimization, although that is good for over all optimization.

My conern was around nesting of sub queries or adding multiple sub queries. Looks like nesting of sub queries is not supported yet. 

As always thanks for the quick response.

Shashikant SharmaShashikant Sharma

Are you asking this

About SubQuery : There is no issue in performance in using sub query and sub queries also get counted in SOQL Limit 

like a separate query. Means 

 

Accoaunt a = [Select id , (Select id from Contacts) from  Account limit 1];

it will be counted as 2 SOQL

and similar to

Accoaunt a = [Select id from Account limit 1];

List<Contact> c = [Select id from Contact where accountid =: a.id];

Ankit AroraAnkit Arora

@Dipu : Shashikant is just explaining you how you can optimize your code with the minimum use of code line which indeed helps you to reduce the use of query. It seems perfect to me.

 

Just in addition if you want to ask that we can use Sub-Query in Sub-Query (nested) then am sorry we can not do this. But the example and explanation provided by Shashikant is good.

 

 

Thanks

Ankit Arora

Blog | Facebook | Blog Page

dipudipu

Accoaunt a = [Select id , (Select id from Contacts) from  Account limit 1];

I just tried it.using the console.

It is counted as one SOQL.

 

My guess is there would be some performance penalties for sub query or drilling down to reference fields. Since you guys are not aware of any such things, looks like there is no documentation on that. 

 

Shashikant SharmaShashikant Sharma

No there is documentation

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

 

 http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_gov_limits.htm

 

You must be running this in annonymous block or debug log. When you will use this in Apex Class or Trigger you will see the count as 2.

This was selected as the best answer
@login.ax974@login.ax974

Hi,

 

 I want to ask one very basic question here - suppose my code is like this:

 

Set<Id> stAcctId = new Set<Id>();
for Account a : trigger.new)
{
   if (a.OwnerId != trigger.oldMap.get(a.id).OwnerId)
      {
         stAcctId.add(a.Id);
      }
}

List<Contact> lstContacts = [SELECT Id, Ownership_Changed__c FROM Contact WHERE AccountId IN :stAcctId AND Contact_Type__c = 'New User'];

 

Even if my collection stAcctId is empty, will the SOQL fire? If yes, will it be counted in the total number of SOQL queries?

Is checking before firing the query whether or not the collection is empty a good practise?

 

Thanks.

IspitaIspita

The SOQL will be fired and if its fired dont you think it will counted ?