0

In an Apex Test, I'm getting the 'Too many SQL queries: 100' error. Quite a lot happens in the code that is called (it's an API endpoint, not just a single function) and in one scenario, the end point is getting called twice and exceeding the SOQL Queries governor limit.

Having spent some time tracking down where these queries are made, they all seem reasonable and necessary in their own right - but I did come across one line that stands out as being a bit expensive:

System.debug('Number of Queries processPUTRequest PRE upsert oppLineItem : ' + Limits.getQueries());
upsert oppLineItem;
System.debug('Number of Queries processPUTRequest POST upsert oppLineItem : ' + Limits.getQueries());

oppLineItem is a single OpportunityLIneItem record. The two debug lines show the number of used queries before the upsert is 27 and the number afterwards is 37 - that UPSERT, updating a single OLI, is consuming 10 SOQL Queries!

I've looked for but not been able to find any articles about this. Does any one know why the UPSERT command is so 'expensive'?

2
  • Usually this is extra query and DML activity on other related objects as the result of your update e.g. calculating a derived value that can't be done by the "free" formula or rollup summary mechanisms.
    – Keith C
    Feb 24, 2021 at 16:24
  • It's good to keep in mind that having rollup summary fields means you'll end up executing (potentially multiple) other triggers. DML on an OLI can cause Opportunity triggers to run, which in turn can cause Account triggers to run. If that's what's happening here, changing that upsert to an insert or update should have no effect on the number of queries used there.
    – Derek F
    Feb 24, 2021 at 16:28

1 Answer 1

3

Without seeing your org, it is impossible to say for sure, but almost certainly what is happening here is that you have triggers consuming this governor. Most likely would be triggers on OpportunityLineItem itself, but you might also do cross-object logic which then interacts with other triggers and consumes more queries.

The upsert keyword in and of itself does not consume any queries. You could have an insert consume 99 queries in a complex org. Contrariwise, you could upsert a record in a new object with no logic on it and governor consumption should be near zero.

1
  • 1
    Thanks - I think this is what must be happening. In the Test class that calls this, I found another example - setting up some data for the test, an INSERT of a different object also consumes 10 queries. Not quite sure why I didn't think of this in the first place - It's been a long day!
    – cyberspy
    Feb 24, 2021 at 18:05

You must log in to answer this question.

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