Multiple Models of Same Object Creates SOQL Query Limit Exceptions

  • 2
  • Problem
  • Updated 3 years ago
I have a Parent object with its own model and a child object that has 20 models. The child object models all have different filters so they have different default values. However, I have a few triggers running on this child object.

The issue is that when my save button attempts to save all the models on my skuid page, it attempts to save each model one at a time. This is a problem because there are queries in my child object trigger that get called excessively. Ideally, this should be saving all models of the same object type at the same time.

Am I reading this situation correctly, is this how skuid actually saves models? Any help with this would be appreciated. Thanks!
Photo of Eulogio Gallo

Eulogio Gallo

  • 1,874 Points 1k badge 2x thumb
  • perplexed

Posted 3 years ago

  • 2
Photo of Irvin Waldman

Irvin Waldman, Champion

  • 9,006 Points 5k badge 2x thumb
Are you saving the models in one action step or multiple action steps?
Photo of Eulogio Gallo

Eulogio Gallo

  • 1,874 Points 1k badge 2x thumb
I have a button on the page that is saving all the models in one step (I presume). I'm attaching a screenshot, I hope it's informative.

Thanks!

Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
Actually you contention about the way muliple model save actions occur is not correct.  When you have multiple models tied to a singe save action - that is managed as one server call, and is one Salesforce transaction.  

Here is what we think is happening.   If the triggers on your child objects are in turn doing SOQL queries,  when all 20 records are processed together in a single salesforce transaction, the SOQL queries are being treated independently and you are running into the Governor Limits on what you can do within a single transaction.  We think you never ran into this on standard layouts because you only ever saved one record at a time.  Skuid gave you the ability to save 20 records in one transaction. 

We think you need to explore bulkifying the triggers you have written so that they don't trip the governor limits. 



Another strategy would be to change that button into an "actions framework" button that called 20 different "save models" actions.  Each of these would be treated as a separate server call (and Salesforce transaction).  I think this would be worse user experience,  but it might get around your Trigger issues. 
Photo of Eulogio Gallo

Eulogio Gallo

  • 1,874 Points 1k badge 2x thumb
The trigger methods that are being called are bulkified, so I don't think that's the issue. I looked at the debug log for the skuid page I built and saw that every child model being saved was getting its own DML save call, and for each of those the parent model was getting a DML update call. I tested my trigger methods and see that a list of child object input only has a single DML call for all the records.

Does that make sense? Maybe it's the way that I've added the models to my page that is causing them all to save as a separate DML action, I'm not sure.
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
Though you are seeing multiple DML entries - we still contend that it is one single transaction,  and governor limits are being applied in the one single transaction. 

Could you paste the specific error message you are getting?  Maybe we are going down the wrong path here. 
Photo of Eulogio Gallo

Eulogio Gallo

  • 1,874 Points 1k badge 2x thumb
Ok sure, I'm going to include first the log of me inserting these child objects through a console window. I'm bolding what I think are the most relevant parts:


33.0 APEX_CODE,DEBUG;APEX_PROFILING,INFO;CALLOUT,INFO;DB,INFO;SYSTEM,DEBUG;VALIDATION,INFO;VISUALFORCE,INFO;WORKFLOW,INFO

......

08:51:27.038 (38772538)|EXECUTION_STARTED

08:51:27.038 (38785173)|CODE_UNIT_STARTED|[EXTERNAL]|execute_anonymous_apex

........

08:51:27.046 (46628928)|DML_BEGIN|[26]|Op:Insert|Type:TBA_Selection_Score__c|Rows:21

08:51:29.823 (2823948575)|CODE_UNIT_STARTED|[EXTERNAL]|01q800000009ZiZ|trigger_TBA_Selection_Score on TBA_Selection_Score trigger event AfterInsert for [a2C800000004tUW, a2C800000004tUX, a2C800000004tUY, a2C800000004tUZ, a2C800000004tUa, a2C800000004tUb, a2C800000004tUc, a2C800000004tUd, a2C800000004tUe, a2C800000004tUf, a2C800000004tUg, a2C800000004tUh, a2C800000004tUi, a2C800000004tUj, a2C800000004tUk, a2C800000004tUl, a2C800000004tUm, a2C800000004tUn, a2C800000004tUo, a2C800000004tUp, a2C800000004tUq]

