function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Blake TanonBlake Tanon 

Query optimiazation

I'm running into time limits with query around tasks, one of the transactions I'm trying to execute is gathering a count of tasks that fall in the below query.  The query is run in a trigger when (1) only 1 record is being updated and (2) when a checkbox is true.

-----
for(aggregateResult r:[SELECT count(id), ownerid FROM task WHERE isclosed = true and month_ending__c =: me AND ownerId =: uid
       AND outcome__c = 'Success' AND call_score__c in ('1','2')
       AND activity_type__c = 'Call'
       GROUP BY ownerid  LIMIT 300])

 s.Score_Value__c = integer.valueof(r.get('expr0'));
-----

The trigger is run off a custom object called Scorecard__c
  • Variables:
    • me = Date, Month_ending(date) on scorecard__c
    • uid = id, custom user field on scorecard
  • Task fields
    • month_ending__c = date field
    • outcome__c = picklist
    • call_score__c = picklist
    • activity_type__c = text/external ID
There are 767,147 tasks in SFDC as of right now.  I can't figure out how to make this query more selective, it was a custom indexed field (activity_type__c) and a standard index (ownerid).  Neither hit the optimizer threshold limit.

Any thoughts?

Chidambar ReddyChidambar Reddy
Hi Blake,

I think it is giving the problem due to call_score__c in ('1','2')


As there are many tasks in your Org, You can break the query into pieces.


like :

Map<id,Task> map1 = [SELECT id FROM task WHERE isclosed = true and ownerId =: uid AND                                                                               activity_type__c = 'Call'];

Map<id,Task> map2 = [ SELECT id FROM task WHERE Id IN : map1.keyset() AND month_ending__c =: me
                                             AND outcome__c = 'Success'  ]; 

 I dont think you need groupby as you are only seaching for one Owner.(uid)

for(aggregateResult r:[SELECT count(id), ownerid FROM task WHERE Id IN : map2.keyset()                                                                                      AND call_score__c in ('1','2') LIMIT 300 ]){


//do your logic

}


-Thank you