We are having an issue where we need event relations for people(s), and are having problems with this very large group of people having almost 400 total event relations in this one week we are testing on... When trying to grab this large groups event relations, it will take forever and possibly time out. However, if you try again right after a timeout it goes in a couple seconds and is great. I was thinking this was salesforce just chaching the soql query/information and so it could act very quickly the second time. I tried to kind of trick it into having this query cached and ready by having a batch job that ran regularly to query every members event relations so when they tried to access our app the timeout issue would stop.
However, this is not even appearing to work. Even though the batch is running correctly and querying all these event relations, when you go to the app after a while without using it, it will still timeout or take very long the first time then be very quick after that.
Is there a way to successfully keep this cached so it will run very quickly when a user goes and tries to see all the event relations of a large group of people? With the developer console we saw that the event relation query was the huge time suck in the code and the real issue. I have been kind of looking into the Platform Cache of salesforce. Would storing this data there provide the solution I am looking for? (This particular query for event relations takes 20 seconds to possibly the timeout of 2 minutes... where querying events that returned more records took about 1.3 seconds).
And the query in question is:
List<EventRelation> allRelationsList = [SELECT EventId, RelationId FROM EventRelation WHERE RelationId IN :memberIdList AND ((Event.StartDateTime < THIS_WEEK AND Event.EndDateTime = THIS_WEEK) OR (Event.EndDateTime > THIS_WEEK AND Event.StartDateTime = THIS_WEEK) OR (Event.ActivityDate < THIS_WEEK AND Event.EndDateTime > THIS_WEEK) OR (Event.ActivityDate = THIS_WEEK AND Event.EndDateTime = THIS_WEEK)) AND Event.IsChild = false LIMIT 50000];
Where memberIdList is just a list of Strings that are user Ids so I am only grabbing event relations related to certain people depending on the group that was selected.
Update: After being informed the problem largely was in the part of the query checking for start dates before the view and end dates after the view... my updated query is this:
List<EventRelation> allRelationsList = [SELECT EventId, RelationId FROM EventRelation WHERE RelationId IN :usersToFilter AND ((Event.ActivityDate <= :startJustDate AND Event.ActivityDate >= :earliestStart AND Event.EndDateTime >= :endJustDate AND Event.EndDateTime <= :latestStart) OR (Event.ActivityDate >= :earliestStart AND Event.ActivityDate <= :startJustDate AND Event.EndDateTime <= :endJustDate AND Event.EndDateTime >= :startJustDate) OR (Event.ActivityDate >= :startJustDate AND Event.ActivityDate <= :endJustDate AND Event.EndDateTime >= :endJustDate AND Event.EndDateTime <= :latestStart) OR (Event.ActivityDate >= :startJustDate AND Event.EndDateTime <= :endJustDate)) AND Event.IsChild = false LIMIT 50000];
It loaded the problematic group in about 11 seconds, which isn't perfect, but better than timing out after two minutes. I am very open to any further optimizations anyone may have for this. My need is to get all events that start and end within the current view date range, events that may start in the current view and end outside it, events that end in the current view but start outside it, and events that start and end outside the view, but pass through the view so should still be visible. Which is what my query explicitly looks for. It still takes a fare chunk of time though, but not sure if there is really a good way around this.
earliestStart is simply 15 days before the start of the view date range and lateststart is just 15 days after the end of the view date range, which utilizes Salesforce rule that events cannot span more than 14 days to narrow how far back and how far forward this query is looking for event relations.
But like I said, if anyone has further optimizations that could really speed up this new query, I would love to hear about it!