08:51:29.849 (2849007231)|METHOD_ENTRY|[1]|01p80000000QCqP|TBA_Selection_Update_Tools.TBA_Selection_Update_Tools()

08:51:29.849 (2849028900)|METHOD_EXIT|[1]|TBA_Selection_Update_Tools

.......

08:51:30.318 (2881981650)|CUMULATIVE_LIMIT_USAGE

08:51:30.318|LIMIT_USAGE_FOR_NS|(default)|

Number of SOQL queries: 2 out of 100

Number of query rows: 1 out of 50000

Number of SOSL queries: 0 out of 20

Number of DML statements: 1 out of 150

Number of DML rows: 21 out of 10000

Maximum CPU time: 19 out of 10000

Maximum heap size: 0 out of 6000000

Number of callouts: 0 out of 100

Number of Email Invocations: 0 out of 10

Number of future calls: 0 out of 50

Number of queueable jobs added to the queue: 0 out of 50

Number of Mobile Apex push calls: 0 out of 10


08:51:30.318|CUMULATIVE_LIMIT_USAGE_END


08:51:29.882 (2882091550)|CODE_UNIT_FINISHED|trigger_TBA_Selection_Score on TBA_Selection_Score trigger event AfterInsert for [a2C800000004tUW, a2C800000004tUX, a2C800000004tUY, a2C800000004tUZ, a2C800000004tUa, a2C800000004tUb, a2C800000004tUc, a2C800000004tUd, a2C800000004tUe, a2C800000004tUf, a2C800000004tUg, a2C800000004tUh, a2C800000004tUi, a2C800000004tUj, a2C800000004tUk, a2C800000004tUl, a2C800000004tUm, a2C800000004tUn, a2C800000004tUo, a2C800000004tUp, a2C800000004tUq]

08:51:29.901 (2901104734)|DML_END|[26]

08:51:30.347 (2910867731)|CUMULATIVE_LIMIT_USAGE

08:51:30.347|LIMIT_USAGE_FOR_NS|(default)|

Number of SOQL queries: 2 out of 100

Number of query rows: 1 out of 50000

Number of SOSL queries: 0 out of 20

Number of DML statements: 1 out of 150

Number of DML rows: 21 out of 10000

Maximum CPU time: 19 out of 10000

Maximum heap size: 0 out of 6000000

Number of callouts: 0 out of 100

Number of Email Invocations: 0 out of 10

Number of future calls: 0 out of 50

Number of queueable jobs added to the queue: 0 out of 50

Number of Mobile Apex push calls: 0 out of 10


08:51:30.347|CUMULATIVE_LIMIT_USAGE_END


08:51:29.910 (2910933141)|CODE_UNIT_FINISHED|execute_anonymous_apex

08:51:29.912 (2912464799)|EXECUTION_FINISHED
(Edited)
Photo of Eulogio Gallo

Eulogio Gallo

  • 1,874 Points 1k badge 2x thumb
Ok and now here is some of the log for my skuid page. Notice how each child object is getting it's own DML call:

