6

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_Linked_to_My_Custom_Object__c and Is_Proj_Document__c are just simple checkboxes and My_Custom_Object_Id__c is a Text(18).
Query planner gives the following results:

enter image description here

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:

enter image description here

Debug log of this query execution earlier today:

07:58:05:004 SOQL_EXECUTE_BEGIN [40]|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 [40]|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.

  • Why haven't you created a lookup instead of the My_Custom_Object_Id__c text field that stores the Id of the parent anyway? Lookups are indexed by default which might help. – Robin De Bondt Sep 3 '18 at 14:00
2

Put the 2 boolean conditions before the id conditions. It might speed up your query.

  • 4
    Have you any evidence that that will help? – Keith C Sep 3 '18 at 13:30
  • It will help but, it is hard to tell at which point. The boolean conditions are the quickest ones to evaluate (it 0 or 1). After that, comes the Id and other indexed fields. So, putting the boolean conditions first in the "where" part might help as less comparisons will be done on Id because it is a AND clause. – N. Germeau Sep 4 '18 at 14:07
  • You are assuming that the query optimiser makes use of the order of the terms in the original query which while possible is unlikely. – Keith C Sep 4 '18 at 15:16
1

Assuming the number of matching results are small, avoiding a table scan is the usual way to speed a query up where there are a very large number of rows in a table.

You have:

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

where only the ContentVersion.OwnerId will have an index by default. I suggest you request that Salesforce support adds a custom index to your ContentVersion.My_Custom_Object_Id__c field as that should allow the right data to be found without a table scan via that index.

See the knowledge article Improve performance with custom indexes using selective SOQL queries for some info on what to include in your case.

Note that a more conventional way to relate content to a custom object is via the standard ContentDocumentLink object.

  • isn't setting My_Custom_Object_Id__c as External ID same as asking the support to add the index to this field? If I am not mistaken, External IDs are indexed. And regarding ContentDocumentLink: the required logic is that we also query files that are not linked to any record(except for a current user), so that was not an option. – Novarg Sep 3 '18 at 14:00
  • @Novarg You are right about the external Id - I missed that part in your question. I was assuming you did have a record inside Salesforce but sounds like you don't so fair enough. – Keith C Sep 3 '18 at 14:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.