0

I am working with Platform Event to carry out business operations on Objects to avoid hitting governance limit for each transaction.

Problem: for each single Account record update, if I hit 50 SOQL query limit, then anytime 3 records are getting updated, I will constantly hit 101 Too many queries.

Solution-1: create\raise a Platform Event for each 3 account record updates, so each would get a new transaction (is it ?)

Queries

  1. When I raise 3 Platform Events then will they be clubbed (bulkified) and all would be executed under a single transaction ? (so falling into the same problem that I am trying to solve. currently I got to see the 101 too many queries issue under then PE execution logs so assuming it clubs all events and executes them)

  2. If I change Publish behaviour from After Commit --> Publish Immediately, will that execute trigger under 3 separate transactions ?

  3. Is there a way to raise 3 Platform Events that would have their own 3 separate transactions ?

  4. Is PE execution sync or async ? To know the SOQL governance limit currently seeing same limits as that of sync.

3
  • 3
    Before you go trying to go down the Platform Event route, it sounds very much in your interest to investigate why each record is consuming 50 queries (assuming that's not a hypothetical). Your time will probably be better spent making sure that your existing code is bulkified (i.e. that your methods generally take collections as parameters instead of individual records, and that you don't have any queries or DML inside of loops).
    – Derek F
    Sep 14, 2022 at 14:29
  • 1
    If one record is taking 50 SOQLs, then the same number of SOQLs should be consumed even for 3 records, from Trigger. Are you inserting records in LOOPS? Sep 14, 2022 at 18:44
  • @Derek F, Nagendra S : the logic is a needed one that needs frequent "checks" every time Account record is updated that is why apart from retrieve queries we have update queries which causes limits to be hit. No logic is not in the loop. Sep 15, 2022 at 7:32

1 Answer 1

2

When I raise 3 Platform Events then will they be clubbed (bulkified) and all would be executed under a single transaction ? (so falling into the same problem that I am trying to solve. currently I got to see the 101 too many queries issue under then PE execution logs so assuming it clubs all events and executes them)

Yes. In fact, the platform aggregates up to 2,000 events in a single transaction, so it could arguably be even worse than the current situation.

If I change Publish behaviour from After Commit --> Publish Immediately, will that execute trigger under 3 separate transactions ?

It's theoretically possible, but most likely not. Again, the system aggregates PEs that are very close together. You'd have to introduce a "spin loop" (an empty loop that just wastes time) in order to ensure this, and there's no guarantee of what the minimum amount of time would be.

Is PE execution sync or async ? To know the SOQL governance limit currently seeing same limits as that of sync.

It's synchronous, but in its own separate transaction.


What you probably want to do is to use Queueable instead. You can queue up to 50 per synchronous transaction, they each get their own limits, and they can chain an additional call if you want to. This is usually the best way to avoid SOQL/SOSL/CPU limits in synchronous code. You could also use a future method, but I don't prefer them as much anymore, since they can only accept primitive objects and are more restrictive (e.g. a future method can't call another future method).


Alternatively, consider optimizing your code. Your code should be able to handle 1 or 200 records with the same number of SOQL. Do some debugging and find the queries that are being run too many times, and then fix the code. Your current solution of off-loading to an asynchronous process isn't ideal if you can help it.

As a trivial example, I once had a project where creating a single record of a certain type would produce 98 SOQL. I was able to cut it down by half (46) with aggressive optimization, and this was considering that the record in question had triggers that went up to six layers deep. Consider fixing the code properly rather than adding asynchronous complexity.

5
  • you can also (tho not recommended) configure the PE to process 1 record per transaction
    – cropredy
    Sep 14, 2022 at 23:33
  • Thanks sfdcfox for your detailed answer. Yes there are couple of ideas we are thinking of to avoid limits, a challenge would be maintaining data integrity. One more query, any documentation or personal experience "if there is a elapsed time" which will ensure that PE raised between that time would ensure separate transactions and not get clubbed (batch of 200 or 2000) and get executed in a single transaction. example, if I raise a PE with a gap of 1 minute (just an eg) then they won't get clubbed to be executed together. Sep 15, 2022 at 7:37
  • @IHaveAQuestion I wouldn't recommend wasting time pursuing the Platform Event path. I don't think there is a documented window of time to avoid the "clubbing" effect, and it might even be dynamic based on system load and other factors. I have observed that it's probably a window of a few seconds, but it'd be hard to pin down.
    – sfdcfox
    Sep 15, 2022 at 8:09
  • As @cropredy mentioned the PEs can be configured to process 1 rec per transaction documentation here Oct 4, 2023 at 12:07
  • Winter 24 (pilot) also introduces multiple subscribers per Platform Event that in theory can distribute the workload into multiple transactions
    – cropredy
    Oct 4, 2023 at 17:00

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .