0

While doing a left anti join in soql on a non-indexed field. The query is failing with QUERY_TIMEOUT.

'QUERY_TIMEOUT: Your query request was running for too long.'

Here is my query-

SELECT ID,Order__c FROM Agent_Credit__c WHERE Order__c NOT IN (SELECT Order__c from Opportunity)

I am running this as daily basis, but success rate is like once in a week. This is not the exact query what I am running but a similar example.

closed as off-topic by Eric, Ratan Paul, Tushar Sharma, superfell, Sebastian Kessel Oct 5 '16 at 19:33

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions on problems in code you've written must describe the specific problem and include valid code to reproduce it. For help writing short, self-contained syntactically-valid examples, see: SSCCE.org" – Eric, Ratan Paul, Tushar Sharma, superfell, Sebastian Kessel
If this question can be reworded to fit the rules in the help center, please edit the question.

  • The question doesn't contain sufficient information. What is obj2? Is it a list, a sub query, or something else? I suggest you put the original code here. – Lance Shi Oct 5 '16 at 5:37
  • 1
    I'm actually pretty sure that you are missing FROM in your query. Weird that it succeeds even once a week – Novarg Oct 5 '16 at 6:16
  • @LanceShi edited the query and added the objects – Sujit Tiwari Oct 5 '16 at 13:10
  • @Novarg I missed From in the query actually it contains – Sujit Tiwari Oct 5 '16 at 13:10
1

The use of negative filters like NOT IN is just not good for performance. You should see if there's a way to get the data you need in a way that uses positive filters instead.

Any indexed fields you can add positive filters for should improve the situation. For example, if you only want records created this month, you can add:

WHERE CreatedDate = THIS_MONTH

Whatever changes you make, you can run them through the Query Plan tool. Following the same steps you would normally take to make the query selective should yield good results.

  • I understand left inner join is better than left anti join, but I have no idea how to apply it in my query scenario. I have edited exact query in the question. Any help on the same will be appreciated. Thanks – Sujit Tiwari Oct 7 '16 at 3:09

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