2

Setup:

Say I have five Contact sObjects that I create and apex. When you insert a contact one at a time, you have x soql queries by way of triggers and stuff it uses.

Questions:

  1. So if you insert each contact one at a time in your code you get 5x soql queries (right?).

  2. Now say you put those 5 guys into an array and insert the array using one insert. How many soql queries are there?

  3. In regards to 2, if It’s 5x, why do we bother? So we can decrease the number of DML's by 4?

Thanks,

The semi-new guy

PartOfTheOhana
  • 667
  • 2
  • 16
  • 40

2 Answers2

6

A given block of code and all of the synchronous apex code that executes because of it is evaluated in a single execution context, which means that it has a single set of governor limits. In the example that you give, suppose that your Contact trigger makes 3 SOQL queries and one DML statement. If you inserted each contact separately, then you would use 5 DML statements (one for each Contact insert) + 1 DML statement for each trigger, and 3 * 5 SOQL statements for each trigger, leading to a total of 15 SOQL statements and 10 DML statements for your code. However, if you inserted them as a list, then all five records would be sent as a list to your trigger (1 DML statement) which would then execute its 3 SOQL queries and 1 DML statement once, for a total of 3 SOQL queries and 2 DML statements.

The key here is to design your triggers (and most of your other code) so that the number of queries and statements is not dependent on the number of records you are dealing with. Salesforce provides a brief guide for that here: http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_triggers_bulk_idioms.htm

John Sullivan
  • 1,301
  • 1
  • 10
  • 20
  • Thanks TM. I get it, but there are a couple of points I don't quite follow. Could you clarify them for me? #1, I don't get the count of the # of DML's in either of the cases. In the non-list case, you'd have 1 DML for the insert. Are you saying that when the trigger fires, a second one is executed as well? I apologize, but I've only written 1 trigger thus far. #2 is can you elaborate on: A given block of code and all of the synchronous apex code that executes because of it is evaluated in a single execution context, which means that it has a single set of governor limits. Thx – PartOfTheOhana May 31 '12 at 17:25
  • 1) For the purpose of this question, I imagined a Contact trigger that fires one DML operation (in addition to it's queries), so that is where that came from. This is not automatic with the trigger, but part of my example. – John Sullivan May 31 '12 at 18:09
  • 2) The governor limits in salesforce are calculated against all of the code that runs in a single piece. So your piece of code that makes 5 inserts, and each of the 5 trigger calls that it run have (for example) a total of 100 SOQL queries and 150 DML statements _in total_, not 100 SOQL queries and 150 DML statements _each_. – John Sullivan May 31 '12 at 18:17
  • I'm getting the hang of it. Thanks Triangle Man! – PartOfTheOhana May 31 '12 at 18:26
1

If you're going to insert the Contacts one at a time, then yes, you would most likely end up with five times as many SOQL queries. But it wouldn't be advised to insert these in this fashion. You're on the right track when you mention that you'd want to insert a collection of records with a single insert call.

If your trigger is written correctly, you should end up with the same number of SOQL queries whether you insert a single record or 20 records. If the number of queries increases with the number of records you insert, then the trigger most likely has a query inside a loop of some sort...this is a great way to run head-first into the governor limits. Keep the Apex Code Best Practices in mind when developing your triggers (see the link for code samples):

  • Keep SOQL queries outside of loops wherever possible
  • Ensure that helper methods called from your triggers are able to operate on collections efficiently
  • Use Apex collections liberally to make more efficient use of the queries you can't avoid
JCD
  • 2,211
  • 1
  • 19
  • 29