+ Start a Discussion

Best Practice Question: Aggregate Query or Iterate through For Loop?

A Little Background:

We have developed some feedback loops that aggregate (textual) categorical information from Opportunity Line Items and push it back to the Opportunity, Account, and Contact objects. These feedback fields and their source fields are only used by 2 out of 5 of the groups in our company that use Opportunities, so we've created a Custom Settings dataset for storing the opt-in status of these different groups for various automated enhancements we've developed.


The Scenario:

From an after-update Trigger on Opportunities, I need an efficient way to check whether any of the record types within the batch qualify for the feedback summary automation. This controls what fields are included in the SOQL query to fetch extended opportunity data and whether the summary ,method is invoked from the trigger at all. Which is a better way to do this. I could

  1. Perfrom and Aggregate Query grouped by RecordTypes and filtered by the Opportunity IDs from the Trigger. This would then provide a much reduced set of values to iterate through, but would count against my query governor limits without truly providing new data. 
  2. Iterate through the entire set of Opportunities to cull the set of distinct record types. This seems like a bit of a waste since the Opportunity records themselves don't contain any other information that could be processed at this time and they will all have to be iterated over again later when they've been returned with their extended details.
  3. Iterate through the set of Opportunities until a single RecordType qualifies for the extended details. This would solve the immediate scenario and provide minor efficiencies over #2, but is less extensible - ie if we later introduce another automated, opt--in, enhancement this would become a bit useless.


What do people think? Personally, I have a strong preference for #1, but I try to make a habit of questioning my preferences...  


    I would vouch for 1 too..


    I was just thinking, can I not use a formula field that would set either 1 or 0 based on the qualifying output and then use this as a summary field?


    Just came up on my mind. I myself have not tried it.  Will try now to check the feasibility.