0

I have exposed apex rest service where I received 1369 records to be inserted into one custom object called PUP. Before inserting into PUP, i have to multiply these rows by 3 ( 1 record from external system map to 1 role and I need to create records for 3 roles so 1*3) so total # of records is now 4107. Then I am inserting into PUP objects as follows

insert PUPs

As per salesforce documentation , trigger accepts only 200 records at a time. So trigger will fire 21 times on this object ( 4107 /200 =20.33).

The trigger written on this object is bulkified. But since trigger is firing 21 times and trigger variables are not reseting after each 200 rows inserting and it is firing in single transaction scope, I am getting "too many SOQL queries :101" error.

Note: Trigger on PUP object has some business logic which fire query on other object. So assuming we have 6 queries on PUP trigger for both insert and update trigger event, the total # of soql queries will be 21 * 6 = 126

  • only if you can share your code snippet – night crawler Nov 10 '17 at 3:35
  • I can not share the code here as it is too big which contain the apex rest service , trigger on custom object PUP, trigger on Contact which is master of PUP. but the code flow is as follows 1. Apex rest service which receives 1309 rows from external service 2. Apex code multiply this rows by 3 ( 1309* 3) as explained in question. 3. apex rest service calls insert PUP records. Trigger written on PUP which has almost 6 soql queries to do some business logic. – mangesh patil Nov 10 '17 at 3:46
  • Is that possible to reduce amount of SOQL queries in trigger? Cache something in static variables? If not -- one of the solutions would be creating and starting of batch job on incoming webservice to process data in chunks and insert in different contexts – kurunve Nov 10 '17 at 4:59
  • 5
    @mangeshpatil Use static variables to store commonly queried data, or consider using a trigger handler framework that has bypass and bulk-action methods. There's ways to avoid needing to go as far as writing batch classes and such if you put a little effort in to it. – sfdcfox Nov 10 '17 at 5:01
0

The part mentioned above about stashing data in a static variable is a great idea. Also if some of those 6 queries include are against a small set of data from other objects representing business logic, you could use Org Cache to handle that. If you're OK with it only being queried once every 24 hours you can implement it via the CacheBuilder interface and save yourself a lot of queries.

I have to say, though, what we're missing is the ability to have a "finalizer" in Apex triggers: Something that runs only after the entire trigger context is over. Someone actually brought this up at Dreamforce this week in a direct talk with the developers of Apex itself.

If cutting down the SOQL query count is impossible, one approach is to pass the data into a Queueable class. You can even intelligently decide, based on the Limits class, whether to execute synchronously or asynchronously. For example,

MyQueueableClass qInstance = new MyQueueableClass(myData);
// run it synchronously if there are enough queries left
if (Limits.getLimitQueries() - Limits.getQueries() > 10) {
    // you can run the Queueable synchronously like this, omitting the `QueueableContext` parameter
    // as long as your code does not use it or has a null check
    qInstance.execute(null);
}
// otherwise enqueue it asynchronously
else {
    Id jobID = System.enqueueJob(qInstance);
}

So then a small data set would process synchronously, and a large data set would toss the excess records into the asynchronous queue.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.