You have created a Process Flow using Process Builder. The Process works fine when processing a record one at a time. But whenever changes are made to a number of records (such as Dataloader or a mass update from another process), your Process fails with “Too many SOQL Queries”.
I love the way Salesforce constantly releases new tools to make everyone’s job easier and more streamlined. The introduction of the Process Builder seemed like ‘manna from heaven’ with the promise of relieving developers from creating tedious triggers that did mundane things simply because workflow could not handle child relationship updates or other seemingly simple tasks. It gave hope to the hopeless administrators who pleaded with developers to finish their ‘one simple trigger’, or complete their silly ‘Visual Flow Plugin’.
But the release, and consequently the update provided in Winter ’16 did not live up to the full expectations of the people. The initial release handled logic on ‘one record at a time’, which seemed great -if everything in your Org happened one….record….at….a….time. “When does that ever happen!” – many administrators screamed from the dungeons of their cubicles, as developers snorted “great, useless” in their murmurings rummaging through Salesforce help articles and seeing this limitation. The following release promised to “Reduce Chances of Hitting SOQL Limits in Processes“, but instead of ‘everything just working’ seamlessly, now the ‘Too many SOQL Queries’ seem to happen randomly. What gives?!
So – in the Winter ’16 release, and the corresponding solution to the idea that was posted around ‘bulkifying’ the process, Salesforce has the following verbiage:
The PM pointed out there is often a misunderstanding of what is causing most use cases to hit limits. The operation may be to create a bunch of records, but the queries associated are actually the main cause of limit errors. In such cases, you are hitting platform limits. At this point, Process Builder & Flow are as optimized and bulkified as possible. For those purely reacting to the details captured in the release notes, we will be updating that content to make it clearer.
Clear as mud, right? Here is what they are trying to state:
For inserts and updates defined within the ‘Actions’, we will bundle as many of these records together as possible (based on the Criteria you defined) to perform these actions… This will reduce the number of DML Statements that we execute within a transaction on your behalf… more on that later…
For the magic ‘decision diamond’…. (which this is where your SOQL Query Limits happen) they are trying to state the following:
In order to determine what records can flow through the process you created, we will need to query these records. We use the Standard Apex Language on the backend, so the queries we use are restricted to those limitations. As we build the queries, we will try to build them is such a way to limit the number of queries we make, but in the end – most likely, each criteria will result in 1 SOQL query per record….
So this means, if you have 4 Criteria in that diamond, more likely than not, for each record, 4 SOQL statements will need to be issued to determine if that record can follow through to the ‘Actions’ to be executed for that record. Now, as it is evaluating each of the records, it will store them in kind of a buffer, or queue, and once all of the records are evaluated, it can determine which records (in bulk), so send to the ‘Actions’ for the true evaluation of that diamond, and which records to send to the ‘Next Criteria Evaluation’ (if one exists).
Continuing on the example from above, this means that if you are dataloading records that are to be evaluated by the Process that has 4 Criteria listed, most likely, in order to avoid the SOQL Query limit (if it is – lets say 100 Queries within a single transaction) set your batch size to 25.
So does this ‘SOQL Query’ statement made above apply to each ‘Criteria Diamond’ in our process? Yes, yes it does. How can we get around it? This is where the ‘Headless/Autolaunched Visual Flow’ comes in – the Visual Flow that you can invoke from Process Builder processes. Salesforce has made advances in this area to allow the headless/autolaunched flows to be executed multiple times under the right circumstances to avoid the SOQL limits exception. See the Salesforce help menu on setting this up. There are also other articles that tackle this as well. Be sure to search the Stackexchange Salesforce Category as well for more complex examples, problems and solutions.
So in closing, Process Builder is an important evolution to the distribution of architecting and implementing business logic from the developer to the administrator on the Salesforce platform. Sure, its not perfect yet, but hopefully the information above can help stop your headaches related to finding seemingly ‘intermittent’ SOQL Query errors in your Salesforce processes.