1

I have the below SOQL query which is performing poorly in my Informatica job:

SELECT 
Job__c.Id ,
Job__c.Name ,
Job__c.Case__r.Id ,
Job__c.Case__r.Account.RecordTypeId ,
Job__c.Case__r.Account.AccountNumber ,
Job__c.Case__r.RecordType.Name ,
Job__c.Case__r.Contract_New__r.Name ,
Job__c.Claim_Payee__r.Id  
FROM Job__c 
Where
(Case__r.CSR_Integration_Status__c = 'In Process')

Is the best way to query records on the parent? Would something like: where Job__c.Case__r.CSR_Integration_Status__c = 'In Process' perform better or is it the same?

Also what is the performance impact of having all these relationships to other objects in my query? I think that could hurt my performance, but I'm not sure of the best way to test query performance.

Thank you, Ian

2
  • Ian - Cases have sufficiently large data volume where you will need to use more critieria to make your query selective. Below advice will help you figure it out (using Query Plan Tool, etc). Your FILTER clause is not selective since I know your Org... ;) -> You can add Case__r.LastModifiedDate which is indexed or opena support case and have SFDC add a custom Index on the integration status. field.help.salesforce.com/help/pdfs/en/… May 7 '15 at 17:58
  • Thanks Cory! The two approaches I was proposing are the same in the Query Plan Tool, but I the answer from Jitendra helped, and if I can change the query in Informatica I think that will improve the performance. For now we're just running the job every 5 minutes instead of 10 like we were doing before. Hope all is well in your new role! May 12 '15 at 20:03
1

Instead of querieng child record and adding condition on parent record you can perform it other way around. Your query is basically doing Joins to check condition and to get value from parent :

Try something like this

SELECT
    Id,
    Account.RecordTypeId,
    Account.AccountNumber ,
    RecordType.Name,
    Contract_New__r.Name ,
    (SELECT ID, Name from Job__r)
FROM
    Case
WHERE
    CSR_Integration_Status__c = 'In Process'

Make sure field "CSR_Integration_Status__c" is indexed.

Also, check these FAQ on Query Plan Tool.

1

Offical doc for Query Editor:

https://help.salesforce.com/apex/HTViewHelpDoc?id=code_dev_console_tab_query_editor.htm&language=en_US

Take a look at the query Editor tool provided by salesforce.

Go to Developer console--> Query Editor

Enter both your queries and see how much the job costs

Note: Go to Help--> Preferences and set Query Plan to true before you execute your query.

You can compare and see what the cost of the query is from the query plan window.

0

I think you would be better off querying against the Case object. That way you would not have to perform these cross object relational lookups. That should help performance.

-Clark

0

Usually in SOQL, you would not require Job__c prefix to be used for each field.

SELECT 
Id ,
Name ,
Case__r.Id ,
Case__r.Account.RecordTypeId ,
Case__r.Account.AccountNumber ,
Case__r.RecordType.Name ,
Case__r.Contract_New__r.Name ,
Claim_Payee__r.Id  
FROM Job__c 
Where
(Case__r.CSR_Integration_Status__c = 'In Process')

If your query is performing bad, it might be because of data volume. You can check query plan tool in salesforce developer console to check cardinality of your WHERE clause. But I assume that "CSR_Integration_Status__c" field is a picklist field. If it is a text field, you can consider indexing it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.