Apex Code Development You need to sign in to do that
Don't have an account?
Marshal Miller 7 Non-selective query against large object type (more than 200000 rows)
It is happening on the Fee__c object but the query results are very low around 5-10 so I dont understand why I get this error. I am new to Apex so I could really use some professional advice.
trigger PortalInvoice on Task (before insert) {
List<Invoice__c> I = new list<Invoice__c>();
List<Fee__c> FeeList = [SELECT Fee_Amount__c, Fee_Description__c, Comment1__c, Transaction_Key__c, Comment2__c FROM Fee__c WHERE Transaction_Date__c = TODAY AND Financial_Account__r.Fee_Method__c = 'I-Invoice'
AND (NOT Transaction_Code__c LIKE '5592%')
AND (NOT Transaction_Code__c LIKE '6492-22%')
AND (NOT Transaction_Code__c LIKE '6492-20%')
AND (NOT Transaction_Code__c LIKE '6492-21%')
AND (NOT Transaction_Code__c LIKE '6492-01%')
AND (NOT Transaction_Code__c LIKE '6492-03%')
AND (NOT Transaction_Code__c LIKE '4492%') ];
Decimal amountValue = 0.00;
If(!FeeList.isEmpty()){
amountValue = FeeList[0].Fee_Amount__c;
}
For(Task Tk : Trigger.new){
if(Trigger.isInsert){
if(Tk.Subject == 'Invoice Sent' && Tk.Status == 'Completed' && Tk.Type == 'Email' && FeeList.size() > 0){
Invoice__c INV = new Invoice__c();
INV.Amount__c = amountValue;
INV.Comments__c = Tk.Description;
INV.Financial_Account__c = Tk.Whatid;
INV.Invoice_Date__c = Date.today();
I.add(INV);
for(Fee__c F : FeeList){
Inv.Fee_Description__c = String.valueOf(F.Fee_Description__c);
Inv.Transaction_Number__c = F.Transaction_Key__c;
INV.Billing_Notes__c = F.Comment1__c + ', ' + F.Comment2__c;
}
}
}
insert I;
Update I;
}
}
You are recieving this error because the SOQL query being used in your Apex code, returns a huge number of records, such that the limit on the heap size is violated and the error occurs. Salesforce SOQL Query engine has a defined threshold on the number of records that should ideally be returned in the result set. Whenever, the Query Engined identifies that this threshold is violated, it raises the given error. The error is aimed at optimizing the Soql Query so that it returns the expected result set below the query engine's system threshold.
You would need to optimize your SOQL query by adding specific filters or techniques such that it returns only the expected result set.
Few simple things you could do to optimize your query are as follows:
1. Use a Soql For Loop to iterate on your result set. The Soql for loop uses queryMore() which runs once for a chunk of every 200 records in the returned result set thus helping avoid the hitting the limits.
for(Fee__c : <Your_Soql_Query>){ //Your fee processing logic goes here }2. Revisit your WHERE clause filters and instead of using a NOT Transaction_Code__c LIKE '5592%' you could identify the exact set of Transaction Codes and pu them in a Set which could used to filter out the results. For e.g. Transaction_Code__c IN <Set_of_Transaction_Codes>3. Use Indexed fields or standard fields, to filter the Soql query result set. Have Transaction_Code__c marked as a External Id or an Unique field if applicable so that it is an indexed field.
4. Strip of records that do not have values populated for the Transaction_Code__c field.
5. Add a LIMIT Clause to ensure only a specific number of records are returned.
Refer the following articles to understand further about making your queries selective
- https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_SOQL_VLSQ.htm
- https://help.salesforce.com/articleView?id=How-to-make-my-SOQL-query-selective&language=en_US&type=1
Hope this helps.
Please DO NOT forget to mark this thread as SOLVED if this ANSWER helps resolve your issue.
A little additional comment ... it isn't necessarry that the query "returns" more than 200.000 records. This error occurs if you are accessing to object that has more than 200.000 records. So althougth your query get only 1 record and your where fields aren't indexed the error will throws anyway.
Usually you can also try to delete some unnecesary records and use only one filter in your where clause that selects records using an external key field. i.e. you will need to join some fields to construct a "bussiness" unique key.