Need Help Optimizing SOQL Query For 100,000+-Record Table (old query, suddenly failing)

Question

Although my org seems to have had over 100,000 Task records for quite some time (as of this morning, we're at 119,479), between a week ago and yesterday two of the test methods that come with an AppExchange app called "Action Plans" suddenly started failing because the production query optimizer suddenly decided that a "Task" SOQL query these test methods invoke isn't optimal for 100,000+-record objects.

I am seeking assistance fixing the offending query so I can resume deploying code to production.

My questions are:

1. Any idea why now?
2. Any idea what I can do to fix this query? (Unless I comment out the two failing test methods, which is a pretty nasty way to "fix" the problem, I can't deploy any new code to production.)

---

---

Here's the offending code in a trigger called "ActionPlanTrigger.apxt" (which acts on a custom object that came with the app called "ActionPlan__c":

set<ID> apIds= new set<ID>();
set<ID> apttIds= new set<ID>();
set<ID> taskIds= new set<ID>();
List<Task> tasks = new List<Task>();

if( trigger.isDelete && trigger.isBefore ) {
    if ( !ProcessorControl.inBatchContext){
        // if action plan is deleted, delete all tasks associated with it
        for( ActionPlan__c ap : trigger.old ){
            apIds.add( ap.Id );
        }

        for( APTaskTemplate__c aptt : [ select Id from APTaskTemplate__c where Action_Plan__c in :apIds ] ){
            apttIds.add( aptt.Id );
        } 
        for( Task ap : [select Id from Task where Status != 'Completed' and TaskTemplateId__c in :apttIds ] ){ 
            taskIds.add( ap.Id );
        } 

        if( taskIds.size() > 0 ){
            ActionPlansTaskTriggerUtilities.deleteTasks( taskIds );
        }
    }
}

The offending query seems to be right in the middle - this one:

[select Id from Task where Status != 'Completed' and TaskTemplateId__c in :apttIds ]

The two test methods in ActionPlanCreationController.apxc that are acting up when run in production, "testActionPlanSave()" and "testActionPlanSaveAndNew()," each start with code that fires the trigger code above:

//Erase all Action Plans before begining test
List<ActionPlan__c> apList = [ select Id from ActionPlan__c limit 10000];
delete apList;

---

---

Reviewing the logs from the "testActionPlanSave()" method execution, 1285 ActionPlan__c IDs got passed to the trigger, which resulted in 6134 rows of APTaskTemplate__c (all of them) being selected by the 1st query in the "if." (No new APTaskTemplate__c records have been created in the last 2 months, so we haven't had a sudden increase in that number.)

Once the logs get to "line 20" of the trigger, which is where the "[select Id from Task where Status != 'Completed' and TaskTemplateId__c in :apttIds ]" call is, the "SOQL_EXECUTE_BEGIN" line refers to it as "SELECT Id FROM Task," which I assume means the production-org query optimizer decided to do this query as a full table scan.

I can see its logic. Task.Status is indexed, but it's a negative filter. Task.TaskTemplateId__c is indexed, but "in :apttIds" has 6,134 records in it.

But that's about as far as I've gotten - no idea "why the tests that invoke the query are suddenly failing now" or "how to actually fix the issue, because I really don't want to be that developer who just turns off the tests."

Thank you so much for your assistance!

-K

---

---

P.S. Including the following text for future searchability, as it's the log's error summary:

CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, ActionPlanTrigger: execution of BeforeDelete caused by: System.QueryException: Non-selective query against large object type (more than 100000 rows).

Answer 1

Three suggestions I can think of.

First, change your unit test class to at least version 28.0, and make sure you are using @isTest or @isTest(SeeAllData=false). This should give you isolation from production data, and probably allow your tests to pass.

Second, your query is non-selective because you are using both a NOT EQUALS operator, and a non-indexed custom field. You should probably describe the status picklist and select just the values you want specifically. This yields better performance than using NOT EQUALS, because that can't be optimized to use indexes and therefore automatically causes a full table scan if no other indexes are present. Something like this:

Set<String> statusValues = new Set<String>();
for(PicklistEntry entry: Task.Status.getDescribe().getPicklistValues()) {
    statusValues.add(entry.getValue());
}
statusValues.remove('Completed');
taskIds = new Map<Id, Task>(
    [SELECT Id FROM Task WHERE Status IN :statusValues AND TaskTemplateId__c IN :apttIds]
).keySet();

Third, presuming your custom field is a normal text field, because custom activity lookup fields is a recent feature, so you're probably not using it, means you need an index on that field. You can create an index on the field by visiting it in setup, editing it, and setting it as an External ID. This should allow the system to optimize the query by index, which will give you far better query performance as the data continues to grow. If that doesn't help, you can also contact support and have them create a custom index for you, or you might consider creating a new lookup field instead, and copying the existing data to the new field (you may need to contact support to enable this feature).