2

In my SOQL query in my Apex Batch start() methods I query and order

SELECT TextField__c 
FROM MasterDetailChildWith4MillionRecords__c 
WHERE Active__c = 'Yes' 
AND Revenue__c != 0 
AND Lookup__c = 'a1b50000000F6No' 
ORDER BY UP2GO_2F__txt_DLV_DLIId__c

Master Detail child records. This query times out after 2 minutes. Where does this problem come from? From the schere size of records (4 millions) or the ORDER BY?

Any experiences or hints?

|improve this question
  • Are any of the fields in the Where clauses External Ids ? – techtrekker Apr 10 '13 at 9:40
  • Sorry, no :( What should I make an external Id field? I guess only fields that are unique can become external ids?! I don't have such a field. – Robert Sösemann Apr 10 '13 at 9:54
  • 1
    Any field that is marked as an ExternalId is indexed for better querying performance, but you're right its desirable for such fields to have unique values for the filter to make sense, but its not mandatory to make them unique. Start meaning is this used in Database.QueryLocator ? That should be ok for 50 mill records. – techtrekker Apr 10 '13 at 9:59
  • 3
    From my experience i can say that when using the != the search has taken longer. Do you have any formula fields in the query? They are generated on the fly and slow down the search too. – Sergej Utko Apr 10 '13 at 10:37
  • 1
    Lookup should be already a good filter clause (it's like foreign key in normal database, I don't believe they're not indexed internally). Looks like "Active" won't benefit from indexing though if it has only 2 values for example. Try marking Revenue as ext id? – eyescream Apr 10 '13 at 11:07
1

This is probably because the fields you query in the WHERE clause aren't indexed, this way your query will take longer, and will eventually time out with large data sets.

I suggest taking a look at the following webinar, it explains perfectly how to handle big data sets, and what you can do to improve your query times.

Webinar: Extreme Salesforce Data Volumes

|improve this answer

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.