+ Start a Discussion
h20riderh20rider 

Too many SOQL queries

I have a scheduled job that runs nightly which basically updates any team member that is active and becomes inactive due the the start and end date and visa versa.  When update these records there are a lot of triggers that get set off.  If the list of active --> Inactive or the inactive -->active is too big I will run into soql limits

 

In my code put a limit on the query to only retrieve x amount for inactive or active lists.  but really I need it to run all of them

 

any way of accomplishing this.

 

Best Answer chosen by Admin (Salesforce Developers) 
dmchengdmcheng

It looks like you are not using actual Batch Apex.  That would be the best solution if you need to process large numbers of records.

 

http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_batch_interface.htm

All Answers

SeAlVaSeAlVa

I think that the problem is not about the amount of records you retrieve.

It might be that you perform too many [select ...] statements.

 

Try to bulkify your code (try to perform fewer selects, and treat the result with maps, lists, and so on).

 

Regards.

h20riderh20rider

all the code is bulkified.  The issue is if I have x amount of records going form active to inactive.  If x is too much,  I have x times the  queries needed to verify and update nessaccary records.  What I want to do is limit the amout of records that are update at a time until there are no records.  so if I have 200 records update, I want it to update 4o records at a time unitl there are no more.  

stcforcestcforce

 

do you mean something like (or did i misunderstand?)

 

list<recordtype> listname = new lsit<recrodtyp>();

int x = 0;

for(recordtype r: existingList)

{

    listname.add(r);

    x++;

    if(x==40) {

        update listname;

        listname.clear();

        x=0;

    }

}

 

}

h20riderh20rider

It is a little more complicated.  But close enough

 

in the scenary below.  Does it reset the soql limits for each update,  That is basically what I want to do

stcforcestcforce

It really doesn't sound like you've bulkified your code correctly. Even if you have, it might be an idea to post some code (or altered version if you want to protect your business logic) so people can see what exactly the problem is.

it's generally a rarity to actually need to get around the governor limits if you've coded to best practice. Generally when there is a legitimate problem, people suggest looking at batch apex or future methods. Again, this would probably require a greater udnerstanding of your context than you have provided to date.

SamuelDeRyckeSamuelDeRycke

You could lower the amount of records your scheduled job  processes, and have it start a new schedulled job to process a next batch. 

h20riderh20rider

Sdry

 

That is what I want to do.  but I want it to keep running the batch jobs until there are no more records left.  that is what I am  looking for

 

StcForce

 

It is Bulkified.  I dont have any queries in a loop, but unfortunately there are a lot of rules so I do have a lot of logic. Plus if have 200+ records to update,  then I have hit that limit 

 

below is the batch job.  As you can see the Update will do one batch. but then I have to do a lot in addBfpAccountTeam

 

public void UpdateOpportunities(){
List<Opportunity> activeOpptys = [Select isActiveAgency__c, StartDate__c, Id, EndDate__c, BFP_Account__c, Agency__c From Opportunity where isActiveAgency__c = false and StartDate__c <= Today and (EndDate__c >= Today or EndDate__c = null) limit 40];

for (Opportunity oppty: activeOpptys)
oppty.isActiveAgency__c = true;

update activeOpptys;

AccountTeamHistoryServices.addBfpAccountTeam(activeOpptys);
}

 

 

dmchengdmcheng

It looks like you are not using actual Batch Apex.  That would be the best solution if you need to process large numbers of records.

 

http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_batch_interface.htm

This was selected as the best answer
h20riderh20rider

thanks.  that worked!