4

Kind of a vague question but I am writing some "rollup" or summary queries that in totality will collect right around 340 account records between the 4 of them. These are rolling up from (client) Account to (Firm or Advisor) Account based upon a lookup field. Each query will be Grouped By the lookup field.

My question is whether it would be more efficient to query all the records in one general query (just disregard any accounts that don't have the lookup fields filled in), loop through them and Group them using 4 different maps. Or is it just better to run 4 queries where we can Group By the records within the query and have a couple additional filters in the WHERE clause. Would still have to loop over all 4 queries and create an new Account (within Apex) to update the (Firm or Advisor) Account.

4 Queries

SELECT SUM(Total_FYC__c), Referred_By__c FROM Account 
    WHERE (Referred_By__c != null) AND Total_FYC__c != 0

SELECT Count(Id), Referred_By__c FROM Account 
    WHERE Primary_Decision_Maker__c != 'Spouse' 
    AND RecordTypeID = :clientRT AND
    Policy_Count_Including_Spouse__c > 0 
    AND Referred_By__c != null 
    GROUP BY Referred_By__c

SELECT SUM(Total_FYC__c), Firm__c FROM Account 
    WHERE (Firm__c != null) AND Total_FYC__c != 0 GROUP BY Firm__c

SELECT Count(Id), Firm__c FROM Account WHERE
    Primary_Decision_Maker__c != 'Spouse' AND RecordTypeID = :clientRT AND
    Policy_Count_Including_Spouse__c > 0 AND Firm__c != null GROUP BY
    Firm__c

Or 1 Query and loop through, Summing and counting

SELECT Total_FYC__c, Referred_By__c, Firm__c FROM Account 
    WHERE (Referred_By__c != null OR Firm__c != null) AND Total_FYC__c != 0
  • Can you perhaps show sample queries of what you mean? I got confused by your paragraph – Sebastian Kessel Jan 10 at 19:11
  • 4
    Also, have you played with Aggregate queries? I am not sure what exactly you're aggregating but 1 query tends to be better than 4. :) – Sebastian Kessel Jan 10 at 19:12
  • Each of the 4 queries would be an aggregate query, either Sum or Count – Bobbygllh Jan 10 at 19:15
  • 3
    Then why don't you group by the lookup field and then the rest of the fields? – Sebastian Kessel Jan 10 at 19:17
  • 1
    If you can write a query that does the processing you need this will (almost) always be more efficient than using a loop in Apex - Apex loops are quite slow and typically use a lot of CPU. I would, however, remind you that aggregate queries contribute to the query row count against the governor limits not by returned aggregate result but rather by the rows that contribute to these results. You may find you actually need to split the processing into parts, or even drop the idea of aggregate queries for batchables processing. Rather depends on the data volumes. – Phil W Jan 10 at 20:59
5

My answer is that it doesn't matter for this example as the volume of data you are dealing with - 340 records - is small so optimization is way down the priorities. My priority list is ballpark this if there is nothing extreme about the code:

  • Code that works
  • Tests that prove the code works
  • Code that doesn't run into query or CPU or heap governor limits
  • Code that others can understand (the queries you list are simple and understandable; merging those may not be)
  • Code that is easy to change and extend
  • Cost-effective development
  • ...
  • Fast code (one meaning of efficiency)
  • ...

Articles like this Why Premature Optimization Is the Root of All Evil expand on the subject.

But looking for alternatives is definitely worthwhile e.g. the single query, using aggregate queries so that you don't do something complicated when there is a simpler approach available.

| improve this answer | |

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.