Mixing
Query optimization for ContentVersion
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
6
down vote
favorite
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:
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 [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.
apex soql performance content contentversion
asked Sep 3 at 9:26
Novarg
3,3191134
add a comment |Â
up vote
6
down vote
favorite
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:
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 [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.
apex soql performance content contentversion
asked Sep 3 at 9:26
Novarg
3,3191134
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 at 14:00
add a comment |Â
up vote
6
down vote
favorite
up vote
6
down vote
favorite
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:
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 [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.
apex soql performance content contentversion
asked Sep 3 at 9:26
Novarg
3,3191134
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:
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 [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.
apex soql performance content contentversion
asked Sep 3 at 9:26
Novarg
3,3191134
asked Sep 3 at 9:26
Novarg
3,3191134
asked Sep 3 at 9:26
Novarg
3,3191134
asked Sep 3 at 9:26
Novarg
3,3191134
3,3191134
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 at 14:00
add a comment |Â
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 at 14:00
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 at 14:00
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 at 14:00
add a comment |Â
2 Answers
2
active
oldest
votes
up vote
2
down vote
Put the 2 boolean conditions before the id conditions. It might speed up your query.
answered Sep 3 at 9:38
N. Germeau
366
4
Have you any evidence that that will help?
– Keith C
Sep 3 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 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 at 15:16
add a comment |Â
up vote
1
down vote
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.
answered Sep 3 at 13:34
Keith C
90.5k1082184
isn't settingMy_Custom_Object_Id__cas External ID same as asking the support to add the index to this field? If I am not mistaken, External IDs are indexed. And regardingContentDocumentLink: 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 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 at 14:28
add a comment |Â
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
2
down vote
Put the 2 boolean conditions before the id conditions. It might speed up your query.
answered Sep 3 at 9:38
N. Germeau
366
4
Have you any evidence that that will help?
– Keith C
Sep 3 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 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 at 15:16
add a comment |Â
up vote
2
down vote
Put the 2 boolean conditions before the id conditions. It might speed up your query.
answered Sep 3 at 9:38
N. Germeau
366
4
Have you any evidence that that will help?
– Keith C
Sep 3 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 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 at 15:16
add a comment |Â
up vote
2
down vote
up vote
2
down vote
Put the 2 boolean conditions before the id conditions. It might speed up your query.
answered Sep 3 at 9:38
N. Germeau
366
Put the 2 boolean conditions before the id conditions. It might speed up your query.
answered Sep 3 at 9:38
N. Germeau
366
answered Sep 3 at 9:38
N. Germeau
366
answered Sep 3 at 9:38
N. Germeau
366
answered Sep 3 at 9:38
N. Germeau
366
366
4
Have you any evidence that that will help?
– Keith C
Sep 3 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 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 at 15:16
add a comment |Â
4
Have you any evidence that that will help?
– Keith C
Sep 3 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 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 at 15:16
4
4
Have you any evidence that that will help?
– Keith C
Sep 3 at 13:30
Have you any evidence that that will help?
– Keith C
Sep 3 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 at 14:07
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 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 at 15:16
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 at 15:16
add a comment |Â
up vote
1
down vote
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.
answered Sep 3 at 13:34
Keith C
90.5k1082184
isn't settingMy_Custom_Object_Id__cas External ID same as asking the support to add the index to this field? If I am not mistaken, External IDs are indexed. And regardingContentDocumentLink: 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 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 at 14:28
add a comment |Â
up vote
1
down vote
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.
answered Sep 3 at 13:34
Keith C
90.5k1082184
isn't settingMy_Custom_Object_Id__cas External ID same as asking the support to add the index to this field? If I am not mistaken, External IDs are indexed. And regardingContentDocumentLink: 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 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 at 14:28
add a comment |Â
up vote
1
down vote
up vote
1
down vote
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.
answered Sep 3 at 13:34
Keith C
90.5k1082184
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.
answered Sep 3 at 13:34
Keith C
90.5k1082184
edited Sep 3 at 13:40
answered Sep 3 at 13:34
Keith C
90.5k1082184
answered Sep 3 at 13:34
Keith C
90.5k1082184
answered Sep 3 at 13:34
Keith C
90.5k1082184
90.5k1082184
isn't settingMy_Custom_Object_Id__cas External ID same as asking the support to add the index to this field? If I am not mistaken, External IDs are indexed. And regardingContentDocumentLink: 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 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 at 14:28
add a comment |Â
isn't settingMy_Custom_Object_Id__cas External ID same as asking the support to add the index to this field? If I am not mistaken, External IDs are indexed. And regardingContentDocumentLink: 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 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 at 14:28
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 at 14:00
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 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 at 14:28
@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 at 14:28
add a comment |Â
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsalesforce.stackexchange.com%2fquestions%2f231029%2fquery-optimization-for-contentversion%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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 at 14:00