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:
- Any idea why now?
- 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).