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
solarcookersolarcooker 

Improve query cost in trigger: problem with IsDeleted system field

Hi there,

I have the following exception with my trigger:
System.QueryException: Non-selective query against large object type (more than 100000 rows).
My SOQL query is like this: 
SELECT Id, Type__c, Assembled__c, Production_Center__c, 
	Transport_to__c, Quality_Control_Result__c, Quality_Control_Date__c 
	FROM OLI_Core__c 
	WHERE Production_Center__c = :centerId 
	ORDER BY Quality_Control_Date__c DESC LIMIT 8000
The OLI_Core__c object contains around 100942 records and Production_Center__c field is an indexed field.
After some research I use the following query to check the request's cost in query plan (developer console): 
SELECT Id FROM OLI_Core__c WHERE 
Production_Center__c != null AND Production_Center__c = 'a03D000000FrF4HIAV' 
ORDER BY Quality_Control_Date__c DESC LIMIT 8000
The query plan result is like the image bellow:
query plan screen shoot

I think that it's not possible to index IsDeleted field because it is a system field, and even if it's possible I think that's not a good idea because I will need to do like this with some other objects when records will be over 100K (which are used in other triggers).

Someone can help me to have a cost bellow 1 with Index as Leading Operation Type for this query considering the IsDeleted field problem. Or is there a workaround?

Thanks in advance for any kind of help.
 
Best Answer chosen by solarcooker
solarcookersolarcooker
Hi Alexander,

I have solved this problem by using CreatedDate (which is indexed by default) to get all recent records. Then I use IF statement to replace filter in WHERE clause and to get the same results.
This workaround is used until my request to Salesforce about custom indexing will be done.

Thanks for all,
Tsara

All Answers

Alexander TsitsuraAlexander Tsitsura

Hi solarcooker

Can u try to add "IsDelete" field in Where statement?

SELECT Id, Type__c, Assembled__c, Production_Center__c, 
	   Transport_to__c, Quality_Control_Result__c, Quality_Control_Date__c 
  FROM OLI_Core__c 
 WHERE Production_Center__c = :centerId AND IsDeleted = false
 ORDER BY Quality_Control_Date__c DESC LIMIT 8000
 



As a common practice, if your question is answered, please choose 1 best answer. 
But you can give every answer a thumb up if that answer is helpful to you.

Thanks
Alexander TsitsuraAlexander Tsitsura
I recommend you to add more conditions in where statement.
solarcookersolarcooker
Hi Alexander,
Thanks a lot for your answer.

I already tried to add IsDeleted field in WHERE statement but I had the same result.
User-added image

I had also added more conditions and received a low cost at TableScan as Leading Operation Type but the same Cost for Index.
User-added image
As I said earlier, this request is used in trigger and I think that selective query uses Index as Leading Operation Type and cost must be above 1.
I was already used this request in my trigger in prod and had the same exception (System.QueryException: Non-selective query against large object type (more than 100000 rows). 
SELECT Id, Type__c, Assembled__c, Production_Center__c,Transport_to__c, Quality_Control_Result__c, Quality_Control_Date__c FROM OLI_Core__c
WHERE Production_Center__c = :centerId AND IsDeleted = false
AND Assembled__c = false AND Quality_Control_Result__c = 'Good' 
ORDER BY Quality_Control_Date__c DESC LIMIT 8000
I'd like to note that only Production_Center__c and Transport_to__c are indexed but we don't need to filter Transport_to__c.


Please let me know if someone have solution because it's been almost a week since our production is blocked (can't input new records).
Alexander TsitsuraAlexander Tsitsura

Hi solarcooker

Maybe this help. You can some Audit dates (such as LastModifiedDate) in your WHERE statement.  
WHERE LastModifiedDate = LAST_N_DAYS:365

Thanks,
Alex
solarcookersolarcooker
Hi Alexander,

Thanks for your idea. I also have a similar idea by using CreatedDate in WHERE clause but I will try to custom index Assembled__c and Quality_Control_Result__c by the salesforce support first.

Regards,
Tsara
solarcookersolarcooker
Hi Alexander,

I have solved this problem by using CreatedDate (which is indexed by default) to get all recent records. Then I use IF statement to replace filter in WHERE clause and to get the same results.
This workaround is used until my request to Salesforce about custom indexing will be done.

Thanks for all,
Tsara
This was selected as the best answer