function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Patrick Thai 14Patrick Thai 14 

Preventing SOQL query timeout

Hello,

We regularly export ou Contacts to send them. We use the data loader in command line to query the contacts that were not updated by the tech user, with the following SOQL query

select Id, [some other fields]
FROM Contact
WHERE LastModifiedDate = LAST_N_DAYS:3
AND LastModifiedById != '[Id of the tech user]'


Because there are more than 10 millions records in the database (but only a few of the in the expected result), the query takes some time (14s) and even times out. We tried to optimize the query by checking the developer guide ( https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_SOQL_VLSQ.htm) with a couple of solutions of our own.

For instance we tried to use a "LastModifiedBy.ProfileId IN (...)" filter. We also created a checkbox to turn the negation form into a postive form : "LastModified.TechnicalUser__c == true". But in both cases, the result was slower (18s) than our first query and still had some timeout.

I was thinking about asking the support for a custom index on the User.TechnicalUser__c field but this can only be requested by their developer's team. Is there any way to improve the performance our query?
Patrick Thai 14Patrick Thai 14
Juste an update for people to be aware of our attempts. Even though there is no such suggestion in the documentation, we thought that a reference to another object could slow down the query. So we created a text field that is populated by a workflow with a "true" value, except if the updating user is the tech user. In this case we set the text field to a blank value. The Result was infortunately the same as our previous try (18s).