+ Start a Discussion
Linda 98Linda 98 

Too many DML rows..Please suggest

I have a custom button which when clickd will execute a apex class.The whole logic is like this:
http://wiki.developerforce.com/page/The_Get_Next_Button#Quickstart:_The_Get_Next_Case_and_Get_Next_Lead_Button

I had customized this and using as per my req.This works perfect in sandbox where i just have test data.When moved to production where i have 500k records/leads i get the error TOO MANY DML ROWS:10001

Can any one please tell me what am i missing?
Do i need to bulkify my class still?
Please help ..
Below is my apex code:

global without sharing class RetrieveNextUtils {
    webService static Id retrieveNextLead(String userId){
  
    String RtID=null;   
    String query ='Select Id,Owner.name,status,Time_Zone__c from Lead where Status =\'new\' ';
    query=query+ ' and owner.Name= 'Queue'  FOR UPDATE';  //Locking statment as when two users click the button they are pulling same lead .
    try{
    List<Lead> leads = Database.Query(query); 
   
    for(integer i=0;i<leads.size();i++)
    {
        String tzname = leads[i].time_zone__c;
        String str_LeadCurrTime= DateTime.now().format('HHmm',tzname);
     
        if(str_LeadCurrTime >=  '0900' && str_LeadCurrTime <= '1145' || str_LeadCurrTime >= '1315' && str_LeadCurrTime <= '1700' )
        {   
             leads[i].OwnerId =UserInfo.getUserId();
             RtID=leads[i].Id;
             break;
         } 
   }
   update leads;
   return RtID;
   }
   Catch(exception ex){
        return RtID;
   }

}
}

Ashish_SFDCAshish_SFDC
Hi , 

See few sample threads which had similar issue, 

http://salesforce.stackexchange.com/questions/12514/system-limitexception-too-many-dml-rows-10001

https://developer.salesforce.com/forums/ForumsMain?id=906F000000093m1IAA

https://developer.salesforce.com/forums/ForumsMain?id=906F000000092aAIAQ

https://developer.salesforce.com/forums/ForumsMain?id=906F000000092BbIAI

Regards,
Ashish
justin_sfdcjustin_sfdc
Hi,
Change your code to a batch class and then it should be able to perform the DML operation. Once change to batch class, you can call that class on the click of the button; which will still take a little longer to update all the records, but it will do it; the time depends on the size of the update.

Thanks,
justin~sfdc
willardwillard
First of all - does this line actually compile?
query=query+ ' and owner.Name= 'Queue'  FOR UPDATE';

That said, the reason you are getting that DML limit exception is because you are trying to update too many rows at once.  The easy answer is to just wrap your query into a batch loop (http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_loops_for_SOQL.htm - look under the SOQL for loop formats).

So for you, that would look like this:

// ... some code  

// Grabs leads in batches (I think 200 per loop)
    for(Lead[] leads : [Select Id,Owner.name,status,Time_Zone__c from Lead where Status ='new' and owner.Name= 'Queue'  FOR UPDATE])
    {
        String tzname = leads[i].time_zone__c;
        String str_LeadCurrTime= DateTime.now().format('HHmm',tzname);
    
        if(str_LeadCurrTime >=  '0900' && str_LeadCurrTime <= '1145' || str_LeadCurrTime >= '1315' && str_LeadCurrTime <= '1700' )
        {  
             leads[i].OwnerId =UserInfo.getUserId();
             RtID=leads[i].Id;
             break;
         }
        update leads; // updates only 200 at a time. 
   }

   return RtID;

That said - are you sure you need to update > 10k records in one shot?  It seems excessive and you may be updating more leads than you should.  I'd look into narrowing down the number of leads returned from your query.
willardwillard
I can't seem to edit my post.  Trying to wrap my code into a code block.  Let's see if this works:
<pre>
// ... some code 

// Grabs leads in batches (I think 200 per loop)
    for(Lead[] leads : [Select Id,Owner.name,status,Time_Zone__c from Lead where Status ='new' and owner.Name= 'Queue'  FOR UPDATE])
    {
        String tzname = leads[i].time_zone__c;
        String str_LeadCurrTime= DateTime.now().format('HHmm',tzname);
   
        if(str_LeadCurrTime >=  '0900' && str_LeadCurrTime <= '1145' || str_LeadCurrTime >= '1315' && str_LeadCurrTime <= '1700' )
        { 
             leads[i].OwnerId =UserInfo.getUserId();
             RtID=leads[i].Id;
             break;
         }
        update leads; // updates only 200 at a time.
   }

   return RtID;
</pre>
Linda 98Linda 98
What will happen if 200 leads doesnt satisfy my if condition?
Do i get error saying no leads at this time or it will loop after next 200 leads??
I tried to implement above,i still get Too many DML rows:10001

please correct me if i am wrong.Below is my code:


global without sharing class RetrieveNextUtils {
    webService static Id retrieveNextLead(String userId){

    String RtID=null;
   String leadq =Lead_config__c.getInstance('Leadsettings').Leadqueue__c;
   String query ='Select Id,Owner.name,status,Time_Zone__c from Lead where Status =\'new\' AND owner.Name= '+ leadq + ' FOR UPDATE';
   for(Lead[] leads : database.query(query)){
         for(integer i=0;i<leads.size();i++)
    {
        String str_LeadCurrTime= DateTime.now().format('HHmm',leads[i].time_zone__c);
    
         if(str_LeadCurrTime >=  '0900' && str_LeadCurrTime <= '1145' || str_LeadCurrTime >= '1315' && str_LeadCurrTime <= '1700' )
        {  
             leads[i].OwnerId =UserInfo.getUserId();
             RtID=leads[i].Id;
             break;
         }
   }
   update leads;
   }
   return RtID;
   }
}

Thanks a lot for your help.I need this fix asap!!!
Linda 98Linda 98
I had never worked on batch apex..Could you please guide me on how can i write it.??
I followed this link but it didnt work for me.I still get the error.So i am not sure if i am doing it correct or not.
http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_loops_for_SOQL.htm - look under the SOQL for loop formats

Please help

 
willardwillard
I just realized that you have a break if you match on a lead.   It looks like once you find that lead, you just want to update that one lead, correct?

If so, then all you have to do is run this code (modified from first example):
<pre>
global without sharing class RetrieveNextUtils {
    webService static Id retrieveNextLead(String userId){
 
        String RtID=null;  
        String query ='Select Id,Owner.name,status,Time_Zone__c from Lead where Status =\'new\' ';
        query=query+ ' and owner.Name= 'Queue'  FOR UPDATE';  //Locking statment as when two users click the button they are pulling same lead .
        try{
            List<Lead> leads = Database.Query(query);
      
            for(integer i=0;i<leads.size();i++)
            {
                String tzname = leads[i].time_zone__c;
                String str_LeadCurrTime= DateTime.now().format('HHmm',tzname);
        
                if(str_LeadCurrTime >=  '0900' && str_LeadCurrTime <= '1145' || str_LeadCurrTime >= '1315' && str_LeadCurrTime <= '1700' )
                {  
                    leads[i].OwnerId =UserInfo.getUserId();
                    RtID=leads[i].Id;
                    update leads[i]; // just update this one lead
                    return RtID;  // then return the rtid (you don't need the break)
                }
           }
        }
        Catch(exception ex){
            return RtID;
        }

    }
}
</pre>
Linda 98Linda 98
Thank you for that but Now i get Too many DML statements:151
Also its taking me long time..Please help
Ashish_SFDCAshish_SFDC
Hi, 

There is a Update Statement in th For Loop. 

Remove the statement out of the Loop and that should solve the issue. 

Regards,
Ashish