31.0 APEX_CODE,DEBUG;APEX_PROFILING,INFO;CALLOUT,INFO;DB,INFO;SYSTEM,DEBUG;VALIDATION,INFO;VISUALFORCE,INFO;WORKFLOW,INFO
09:00:47.122 (122623978)|SAVEPOINT_SET|[5716]|SavepointValue0
09:00:47.175 (175318526)|DML_BEGIN|[6033]|Op:Insert|Type:SObject|Rows:1
09:00:47.237 (237460059)|CODE_UNIT_STARTED|[EXTERNAL]|01q800000009ZiZ|trigger_TBA_Selection_Score on TBA_Selection_Score trigger event AfterInsert for [a2C800000004tUr]
......
09:00:47.338 (338377258)|SOQL_EXECUTE_END|[7]|Rows:1
09:00:47.344 (344447385)|SOQL_EXECUTE_BEGIN|[237]|Aggregations:0|SELECT Score_SubType__c, Score_Type__c, Activity_Name__c, Value__c FROM TLP_Score__c WHERE TBA_Selection__c = :tmpVar1
09:00:47.352 (352452422)|SOQL_EXECUTE_END|[237]|Rows:0
09:00:47.352 (352688527)|SYSTEM_METHOD_ENTRY|[241]|List.iterator()
09:00:47.352 (352849085)|SYSTEM_METHOD_EXIT|[241]|List.iterator()
09:00:47.352 (352881776)|SYSTEM_METHOD_ENTRY|[241]|system.ListIterator.hasNext()
09:00:47.352 (352905233)|SYSTEM_METHOD_EXIT|[241]|system.ListIterator.hasNext()
09:00:47.353 (353256285)|SOQL_EXECUTE_BEGIN|[734]|Aggregations:0|SELECT Score_SubType__c, Score_Type__c, Value__c FROM TBA_Selection_Score__c WHERE TBA_Selection__c = :tmpVar1
09:00:47.359 (359442607)|SOQL_EXECUTE_END|[734]|Rows:1
09:00:47.359 (359507100)|SYSTEM_METHOD_ENTRY|[738]|List.iterator()
09:00:47.359 (359539074)|SYSTEM_METHOD_EXIT|[738]|List.iterator()
09:00:47.359 (359552844)|SYSTEM_METHOD_ENTRY|[738]|system.ListIterator.hasNext()
09:00:47.359 (359566871)|SYSTEM_METHOD_EXIT|[738]|system.ListIterator.hasNext()
09:00:47.359 (359796165)|SYSTEM_METHOD_ENTRY|[738]|system.ListIterator.hasNext()
09:00:47.359 (359809354)|SYSTEM_METHOD_EXIT|[738]|system.ListIterator.hasNext()
09:00:47.359 (359924913)|DML_BEGIN|[1015]|Op:Update|Type:TBA_Selection__c|Rows:1
........
[1069]|01p80000000QCqP|TBA_Selection_Update_Tools.Update_Application_Values(TBA_Selection_Score__c)
09:00:47.419 (419024098)|METHOD_EXIT|[11]|01p80000000QCqP|TBA_Selection_Update_Tools.Update_Selection_Values(List)
09:00:47.932 (419054622)|CUMULATIVE_LIMIT_USAGE
09:00:47.932|LIMIT_USAGE_FOR_NS|(default)|
Number of SOQL queries: 5 out of 100
Number of query rows: 3 out of 50000
Number of SOSL queries: 0 out of 20
Number of DML statements: 1 out of 150
Number of DML rows: 1 out of 10000
Maximum CPU time: 112 out of 10000
Maximum heap size: 0 out of 6000000
Number of callouts: 0 out of 100
Number of Email Invocations: 0 out of 10
Number of future calls: 0 out of 50
Number of queueable jobs added to the queue: 0 out of 50
Number of Mobile Apex push calls: 0 out of 10

.......

09:00:47.932|CUMULATIVE_LIMIT_USAGE_END

09:00:47.419 (419204001)|CODE_UNIT_FINISHED|trigger_TBA_Selection_Score on TBA_Selection_Score trigger event AfterInsert for [a2C800000004tUr]
09:00:47.446 (446233617)|DML_END|[6033]
09:00:47.458 (458634270)|SOQL_EXECUTE_BEGIN|[6270]|Aggregations:0|SELECT Comments__c,Recommendation__c,Reviewer_Override__r.Name,Score_SubType__c,Score_Type__c,Value__c,TBA_Selection__c,TBA_Selection__r.Name FROM TBA_Selection_Score__c WHERE Id IN :refreshIds LIMIT 1
09:00:47.477 (477977698)|SOQL_EXECUTE_END|[6270]|Rows:1
09:00:47.484 (484923579)|DML_BEGIN|[6033]|Op:Insert|Type:SObject|Rows:1
09:00:47.507 (507461647)|CODE_UNIT_STARTED|[EXTERNAL]|01q800000009ZiZ|trigger_TBA_Selection_Score on TBA_Selection_Score trigger event AfterInsert for [a2C800000004tUs]
.....
09:00:47.533 (533738597)|DML_BEGIN|[1015]|Op:Update|Type:TBA_Selection__c|Rows:1
09:00:47.551 (551835852)|DML_END|[1015]
09:00:47.551 (551879791)|METHOD_EXIT|
.......
Continues like this, inserting single child rows and also updating the parent record, even though there weren't any changes made to it.
.......
09:00:49.898 (2898976940)|EXCEPTION_THROWN|[7]|System.LimitException: Too many SOQL queries: 101
09:00:49.899 (2899137905)|METHOD_EXIT|[1067]|01p80000000QCqP|TBA_Selection_Update_Tools.UpdateAll_Selection_Values(Id)
09:00:49.899 (2899155115)|METHOD_EXIT|[11]|01p80000000QCqP|TBA_Selection_Update_Tools.Update_Selection_Values(List)
09:00:49.899 (2899244447)|FATAL_ERROR|System.LimitException: Too many SOQL queries: 101

