Unable to escape the governor limits (Too Many SOQL Queries: 201)

Question

My governor limits are getting exceeded because i need to use count() for each individual fieldName and check fieldName != null, and return it's individual value

But there are over 300 fields, how to do it within governor limits? Is there any alternate approach to query each field name individually ?

public class testAccFields {
    public void testAccFields1() {
        String sobjectApiName = 'Account';
        Integer count = 0;
        String runQuery = '';
        String test = '';
        String allFields = '';
        List<String> fieldList = new List<String>();
        Map<String, Schema.SObjectType> schemaMap = Schema.getGlobalDescribe();
        Map<String, Schema.SObjectField> fieldMap = schemaMap.get(SobjectApiName).getDescribe().fields.getMap();
        Map<String,Integer> recordListbyFieldMap = new Map<String,Integer>();
        for(String fieldName : fieldMap.keyset() )
        {    
            if(fieldName == 'Id' || fieldName == 'bank_account_number__c' || fieldName == 'BillingAddress' || fieldName == 'ShippingAddress' || fieldName== 'description' || fieldName == 'BillingStreet')
            {
                System.debug(fieldName);
            }
            else {
            test += +fieldName+ '!= NULL OR ';
            allFields += +fieldName+ ',';
            fieldList.add(fieldName);
            }
        }
            List<Account> s = database.Query('SELECT ' +allFields+ 'Id ' + ' FROM Account');
            System.debug(s);
            
            for(Account acc : s ) {
                for(String fieldName : fieldList){ 
                    if(acc.get(fieldName) != null) {
                        if(recordListbyFieldMap.containskey(fieldName)) {
                        recordListbyFieldMap.put(fieldName,recordListbyFieldMap.get(fieldName) + 1);
                        }
                         else {
                        recordListbyFieldMap.put(fieldName,1); 
                        }
                    }
                }
            }
            system.debug('count accounts where Annual Revenue is not null' + recordListbyFieldMap.get('AnnualRevenue'));
       }
  }

here fieldName is having all the field name (350+) which Iam using individually inside a loop (i know it's a bad practice)

Answer 1

These types of computations are considered heavy computations. You will run into governor limits if you try to them synchronously in a single apex transaction.

If you have any other ETL tool like Mulesoft, Informatica you should ideally do the computations there and dump back the results to Salesforce asynchronously.

Salesforce Functions will be a good candidate for these computations once its GA

Native on Salesforce Platform

If you want to run this natively on Platform, leverage one of the asynchronous ways to break the context.

You can use Salesforce Batch apex or a Queuable to split the transactions into at least 4-5 transactions with 40-50 queries each.

Also, the best query for this will be as below

Integer s = database.countQuery('SELECT Count(' + fieldName + ') FROM Account'); 

Notice that COUNT(fieldName) only returns rows with NON NULL values while COUNT() does need filtering.

If this is something you need instantaneous then explore Apex Triggers than can fire on insert, delete and undelete of records and maintain this information in another custom object. For very first time you may have to manually populate the rows in this object but once trigger is deployed it will take care of it.One thing that might become bottleneck is performance with these 350 fields. A CPU hit may occur unless you invoke a queueable apex from trigger and chain up to atleast 4-5 jobs

Answer 2

what is the use case you are trying to implement? if you have to run database.countQuery on individual fields then Mohith has explained all the best approachs.

But if by any chance you just need all the records where a particular field is not null then i would suggest run only 1 soql query and use apex collections to build your logic.

please find a sample code snippet to store all non null account records based on fieldname.

//hold all fieldapiName

List<String>  fieldNameList = new List<String {'id','Name','Billingcity','ShippingCountry'};

//Map to group account records by fieldname  if fieldname is not null

 Map<String,List<Account>> recordListbyFieldMap = new Map<String,List<Account>>();
 String Query = '';

 List<Account> accList = new List<Account>();

//query

query += 'select ' + String.join(fieldNameList,',') +  ' from Account';

accList=database.query(query);


system.debug(accList);

//populate map  with fieldname key and records if field is not null


for(Account acc : accList ) {
    for(String fieldName : fieldNameList){
        if(acc.get(fieldName) != null) {
            if(recordListbyFieldMap.containskey(fieldName)) {
                recordListbyFieldMap.get(fieldName).add(acc);
            }
            
            else
            {
                recordListbyFieldMap.put(fieldName,new List<Account>{acc}); 
            }
            
            }
    }
}


system.debug('All accounts where country is not null' + recordListbyFieldMap.get('ShippingCountry') );

//code snippet to store count

//hold all fieldapiName

List<String>  fieldNameList = new List<String> {'id','Name','Billingcity','ShippingCountry'};

//Map to group account records by fieldname  if fieldname is not null

 Map<String,Integer> recordListbyFieldMap = new Map<String,Integer>();
 String Query = '';

 List<Account> accList = new List<Account>();

//query

query += 'select ' + String.join(fieldNameList,',') +  ' from Account';

accList=database.query(query);


system.debug(accList);

//populate map  with fieldname key and records if field is not null


for(Account acc : accList ) {
    for(String fieldName : fieldNameList){
        if(acc.get(fieldName) != null) {
            if(recordListbyFieldMap.containskey(fieldName)) {
                recordListbyFieldMap.put(fieldName,recordListbyFieldMap.get(fieldName) + 1);
            }
            
            else
            {
                recordListbyFieldMap.put(fieldName,1); 
            }
            
            }
    }
}

//logic to handle zero non null records for a field

for(String fieldName : fieldNameList){
    if(!recordListbyFieldMap.containskey(fieldName))
        recordListbyFieldMap.put(fieldName,0);
    
}

system.debug('count accounts where country is not null' + recordListbyFieldMap.get('ShippingCountry') );