0

The following query is performing very poorly with large amounts of events and large amounts of invited users to this specific event:

List<Event> eventsInRange = [
        SELECT Id, StartDateTime, EndDateTime
        FROM Event
        WHERE OwnerId IN :attendeeIds
        AND (
          (StartDateTime >= :selectedStartTime AND StartDateTime <= :selectedEndTime)
          OR (EndDateTime >= :selectedStartTime AND EndDateTime <= :selectedEndTime)
          OR (StartDateTime < :selectedStartTime AND EndDateTime > :selectedEndTime)
        )
        AND ShowAs != 'Free'
        ];

I know EndDateTime is not an indexed field, and I believe StartDateTime utilizes ActivityDate indexed field? But OwnerId is indexed and the query plan shows OwnerId should be driving the query.

Does anyone know what exactly may be causing this query to execute so slowly? Any pointers on speeding it up?

2
  • As far as I can see, neither StartDateTime, EndDateTime, nor ActivityDateTime are indexed (and they are 3 separate fields for Event). That aside, what do you mean by 'poor performance'? Poor compared to what? How much time (in milliseconds) is your query taking? – Derek F Aug 28 '17 at 20:30
  • Well, I had it reported that someone waited about 37 seconds. Then I tried and it timed out at 142 seconds. Then had it take, like 16-20 seconds another time... But of course, after doing it, doing it again was almost instant because it was cached. – Tyler Dahle Aug 28 '17 at 20:33
6

Having previously worked on a project where we needed to find events in a certain range, I can tell you that there's an optimized way to write this query:

List<Event> eventsInRange = [
    SELECT Id, StartDateTime, EndDateTime
    FROM Event
    WHERE OwnerId IN :attendeeIds
    AND StartDateTime <= :selectedEndTime
    AND EndDateTime >= :selectedStartTime
    AND ShowAs != 'Free'];

You might need to work it out on paper to prove it to yourself, but this should give you better performance than you're getting now.

4
  • Thanks for the updated query. I see how it is working. Do you know of any way to really test queries? It becomes almost impossible to really test their performance after the data gets cached the first time a query runs. – Tyler Dahle Aug 28 '17 at 20:39
  • @TylerDahle There really isn't a good way of doing it, because there's several layers of cache to deal with (disk cache + memory cache + database cache), so there's not much we can do except to try and optimize as best we can. If this doesn't work out well, you might have to contact support to see if they can use their tools to give you even better performance. – sfdcfox Aug 28 '17 at 20:44
  • That is unfortunate. Wish they would provide some way to test query performance! Thank you for the optimized query, I am seeing how that goes and if users still report a huge time issue. Otherwise I was thinking about using ActivityDate (which is indexed) and getting everything 14 days before and 14 days after the start selection (since events can't be more than 14 days) and filter them out through a loop... Not sure if that would help much, but it would be using only Indexed fields. – Tyler Dahle Aug 28 '17 at 21:28
  • You could use a shiny new scratch org to test. Offload data in Json and import it back. #SalesforceDX – stwissel Aug 30 '17 at 1:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.