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?

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?

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?