I have batch which queries for record in a standard object which has 16 million of records. I am trying to query only few records with checkbox checked using querylocator to delete it .
However the query takes too long to process and times out.
The query used is "Select id, Delete_Social_Post__c,xxx_c,ExternalId_c, CreatedDate From SocialPost where Delete_Social_Post__c = true Order by createddate asc "
Rgds
In the Developer Console, there is an option to view the Execution Overview. The way I went about getting to it was opening my debug log in the console, then -> Edit -> View Log Panels (CTRL +P). A 'Select Panels' window appears and I checked Execution Overview. I selected Executed Units, then made only the Queries show as in the image below.
Hope this helps!
Take a look at Working with Very Large SOQL Queries. Querying for field != null will hurt your performance.
Typically, a custom index isn’t used in these cases.
- The queried values exceed the system-defined threshold.
- The filter operator is a negative operator such as NOT EQUAL TO (or !=), NOT CONTAINS, and NOT STARTS WITH.
- The CONTAINS operator is used in the filter, and the number of rows to be scanned exceeds 333,333. The CONTAINS operator requires a full scan of the index. This threshold is subject to change.
- You’re comparing with an empty value (Name != '').
I don't have 25M records to test against, but I believe you might have better luck creating a formula along the lines of Is_Missing_Account__c:
AND(ISNULL(AccountId), ISNULL(accountrefId__c))
If the above does not work, you can use an Apex Trigger, a Workflow Rule, or Process Builder to sync an analogous checkbox. You will need to run a back-fill batch, but once you do that, you can just filter on this custom checkbox instead and it should speed things up.
Another option is to populate an Account_Count__c (formula or otherwise) number field with 0, 1, or 2 depending on these parent relationships. Then filter Account_Count__c = 2.
Best Answer
You can improve your performance with selecting indexed fields - you should read about performance patterns like this:
Improve performance with Custom indexes using Selective SOQL Queries