Improving this SOQL query?

Question

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?

Answer 1

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.