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.

  • 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.

| improve this answer | |
  • 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.