Class.TBA_Selection_Update_Tools.UpdateAll_Selection_Values: line 7, column 1
Class.TBA_Selection_Update_Tools.Update_Selection_Values: line 1067, column 1
Trigger.trigger_TBA_Selection_Score: line 11, column 1
09:00:49.899 (2899259743)|FATAL_ERROR|System.LimitException: Too many SOQL queries: 101

Class.TBA_Selection_Update_Tools.UpdateAll_Selection_Values: line 7, column 1
Class.TBA_Selection_Update_Tools.Update_Selection_Values: line 1067, column 1
Trigger.trigger_TBA_Selection_Score: line 11, column 1
09:00:50.412 (2899354728)|CUMULATIVE_LIMIT_USAGE
09:00:50.412|LIMIT_USAGE_FOR_NS|(default)|
Number of SOQL queries: 101 out of 100 ******* CLOSE TO LIMIT
Number of query rows: 250 out of 50000
Number of SOSL queries: 0 out of 20
Number of DML statements: 20 out of 150
Number of DML rows: 20 out of 10000
Maximum CPU time: 1288 out of 10000
Maximum heap size: 0 out of 6000000
Number of callouts: 0 out of 100
Number of Email Invocations: 0 out of 10
Number of future calls: 0 out of 50
Number of queueable jobs added to the queue: 0 out of 50
Number of Mobile Apex push calls: 0 out of 10

09:00:50.412|LIMIT_USAGE_FOR_NS|skuid|
Number of SOQL queries: 20 out of 100
Number of query rows: 20 out of 50000
Number of SOSL queries: 0 out of 20
Number of DML statements: 22 out of 150
Number of DML rows: 22 out of 10000
Maximum CPU time: 0 out of 10000
Maximum heap size: 0 out of 6000000
Number of callouts: 0 out of 100
Number of Email Invocations: 0 out of 10
Number of future calls: 0 out of 50
Number of queueable jobs added to the queue: 0 out of 50
Number of Mobile Apex push calls: 0 out of 10

09:00:50.412|CUMULATIVE_LIMIT_USAGE_END

09:00:49.899 (2899469564)|CODE_UNIT_FINISHED|trigger_TBA_Selection_Score on TBA_Selection_Score trigger event AfterInsert for [a2C800000004tVB]
09:00:49.902 (2902807525)|DML_END|[6033]
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
Eulogio.  Hope you had a good weekend.  As you may be able to tell,  Skuid was out enjoying the day yesterday.  Hope you were able to as well. 

We do however want to get to the bottom of your Multi-Model trigger issue.  Would you mind giving us login rights to your org so we can take a look at what is going on?   Here is how: 
1. Use this tutorial to give us login rights: http://help.skuidify.com/m/getting-started/l/182412-getting-help-how-to-grant-skuid-login-rights-to-your-org

2. Then send an email to support@skuidify.com  with the following information: 

- Your org Id 

- The name of the page where the problem is happening 

- The steps required to reproduce the problem.  

We’ll see what’s going on...  
Photo of Eulogio Gallo

Eulogio Gallo

  • 1,874 Points 1k badge 2x thumb
Hi Rob!

