Event Relation SOQL Query Taking absurd amount of time first time, then a second any other time?

Question

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!

Answer 1

The problem you're having appears to me to be in the way the query was written. I don't think this was intentional, but the WHERE clause forces all of the database to be read. With a large database, you have a non-selective query.

The issue is in this part of your query:

((Event.StartDateTime < THIS_WEEK AND Event.EndDateTime = THIS_WEEK)    
   OR 
   (Event.EndDateTime > THIS_WEEK AND Event.StartDateTime = THIS_WEEK)
   OR 
   HERE---> (Event.ActivityDate < THIS_WEEK AND Event.EndDateTime > THIS_WEEK)
   OR 
   (Event.ActivityDate = THIS_WEEK AND Event.EndDateTime = THIS_WEEK))

It appears you perhaps intended to write the line marked with HERE---> as:

(Event.ActivityDate < THIS_WEEK AND Event.EndDateTime = THIS_WEEK)

which would be consistent with the way you wrote the first two clauses. Because you didn't, you've essentially written a clause that's the equivalent of:

(Event.StartDateTime < THIS_WEEK AND Event.EndDateTime > THIS_WEEK)

When combined with your first two clauses, that means you're querying every Event in the database that contains those RelationIds until the limit of 50k records is hit.

If you correct that line to use the = instead of > and/or omit the last two clauses entirely, I think you'll greatly improve your performance. I'm genuinely not certain why you'd be querying both the StartDateTime and the ActivityDate as they should both resolve to the same date.

EDIT

In response to comments:

I think there are a couple of different ways to make your query more "selective" that you should consider.

• First, I recommend that you use the Query Optimization Tool available in the Developer Console (you may need to enable it first).

• Since the start and end date can only span 14 days, this is helpful. It also must include This_WEEK if the start or end date isn't in it. That means it can't be an earlier start date (or Activity Date) than Today() + LAST_N_DAYS:14.

• Similarly, you can't use an end date that's greater than Today() + NEXT_N_DAYS:14 (in both cases, you can omit the Today(), I'm simply using it for specificity).

• The combination of the above two criteria creates a window of 28 days.

• If neither the StartDate nor the EndDate falls within THIS_WEEK, the StartDate MUST have been sometime during LAST_WEEK and the EndDate must occur sometime during NEXT_WEEK since the EVENT spans THIS_WEEK. That's because neither can be more than 7 days outside of THIS_WEEK.

• The above limits the time search window to a period of 21 days. As such, I think this is the criteria that will lead to being the most useful to you and will also be the MOST selective.

Based on this reasoning, here is what I'd try using to replace all of your date clauses:

((Event.StartDateTime < NEXT_WEEK OR Event.StartDateTime >= LAST_WEEK) AND (Event.EndDateTime > Last_WEEK OR Event.EndDateTime <= NEXT_WEEK)) 

This covers a 21 day time period where the EndDate falls within a 2 week period that can only be in either This_Week or Next_Week. Similarly, the StartDate can only be in either This_Week or Last_Week. This combination allows for the possibility for both not to occur during this week. I'd expect this to be much simpler for the query engine to evaluate.