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

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?



Best Answer chosen by Admin (Salesforce Developers) 

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.