+ Start a Discussion
Sai Ram ASai Ram A 

SOQL Optimization to reduce time consuming to execute query with OR Filter Condition

Hi All

Please Suggest the approaches or help in optimizing the Logic/ Query to reduce time Consuming in executing the Order Object Query ( which is taking 10 minutes), this is effecting user experience 
 
//ListMethod

public list<orders__c> getstart() {
	Map<Id,UserTerritory> UserTerritoryCurrentUserMap = new  Map<Id,UserTerritory>([Select u.UserId, u.TerritoryId, u.IsActive, u.Id  From UserTerritory u Where u.isActive=true and u.userId =: UserInfo.getUserId()]);
    system.debug('-----UserTerritoryCurrentUserMap SIZE-------'+UserTerritoryCurrentUserMap.size());
    
    set<Id> TerritoryIdSet = new set<Id>();
    for(UserTerritory ut:UserTerritoryCurrentUserMap.values())
    {
          TerritoryIdSet.add(ut.TerritoryId);
    }    
    
    List<Territory> childTerritories = [Select Id from Territory where ParentTerritoryID in :TerritoryIdSet];
   
    for(Territory ct : childTerritories) {
         TerritoryIdSet.add(ct.Id);
    }
    
    system.debug('-----TerritoryIdSet -------'+TerritoryIdSet);
    system.debug('-----TerritoryIdSet SIZE-------'+TerritoryIdSet.size());

    list<Group> map_group = [Select Id, RelatedId from Group where (Type='Territory' OR Type='TerritoryAndSubordinates') AND RelatedId IN : TerritoryIdSet];
    system.debug('-----map_group -------'+map_group);
	
	Set<String> tempList = new Set<String>();

    for(My_Custom_Object__c s : [SELECT Legacy_Field__c , Account__c FROM My_Custom_Object__c WHERE Account__c IN (Select  AccountId FROM AccountShare where ( UserOrGroupId IN : map_group OR  UserOrGroupId =:UserInfo.getUserId()) AND RowCause IN ('Territory', 'TerritoryManual', 'TerritoryRule')) ]) {
        tempList.add(s.Legacy_Field__c);
    }
    
    system.debug('-----tempList-------'+tempList);
    system.debug('-----tempList-------'+tempList.size()); 
	
	List<orders__c> orders =[SELECT id, Name, Orders__c, Bill_to__c, Payer__c, Ship_To__c, Sold_to__c FROM orders__c WHERE Bill_to__c IN: tempList OR Payer__c IN: tempList OR Ship_To__c IN: tempList OR Sold_to__c IN: tempList];                                     
	system.debug('-----orders-------'+orders);
	
	return orders;
	
}

How to Optimize below Query
 
List<orders__c> orders =[SELECT id, Name, Orders__c, Bill_to__c, Payer__c, Ship_To__c, Sold_to__c FROM orders__c WHERE Bill_to__c IN: tempList OR Payer__c IN: tempList OR Ship_To__c IN: tempList OR Sold_to__c IN: tempList]; 
system.debug('-----orders-------'+orders);

Thank you in Advance
Terence_ChiuTerence_Chiu
How many records on average does this query return ? Can you be more selective?

Are any of those filter fields lookup fields ? Lookup fields are indexed which would help with query performance. If not you can contact support to index the fields. 

See the below article regarding large soql queries.

https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_SOQL_VLSQ.htm
Sai Ram ASai Ram A
Hi Terence

On Average 8000 rows are returned out of 590000 rows; No, All the Fields are Text datatype. 
SFDC FAQ: https://help.salesforce.com/apex/HTViewSolution?urlname=Force-com-Query-Optimizer-FAQ

Indeed i would reach SFDC Support for index field & Skinny Tables. 

Thankyou
Sai