You need to sign in to do that
Don't have an account?
Sai 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
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