You need to sign in to do that
Don't have an account?
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
How to Optimize below Query
Thank you in Advance
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
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
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