+ Start a Discussion
John GerhardJohn Gerhard 

to many soql queries: 101 JG

Hey guys, I am running into an issue with too many SOQL queries when I try a bulk test of my code. The weird part is I run into the issue when I have 25 or more pricebook entries. I'd imagine it is something little.

 
trigger ProductsCreatedonOpp on Opportunity (after insert) {
            
    		//Initiate a list of strings for the SOQL to pull all the products we need
   			List<String> oppsStringList = new List<String>();
    		//Initiate a list that will hold all of the line items we will need to insert
    		List<OpportunityLineitem> lines = new List<OpportunityLineItem>();
    
    		//Fill the oppsStringList with the base quote template we want to filter the SOQL with
    		for (Opportunity opps : Trigger.New){
        		oppsStringList.add(opps.Base_Quote_Tempalte__c);
    		}
    
    		System.debug('1');
    
    		//Build an array with all of the products we want to add to our opportunity
    		List<PriceBookEntry> priceBookList = [SELECT Name,Id,unitPrice FROM PricebookEntry WHERE Quote_Template_Type__c IN:oppsStringList];
    
    		System.debug('2');
    
    		System.debug(priceBookList.size());
    
    		for(PricebookEntry priceBookEntries : priceBookList) {
        		System.debug(priceBookEntries.Id);
    		}
    
     		System.debug('3');
    
    		//Run through each opportunity and then add each product to the lines list to insert
    		for(Opportunity opps: Trigger.new){
    				for (PricebookEntry productsToAdd : priceBookList){
        				lines.add(new OpportunityLineItem(PriceBookEntryID=productsToAdd.Id, OpportunityID=opps.Id,
                	                             		 UnitPrice=productsToAdd.UnitPrice, Quantity=opps.of_Screens_per_Month__c));       	
    				}
       		 	}     
   		 	System.debug(lines);
    		//Insert all of the products into the Opportunities
    		insert lines;
}

 
Best Answer chosen by John Gerhard
Asif Ali MAsif Ali M
Hi John,

Your trigger is consuming only one SOQL. I guess the code running prioir to After Insert is consuming 100 SOQLs the SOQL in the above code is 101st  SOQL resulting in exception.

Try to add this debug line at the start and end of the afterInsert trigger and see how many SOQLs are executed before entering into AfterInsert trigger
system.debug('SQOL Limits: '+ Limits.getQueries() + ' of ' + Limits.getLimitQueries() + '\n');

 

All Answers

Asif Ali MAsif Ali M
Hi John,

Your trigger is consuming only one SOQL. I guess the code running prioir to After Insert is consuming 100 SOQLs the SOQL in the above code is 101st  SOQL resulting in exception.

Try to add this debug line at the start and end of the afterInsert trigger and see how many SOQLs are executed before entering into AfterInsert trigger
system.debug('SQOL Limits: '+ Limits.getQueries() + ' of ' + Limits.getLimitQueries() + '\n');

 
This was selected as the best answer
John GerhardJohn Gerhard
Asif, you sir are correct, sorry I just learned Apex yesterday and this is the first trigger I have actually built for production purposes so far. I actually found another trigger our implementation partner built that had a SOQL inside of a for loop that was running on opportunitylineitem after insert. #NewbieProgrammerProblems this debug statement is awesome! I will start using it very aggressively going forward. Thank you!
Asif Ali MAsif Ali M
awesome. Your first trigger is so clean and bulkified which is actually confusing for many beginners. It's the same when I started.
I have a utility method you can use it to print many of the limits.
public static String getLimits() {

        String sfLimits = '******LIMITS******\n';
        sfLimits += 'SOQL: ' + Limits.getQueries() + ' of ' + Limits.getLimitQueries() + '\n';
        sfLimits += 'SOQL Query Rows: ' + Limits.getQueryRows() + ' of ' + Limits.getLimitQueryRows() + '\n';
        sfLimits += 'Query Locator Rows: ' + Limits.getQueryLocatorRows() + ' of ' + Limits.getLimitQueryLocatorRows() + '\n';
        sfLimits += 'DML Statements: ' + Limits.getDmlStatements() + ' of ' + Limits.getLimitDmlStatements() + '\n';
        sfLimits += 'DML Rows: ' + Limits.getDmlRows() + ' of ' + Limits.getLimitDmlRows() + '\n';
        sfLimits += 'CPU Time: ' + Limits.getCpuTime() + ' of ' + Limits.getLimitCpuTime() + '\n';
        sfLimits += 'HEAP: ' + Limits.getHeapSize() + ' of ' + Limits.getLimitHeapSize() + '\n';
        sfLimits += 'Emails: ' + Limits.getEmailInvocations() + ' of ' + Limits.getLimitEmailInvocations() + '\n';
        sfLimits += 'Future: ' + Limits.getFutureCalls() + ' of ' + Limits.getLimitFutureCalls() + '\n';

        return sfLimits;
    }

I also have one more method which actually check how close I am to limit. You can use this to send notification when the limits are reaching certain % (allowedLimit)  
 
public static Boolean isCloseToLimits(Integer allowedLimit) {
        Double sfVal;
        Double sfLimit;
        Double limitInPercent;

        // DML Rows
        sfVal = Limits.getDmlRows();
        sfLimit = Limits.getLimitDmlRows();
        limitInPercent = sfVal / sfLimit * 100;
        if (limitInPercent >= allowedLimit) return true;

        // DML Statements
        sfVal = Limits.getDmlStatements();
        sfLimit = Limits.getLimitDmlStatements();
        limitInPercent = sfVal / sfLimit * 100;
        if (limitInPercent >= allowedLimit) return true;

        // SOQL limits
        sfVal = Limits.getQueries();
        sfLimit = Limits.getLimitQueries();
        limitInPercent = sfVal / sfLimit * 100;
        if (limitInPercent >= allowedLimit) return true;

        // SOQL Rows
        sfVal = Limits.getQueryRows();
        sfLimit = Limits.getLimitQueryRows();
        limitInPercent = sfVal / sfLimit * 100;
        if (limitInPercent >= allowedLimit) return true;

        // CPU time
        sfVal = Limits.getCpuTime();
        sfLimit = Limits.getLimitCpuTime();
        limitInPercent = sfVal / sfLimit * 100;
        if (limitInPercent >= allowedLimit) return true;

        // HEAP Size
        sfVal = Limits.getHeapSize();
        sfLimit = Limits.getLimitHeapSize();
        limitInPercent = sfVal / sfLimit * 100;
        if (limitInPercent >= allowedLimit) return true;

        return false;
    }

 
John GerhardJohn Gerhard
Thank you! Glad to hear I am doing it correctly. It isn't too bad if you learn how to program in a bulkified method rather than just running and gunning. It also took me about 6 hours to write it lol. Better to write it the right way once and spend more time on it than to run and gun and have to change it all later when you hit issues. Thank you for all of the tips!
John GerhardJohn Gerhard
Blerg, I did find a bug, on my SOQL Quote_Template_Type__c is a multi select picklist and oppsStringList is a picklist. So if more then one item is selected in the multipick then it no longer works. Do you know how to turn it from IN: to an includes?