1

I need to retrieve the field value given subject. But the only way I find to do it is by doing a dynamic query in a For or loop. Did anyone have a solution to avoid the query in the loop? Or any other workaround

Map<String, List<String>> AllAPINames = new Map<String, List<String>>();
list<String> fname = new list<String>();
fname.add('Name');
fname.add('StageName'); 
AllAPINames.Put('Opportunity',fname);

list<String> fname1 = new list<String>();
fname1.add('Id');
AllAPINames.Put('Account',fname1);
system.debug('AllAPINames: '+AllAPINames);

for(string objkey: AllAPINames.keySet()){
    List<string> fieldlist = new List<string>();
    fieldlist = AllAPINames.get(objkey);
    string soqlQueryCurrent = string.join(fieldlist,',');
    if(soqlQueryCurrent.endsWithIgnoreCase(',')){
        soqlQueryCurrent = soqlQueryCurrent.removeEndIgnoreCase(',');
    }
    string queryStr = 'SELECT '+soqlQueryCurrent+' FROM '+objkey +' LIMIT 2';
    List<sObject> sObjRecords = Database.query(string.escapeSingleQuotes(queryStr)); // Need to avoid this code inside the Loop
    system.debug('sObjRecords: '+sObjRecords);
}
1
  • Proper indentation, and adding few strategic line breaks, makes reading code so much easier. Please consider this for your future questions. – Derek F Oct 12 '20 at 11:02
1

This is one case where I'd argue that having your query inside of a loop isn't bad.

Since you're querying separate objects, there's no way to combine these into a single query (at least not with the context that you've given us. It could be possible to do this if the Opportunities and Accounts you're querying are related to one another).

The loop isn't bad here because it is very strictly controlled. Instead of looping over something like trigger.new, you're looping over AllAPINames.keySet(). There's going to be a limited number of keys, and given your current code, they need to be explicitly populated. If this loop is going to cause problems, then you'll know about it because the section of code preceding the loop is going to be quite long.

1

The purpose of the max queries governor limit is to mitigate a very specific potential performance problem, sometimes referred to as "death by a thousand cuts". This is a performance problem characterised by too many requests made against a backend system as can happen when something like a query (or some kind of DML) is put inside a loop.

Certain implementations may require putting a backend request inside a loop. But if you do too many iterations, it will break in any system. A typical system, without any monitoring of this kind of behavior, could break in a very messy way, discovered at runtime, and in a way that causes outages and maybe even ties up the database for many users. In Apex, we have created a synthetic breakage limit, so that it breaks for you, and doesn't break for any of the other users (tenants) of your instance.

So the real question is, if this is otherwise the best way to accomplish what you want, how do you prevent this loop from hitting the maximum queries governor limit?

But that brings up questions.

  • Are these the only sobjects you'll be working with?
  • If so, why are you looping?
  • If not, where are these SObject and field names coming from?
  • Is there any upper limit to how many you'll have?

If you know it will always be a small number, the no problem.

If there are a potentially large number of objects you're querying with in this pattern, you'll want to make some use of the Limits.getQueries() and Limits.getLimitQueries() methods.

Once you've populated your AllApiNames map, at that point in time, you could see if the current queries used for that transaction, added to the size of AllApiNames would breach the limit. Something like:

if ( AllApiNames.size() + Limits.getQueries() > Limits.getLimitQueries() ){
    //...time to abort gracefully
}

You could even add some logic that if it is within a given tolerance, it starts to leave warnings in the debug log, maybe even emails an administrator, posts to an admin chatter group, etc.

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.