1

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

  • I suspect TaskTemplateId is a text field, not a lookup, right? – sfdcfox Jan 13 '16 at 18:03
  • sfdcfox: It's a set of Id... Id casts to string, but does it allow an in query on a set of them? k.. what happens if you put the status filter after the relationship filter? IIRC filters are fired in order and that negative filter might be causing issues. – drakored Jan 13 '16 at 18:07
  • Also a good reference for others (sounds like you already have most of this understood k..: s3.amazonaws.com/dfc-wiki/en/images/0/0e/… – drakored Jan 13 '16 at 18:08
  • Nevermind, just read the 6,000+ Ids bit. Wow.. hmm.. Can you flip the task status filter to a positive filter and get different results w/ Task.Status IN (...everything but completed...). It's a picklist, so I'd imagine it could do this in the optimizer, but maybe it can't because you can fill in non-picklist values into a picklist field. That probably means it needs to query all with a matching Id, then go through that list and remove status closed items. – drakored Jan 13 '16 at 18:16
  • @drakored The NOT EQUALS bit causes a table scan if no other filters use indexes, and the custom text field isn't indexed from what I can tell, so the result is a full table scan. See my answer for what I suspect is going on. – sfdcfox Jan 13 '16 at 18:18
3

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

  • You second point is what I was questioning in my last comment to the OP. Are my suspicions there correct? Have you seen it documented that it works that way? It also might be worth adding that the LIMIT 10000 is practically worthless since it queries all matching records and then just chops it off at the limit. – drakored Jan 13 '16 at 18:18
  • Assuming "TaskTemplateId__c" is indexed field, "Not" operator will still cause full table scan? – Jitendra Zaa Jan 13 '16 at 18:20
  • Nevermind, I read your comment in question, Thanks – Jitendra Zaa Jan 13 '16 at 18:21
  • Hi everyone, and especially @sfdcfox - thanks so much for the help. You saved my bacon. --- 1. sfdcfox's code into the trigger solved the problem on its own. --- 2. I also tried changing the v# of the class w/ the tests from v20 to v23 (wanted seeAllData; didn't want to refactor a mixed-type-of-method class) & tried it against no-change-to-trigger. Failed. --- 3. TaskTemplateId__c indeed a custom Activity field ("Text(255)(External ID)"), and the "Indexed" box is checked. --- 4. Does anyone happen to know how to reach someone at Salesforce Labs so I can get this suggested fix to them? – k.. Jan 13 '16 at 20:31
  • 1
    @k.. I think a low cardinality on the indexed field may have prompted a full table scan, so I'm glad the "in statusValues" bit fixed it for you. I have no idea how to contact whoever runs Salesforce Labs, but you might twitter @asksalesforce and see what they think. You can copy me at @brianmfear, I'd be interested in knowing how to reach them, too. – sfdcfox Jan 14 '16 at 0:00

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.