I did have a great weekend, glad to hear you did as well. I followed these steps and sent an email, looking forward to getting to the bottom of this. Thanks again for all your help!
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
We've not completely dissected your trigger code (nor do we think we could),  but we would reccomend that you rethink the construction of your skuid page so all 21 questions are gathered into 1 model,  rather than as 21 models on one object.  In this way the save action will create 1 database update,  rather than 21. 

You could make the questions be rows in a table and conditionally render the tables based on score subtype. 

Or if you want to keep the field editor design you have today - you could conditionally render each field editor based on score subtype. 

We think this will be a much more performant design. 
Photo of Eulogio Gallo

Eulogio Gallo

  • 1,874 Points 1k badge 2x thumb
The reason I had so many different models is because that was the only way I could find to generate objects with predefined field values. Is there a better way to have preset values in my objects?
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
Aah yes.  That makes sense. 

Then I think you two options available. 

1. Create a separate "save stuff" model.  This model would have the smae fields as the other ones, but not load data on page load.  Then change the save button on the page to write a row to this new model for each one of the othe rmodels.   You could either set up action fwk steps to copy the data out of each other model (its going to be about 50 steps...) , or write a javascript snippet to loop through all the other models on the page and copy thier contents into this new model.   Then once the temporary model is populated you save it as the final action in your process. 

2.  If you want to do that processing on page load rather than page save,  you can do the model population with code at the beginning.  Have the model load data, and then create a snippet that spins through the list.  If a particular score subtype row is not present,  create one and populate the appropriate values.

Neither of these options is straightforward, but you have impressed us with your code.  We think you are up to it!   
Photo of Eulogio Gallo

Eulogio Gallo

  • 1,874 Points 1k badge 2x thumb
Ok thanks Rob, I'll try these methods out and let you know my results. (Hopefully soon!)
Photo of Eulogio Gallo

Eulogio Gallo

  • 1,874 Points 1k badge 2x thumb
Ok I've got it working for the most part (thanks for the suggestion!), but now I'm having trouble if I only want to update the rows, not create them. I'm trying to follow the skuid.model.Model documentation, but I'm having some trouble. My code is below, I think the issue I'm having is that I'm trying to update a row that doesn't exist in the model yet, but I'm not sure how to transfer a row from one model to another. Any advice would be appreciated.


---------------------------------------------------------------------------------------

var params = arguments[0];
var $ = skuid.$;
var saveModel = skuid.model.getModel('SaveStuff');

// Prevent extra row from being saved
var removeFirstRow = saveModel.getFirstRow();
saveModel.abandonRow(removeFirstRow);

var models = skuid.model.list();

// Cycle through existing models
for(var i = 0; i < models.length; i++) {
// Skip Savestuff
if(models[i].id !== "SaveStuff" && models[i].id !== "TBA_Selection") {

// Save if row doesn't exist yet
if(models[i].data[0].Id.length < 18) {
var temp = saveModel.createRow({
additionalConditions: [
{field: 'Score_SubType__c', value: models[i].data[0].Score_SubType__c},
{field: 'Comments__c', value: models[i].data[0].Comments__c},
{field: 'Value__c', value: models[i].data[0].Value__c},
{field: 'Recommendation__c', value: models[i].data[0].Recommendation__c},
{field: 'Id', value: models[i].data[0].Id}
], doAppend: true
});

// Update existing rows otherwise
} else {
console.log(models[i].data[0]);
var temp = saveModel.updateRow(models[i], {
Score_SubType__c: models[i].data[0].Score_SubType__c,
Comments__c: models[i].data[0].Comments__c,
Value__c: models[i].data[0].Value__c,
Recommendation__c: models[i].data[0].Recommendation__c,
Id: models[i].data[0].Id,
Id15: models[i].data[0].Id15
});

console.log(temp);
}
}
}

// Save changes
saveModel.save({callback: function(result) {
if(result.totalsuccess) {
console.log("Success!");
window.location = '/' + models[0].data[0].TBA_Selection__c;
} else {
console.log(result.insertResults);
console.log(result.updateResults);
console.log(result.deleteResults);
}
}});
(Edited)