+ Start a Discussion
AlexPHPAlexPHP 

Querying against reference objects using __c vs __r.Id

Does anyone know of the performance and limit ramifications between using __c vs using __r.Id when querying against reference objects?  They both seem to work, but what's the difference in terms of resource usage?

 

For example... 

 

// querying for myaccounts by a list of state ids

List<MyAccount__c> accounts = [ Select Id From MyAccount__c Where State__c In :stateIdsList ];

// VS

List<MyAccount__c> accounts = [ Select Id From MyAccount__c Where State__r.Id In :stateIdsList ];

 

 

We ran into a non-selective query issue and one of the things that seem to alleviate it was to switch a query from using __r.Id to use __c instead.  Therefore, I believe that using __c probably requires less lookups and will return a smaller set in the SOQL engine?

 

Anyone have any insight or know where there is some documentation on this distinction?

 

Thanks

Best Answer chosen by Admin (Salesforce Developers) 
Anand@SAASAnand@SAAS

As you have found out State__r.Id and State__c are the same values. When you have lookup/master-detail relationships the __c stores the salesforce Id and __r should be used to get additional fields other than Id in your SOQL.

 

Think of __r as means to JOIN the relating table/object and __c as the foreign key. So using __c won't require an additional JOIN and hence might be more performance v/s __r.