+ Start a Discussion
Vinnie BVinnie B 

Getting "Too many DML rows: 10001" but I only have 5,000 rows to insert

I understand that you can't insert more than 10,000 rows at once in a DML statement.  So, I wrote a little routine to do these 5,000 at a time (below).  Note that this is called in the 'finish' function of the batch process so it's gathered all of the relevant records, some 50,000 or so.

for (ID cID : contactIDs) {
      counter ++;
      STB_Constituent__c stbc = new STB_Constituent__c();
      stbc.Contact_ID__c = (String) cID;
      constituents.add(stbc);
      if (counter == 5000) {
       try {
         insert constituents;
          counter = 0;
          constituents.clear();
       } catch (System.DmlException dmlEx) {
         throw dmlEx;
       }

This code seems to be working well, but I'm still getting the error message.  Note that in my logs below it says it's only going to insert 5,000 records but then the next line says that's over 10,000.  Am I missing something as to what constitutes a "DML row".  I'll try again using a batch size of 1,000 and see how that goes.

11:27:54.704 (31704666000)|DML_BEGIN|[94]|Op:Insert|Type:STB_Constituent__c|Rows:5000
11:27:54.704 (31704687000)|EXCEPTION_THROWN|[94]|System.LimitException: Too many DML rows: 10001
11:27:54.704 (31704768000)|HEAP_ALLOCATE|[94]|Bytes:28
11:27:54.706 (31706178000)|FATAL_ERROR|System.LimitException: Too many DML rows: 10001
Best Answer chosen by Vinnie B
Damien Phillippi033905702927186443Damien Phillippi033905702927186443
I'm not sure how you are executing your batch, but it a single batch can execute a DML on millions of records.  Batches fire in a context of 200 records at a time from the records brought back.  My guess is that you need to be executing the batch on those contactIds instead of whatever object you are executing it on.  If I saw more code I could tell better.

All Answers

Damien Phillippi033905702927186443Damien Phillippi033905702927186443
Is there any chance that when you insert your 'consituents' that those also trigger the creation of other objects or children?
Vinnie BVinnie B
No, this object was just created and has no triggers, workflow rules nor validation rules.  I did initially try to add a field on the Contact object instead of doing this but that turned out to generate a huge amount of activity.

BTW, I brought the number down from 5000 to 100 based on another thread I saw.  That doesn't seem to be working either.  :(  I'll keep at it.
Damien Phillippi033905702927186443Damien Phillippi033905702927186443
What does the following give you in your debug logs?

<pre>
System.debug('contactIDs size = ' + contactIDs.size());
</pre>
Damien Phillippi033905702927186443Damien Phillippi033905702927186443
I think I misread your exact text....  I missed the part where you said '10000 rows in a single DML statement'.  That is actually not what the limit is.  It is actually '10000 records in a single execution of code'.  Which means all of the code that is executing in that instance, not just that single line.
Vinnie BVinnie B
I thought that might be the case.  Then how in the heck does one write a piece of code that updates more than 10,000 records!?  I can't believe that I have to call a separate batch file on every single block of 10,000 in order to get this to work properly.  If so, would I do this via a future call or would a batch call be best?  Even with 50,000 records one could easily pop the limit of 5 batch files running at once.
Damien Phillippi033905702927186443Damien Phillippi033905702927186443
I'm not sure how you are executing your batch, but it a single batch can execute a DML on millions of records.  Batches fire in a context of 200 records at a time from the records brought back.  My guess is that you need to be executing the batch on those contactIds instead of whatever object you are executing it on.  If I saw more code I could tell better.
This was selected as the best answer
Vinnie BVinnie B
I think you got me going in the right direction.  I have some other things that I have to work on that are more urgent but I'll look at this later today.

I understand the batching process.  In my code I have a list that is added to on every execute function.  Once I get to the finish function, this list has tens of thousands of records.  I was doing my inserts at this point in the code (iterating through this list in the finish function).  I will try and do the inserts as part of the execute function instead.  That should work as each batch is a separate execution.

However, I also have code that deletes all prior records prior to starting out.  I can't do this as part of the batch process.  I'm wondering if a separate function in another class would work.  As I get to the limit instead of trying to do the update there, I simply pass the batched portion of the list to another function that will do the deletion or insertion for me.  Would that work or would this count as DML rows in my current batch class?
Vinnie BVinnie B
BTW, THANKS!!
Damien Phillippi033905702927186443Damien Phillippi033905702927186443
It will still count as DML rows for the current execution of code.  The only way that would change is if you passed data to a future method and executed it there.

You can either do the deletes in a clever way that allows you to do the deleting of associated old records right before adding the new ones, or you can schedule a batch to fire some time before the current batch and have it do the deletions.
Vinnie BVinnie B
I am shocked that this is so difficult.  Aren't there many instances where people need to update over 10,000 records?  Isn't the batch process supposed to allow us to do massive database actions, including very large DML statements, without running into limits like this?
Damien Phillippi033905702927186443Damien Phillippi033905702927186443
Well, it sort of does in a way but there is actually a good reason it forces us to do it this way.  If we were updating say 100,000 records at once, it would hog a ton of resources and bog down other clients temporarily.  The way batches work forces us to put that heavy loads into sets of 200, and so doesn't require the system to hog as many resources at a single time therefore avoiding a bottleneck.  I understand your point, but a multitenant system like Salesforce must also make additional considerations which enforces more strict rules on clients so that they don't hog all the resources.
Vinnie BVinnie B
I get that.  The question then is what is the technique for deleting or inserting 100,000 records?  I understand that this will have to be done slowly via batch files/future calls.  But if I can't even do this in one instance of a class how is it done?  I can't call batch files or I'll easily pop the limit of 5 batch files running at once.
Damien Phillippi033905702927186443Damien Phillippi033905702927186443
Let's say you are doing your processing over ALL Contact records.  We are going to assume there are 100,000 contacts in your environment.  It will then delete all existing STB_Constituent__c records associated to each of those Contacts, and then creating new STB_Constituent__c in place of them.  (I know I don't have syntax 100% correct but the general idea is here)

<pre>
global class BatchContacts
{
  public Database.queryLocator start()
  {
    return new Database.queryLocator('SELECT Id FROM Contact');//Let's pretend this returns 100,000 records
  }

  public void execute(List<Contact> scope)
  {
    System.debug('scope size = ' + scope.size());//This will come as 200
    delete [SELECT Contact__c FROM STB_Constituent__c WHERE Contact_Id__c IN :scope];

    List<STB_Constituent__c> constits = new List<STB_Constituent__c>();
    for (Contact cont: scope)
    {
      constits.add(new STB_Constituent__c(Contact_Id__c = cont.Id);
    }
    insert constits;
  }

  public void finish()
  {
    //Do your finish stuff in here
  }
}
</pre>
Vinnie BVinnie B
Yes, I think that will work.  I do think I need another batch class which will first do all of the deleting for me.  The queries I will have will be:

  - all constituent records (I need to remove them all)

 - a specific set of contacts that will be considered constituents

THANKS again for all of your help!!  I think I can run with this for a little while anyway.
Damien Phillippi033905702927186443Damien Phillippi033905702927186443
Good luck!
Vinnie BVinnie B
I'll let you know how it goes.  I'm learning new stuff every day.  :)