For a project I wrote a query to get all needed
ContentVersion records. But after the deployment to full sandbox we have seen the performance issues with it.
It only happens for the first time, all next executions of this query are much faster(I assume it's caching). But the client wants it to always be fast, regardless if it's the first or 100th execution of this query.
The query is:
List<ContentVersion> cvList = [SELECT Id, Description, Title, OwnerId, ContentDocumentId, Email_Subject__c FROM ContentVersion WHERE OwnerId = :UserInfo.getUserId() AND Is_Linked_to_My_Custom_Object__c = false AND Is_Proj_Document__c = true AND My_Custom_Object_Id__c = :mcoId];
Is_Proj_Document__c are just simple checkboxes and
My_Custom_Object_Id__c is a Text(18).
Query planner gives the following results:
As you can see, the costs are under 1, so the query is selective.
I also tried changing
My_Custom_Object_Id__c to External Id to make it indexed, but that also added "Other" operation with cost of 4.9:
Debug log of this query execution earlier today:
07:58:05:004 SOQL_EXECUTE_BEGIN |Aggregations:0|SELECT Id, Description, Title, OwnerId, ContentDocumentId, Email_Subject__c FROM ContentVersion WHERE (OwnerId = :tmpVar1 AND Is_Linked_to_My_Custom_Object__c = FALSE AND Is_Proj_Document__c = TRUE AND My_Custom_Object_Id__c = :tmpVar2) 07:58:22:752 SOQL_EXECUTE_END |Rows:0
It took 17.7 seconds without any retrieved records.
What can I do in order to speed up the initial query?
The problem is that it's fairly difficult to test since the caching will just keep the query fast for a long time before I can try it again.