function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Mak OneMak One 

Out of these 2 queries which will perform better and which one should I use?

For a Master-Detail or Look Up relationship. First query simply matches ID (for lookup or master-detail field) whereas second one gets all records of detail from Master ID record. So, at last both will return same records. Only fetching them will be little different. So, which one should I use and which one will give better performance?

[select id, DetailObjField__c from DetailObj__c where Master__c=:MasterId]

OR

[select id,(select DetailObjField__c from DetailObj__r) from Master__c where Master__c.id=:MasterId]
Best Answer chosen by Mak One
Vishant ShahVishant Shah
I tried this code out in my dev org and here are the results 
list<contact> cs = [select id from contact where name like 'vsCont%'];
delete cs;
list<account> accsList1 = [select name, (select name from contacts) from account where Name like 'vsAcc%'];
delete accsList1;
    
list<account> accs = new list<account>();

for (integer i = 1 ; i <=100 ; i++){
    accs.add(new Account(Name ='vsAcc ' + i));
}

insert accs;

for (account acc : accs){
    list<contact> conts = new list<contact>();
    
    for (integer j=1 ; j <= 5 ; j++){
        conts.add(new contact(firstName='vsCont ' + j, lastname = 'vs' + j ,accountId = acc.Id ));
    }
    
    insert conts;
}


list<account> accsList = [select name, (select name, FirstName from contacts) from account where Name like 'vsAcc%'];
//this give  Number of SOQL queries: 1 out of 100

for (account acc : accsList){
    acc.Name = acc.Name + 'test';
    for (contact con : acc.Contacts){
        con.firstName = con.FirstName + 'test';
    }
}

update accsList;
Results of code

This proves the second solution will work better with the updates, I check the contacts to make sure the first name has "test" appended, and it has.

I'm not going to do solution 1 for updates as i know for sure it will be over my DML limits

Ta
Vish

All Answers

Tony TannousTony Tannous
Hello ,

it depend what are the data that you want to render on the VF.

in case you only need data from the detail object of course use the first 1 querry.

but in case you need to have some of the master object data while rendering the details object for sure you should use the second one.

Good Luck
Vishant ShahVishant Shah
Hi,

If your in a loop and using the first query and say you have 100 master records, you will hit SOQL limits as the total queries will now be 101 including your original query,
with the second one, it will be just 2 queries

so it is always better to use the 2nd one, please correct if i'm wrong

Ta,
Vish
Tony TannousTony Tannous
Dear Vishant,

In case you have 200 records in the master and 5 children record for each master record (1000 records).

So using solution 2 in a batch to update the children will generate an error because you will be updating more than 200 records,

However using solution number 1 will not cause any probleme because the batch will process the record automatically.
 
Regards,

TTA
Vishant ShahVishant Shah
Hi Tony,

with solution 1 you will hit the soql limit straight away if you are querying detail records in a loop of 200 master records. with solution 2 only 2 queries will be utilised.. 

You can always update field values of the detail records in solution 2 and do a update on the master record. I hvaent checked how mandy DML will this use though. I'm assuming it would use 2 but could be wrong.

I will check this situation in my org and update later

Ta
Vish
Tony TannousTony Tannous
Hi Vishant,

i didn't paid attention for the where condition that it is = ,

Both of these 2 solution is not best practice and will generate an error .

the best practice is to put the master ids in a set and do the select statement on the Ids so the code will be as follow


[select id,(select DetailObjField__c from DetailObj__r) from Master__c where Master__c.id  in :setMasterIds];


Regards
Vishant ShahVishant Shah
I tried this code out in my dev org and here are the results 
list<contact> cs = [select id from contact where name like 'vsCont%'];
delete cs;
list<account> accsList1 = [select name, (select name from contacts) from account where Name like 'vsAcc%'];
delete accsList1;
    
list<account> accs = new list<account>();

for (integer i = 1 ; i <=100 ; i++){
    accs.add(new Account(Name ='vsAcc ' + i));
}

insert accs;

for (account acc : accs){
    list<contact> conts = new list<contact>();
    
    for (integer j=1 ; j <= 5 ; j++){
        conts.add(new contact(firstName='vsCont ' + j, lastname = 'vs' + j ,accountId = acc.Id ));
    }
    
    insert conts;
}


list<account> accsList = [select name, (select name, FirstName from contacts) from account where Name like 'vsAcc%'];
//this give  Number of SOQL queries: 1 out of 100

for (account acc : accsList){
    acc.Name = acc.Name + 'test';
    for (contact con : acc.Contacts){
        con.firstName = con.FirstName + 'test';
    }
}

update accsList;
Results of code

This proves the second solution will work better with the updates, I check the contacts to make sure the first name has "test" appended, and it has.

I'm not going to do solution 1 for updates as i know for sure it will be over my DML limits

Ta
Vish
This was selected as the best answer
Mak OneMak One
Vishant Shah you mean solution 1 or solution 2?
Direct query or Relational Query?
Mak OneMak One
HI Vishant Shah,

Did you meant that you should use:
[select id,(select DetailObjField__c from DetailObj__r) from Master__c where Master__c.id=:MasterId]  (Query 1)

instead of:
[select id, DetailObjField__c from DetailObj__c where Master__c=:MasterId]  (Query 2)

for updates?
Vishant ShahVishant Shah
It definitely depends on what you are trying to do, but if youre trying to update parent record and child record at the same time, i would go with query 1
Tony TannousTony Tannous
Hi Mak,

as Vishant said it depend what you are doing,

The best practice is to put the master ids in a set and do the select statement on the Ids so the code will be as follow, whatever method you are going to use.


[select id,(select DetailObjField__c from DetailObj__r) from Master__c where Master__c.id  in :setMasterIds];

or

[select id, DetailObjField__c from DetailObj__c where Master__c.Id in :setMasterIds];

Regards
Mak OneMak One
Which will be giving better performance?
I am asking because if we query through id it will search in all records of Child Object
But in case we get it through Parent Object then in that case Salesforce might have some internal logic through which it might not have to search in all the records of Child Object like it might have direct link to data or something.

I am also thinking that 1 st one is better if have to access only Child Objects. Also, because of limit.

But at last what about performance? Does Salesforce have some logic by which it can fetch child records through Parent record more easily?