1

I've got a SOQL query that takes a very long time to run that's part of a larger Apex class. Below is a quick explanation of our org setup, the purpose of the query, and the query itself. Any help to optimize the query or an alternate path to get to the data would be greatly appreciated.

Custom objects:

Sequence__c = sequences are the line items on a contract, think of it like an itemized receipt. Sequences are the child records of Contract__c records (we do not use the standard Contracts object).

Panel_Maintenance__c = Panel_Maintenance__c records represent individual panel structures which can be booked/leased by customers for specific amounts of time. Each Panel_Maintenance__c record represents a unique piece of our inventory.

Panel__c = Panel__c records represent proposed or sold bookings for a particular panel for a particular time period. Panel__c records have a lookup relationship to Panel_Maintenance__c, but are actually children of Proposal__c records. Originally Panel__c records were meant to represent proposed bookings only, but recently our Sequence Apex class was updated to insert Panel__c records to represent sold bookings. All panel records have a parent proposal record, but only some have a lookup relationship to a sequence record.

Purpose of the Query:

This query is part of an Apex class created for an Aura component which allows users to identify Panel_Maintenance__c panels that are unsold for a specific time period. Since Panel__c records are created for proposed and sold bookings, the Apex class is ultimately trying to identify records that do NOT exist. For example there could be a Panel__c record indicating a panel is booked from October 1 through October 28, and another Panel__c record indicating that same panel is booked from November 16 through December 13, but what I really care about is that there is NO Panel__c record indicating that same panel is sold between October 28 and November 16. The panel is "unsold" during that time.

This particular SOQL query takes the users office location and a start and end date entered by the user to search for SOLD panel records. After this query is complete, the Apex class stores the panels' external ids in a set, then performs a separate (not shown) SOQL query for the unique panel records in Panel_Maintenance__c for panels NOT in the sold panel id set.

To qualify as a "sold panel", the panel record must have a lookup relationship to a sequence record, and the sequence record must have a status of 'Active' or 'Expired'. Additionally, the panel's start or end date must overlap with the dates provided by the user.

Query

SELECT Panel_ID__c, Sequence__r.Status__c, Sequence__r.Contract__c

FROM Panel__c

WHERE Plant_Number__c=`userdata` AND (Start_Date__c <`userinput` OR End_Date__c >`userinput`)
                      AND Sequence__c!=Null AND (Sequence__r.Status__c ='Active' OR 
                      Sequence__r.Status__c='Expired')

Query Plan results

0

Interesting question. I can tell you that any query for NOT values typically bypasses the standard indexes, which causes a full table scan. Sequence__c!=Null is an issue. Is it a valid case for records to not have a Sequence or Panel?

Consider a formula field that compresses the null check and Sequence__r.Status__c onto the Panel__c, so you don't have to traverse objects.

Also consider limiting the user's input so they can't provide a date range that's unrealistically wide.

1
  • Adding the formula field as you suggested allowed me to query a single object and cut down on the time drastically. As someone else pointed out, the Sequence status in my WHERE clause makes the Sequence__c!=Null redundant since status only exists if the sequence exists. Thank you! – Josh Oct 15 '20 at 17:03
0
SELECT Panel_ID__c, Sequence__r.Status__c, Sequence__r.Contract__c
FROM Panel__c
WHERE Plant_Number__c=userdata 
      AND (Start_Date__c <userinput OR End_Date__c >userinput) 
      AND Sequence__c!=Null 
      AND (Sequence__r.Status__c ='Active' OR Sequence__r.Status__c='Expired')

This clause

  AND Sequence__c!=Null 

is a primary suspect. Filtering based on null in most cases results in indexes not being used or forcing a table scan. Here, you don't need this clause at all - the following clause subsumes it anyway.

I would start by removing this clause and seeing if that's enough to get the query performant. While the next obvious step would be to index Plant_Number__c, the query planner's cost suggests you might be able to get away without that for now, although it will become necessary as your data volume continues to grow.

3
  • I think the most restrictive filter is used for finding the cost. I have seen cost < 1 for millions of records where one of the filters in SOQL is Id - along with other filters like !=null and > or < dates. In this query there probably is no selectivity at all. Even filter Plant_Number__c=userdata may be returning much larger dataset than 10% (since custom object) – salesforce-sas Oct 14 '20 at 15:09
  • You're right about not needing the Sequence__c!=Null clause, I removed it which cut down on the time. As another person suggested I created a formula field to get to the Sequence status, which allowed me to query a single object instead of having to do a relationship query. I appreciate the help! – Josh Oct 15 '20 at 17:05
  • Just a heads-up, the formula field technically does not remove the join (unless you have Support create a custom index on it, I think). Formula fields do not store data. – David Reed Oct 15 '20 at 17:06

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.