cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
naccarati
Nintex Newbie

for each loop on a large list to move items to an archive list

Jump to solution

I have a very large list, over 19000 items, and each item has an attachment.  I wrote a site workflow that uses the for each loop to find items that meet a query criteria and then move them to an "archive" list so they don't clutter up the other list.

When I run the workflow for the first time on this list with 19000 items, it is possible that 18000 items will meet the query criteria.

My questions are: 

  1. Will the workflow run for 18000 items or will it crash?
  2. Is there a limitation on the number of items it will process that meet the query criteria?

Thanks.

Labels: (1)
Tags (1)
0 Kudos
Reply
8 Replies
jpmhuls
Nintex Newbie

Re: for each loop on a large list to move items to an archive list

Jump to solution

If i'm not mistaken it will retrieve all 18000 items; I thing the view throttle limit does not apply here. But the workflow may time out (which you might resolve by specifying the "Expected workflow duration" in the workflow settings) and the SharePoint queue fill up. So you definitely need to build in a Pause for action after x processed items (the few occassions I had to process 500+ items I needed to pause every 200 items......).

If real life reveals that there a query is also limitted regarding the number of returned items, then you can set the "Specify item limit" in the Output options section of the Query List action. So the for-each loop needs to be places inside a Loop until the number of items in the Query List result collection = 0.

0 Kudos
Reply
naccarati
Nintex Newbie

Re: for each loop on a large list to move items to an archive list

Jump to solution

Thank you.  I'll let you know what happens. 

0 Kudos
Reply
nj
Nintex Newbie

Re: for each loop on a large list to move items to an archive list

Jump to solution

As someone who has attempted this, I urge you not to do so. Especially not during the day. I tried running mine against a returned number of about 3,000 and I brought the entire server to a hault. It caused the time service to eat up every available bit of memory on the server. Nobody else was able to approve tasks or anything like that.

 

The fun part was the workflow would error so you would think no harm no foul right? For some reason the timer service would never recover from this. The REAL fun part is if you restart the timer service, the same workflow would run again. Restarting the server itself caused the workflow to rerun as well. Until I manually canceled the workflow, everything we did to fix the timer service would cause the workflow to rerun. 

0 Kudos
Reply
naccarati
Nintex Newbie

Re: for each loop on a large list to move items to an archive list

Jump to solution

So, we're taking a different route.  I exported to excel about 16000 of the 19000 records and uploaded them into the "archive" list.  So, it looks like on average the workflow will only have to archive maybe 200 records.  Hopefully that won't crash the timer job or the workflow.  We shall see.

0 Kudos
Reply
Nintex Employee
Nintex Employee

Re: for each loop on a large list to move items to an archive list

Jump to solution

Hi naccarati,

 

200 records is most likely going to cause an issue when the workflow tries to persist to the  database.  When large amounts of data are persisted to SQL its possible for SQL to abort the transaction.  When it comes to workflows this generally occurs in situations very similar to what you're describing.  In order to get around this you need to batch the foreach loop:

 

  1. Add an integer variable "count" with default value 0.
  2. In the For Each loop add a run if statement with a condition of count equals 24 (this batches for every 25 items)
  3. In the run if statement place a set variable action configured to set count = 0 and a 5 minute pause.
  4. After the run if statement, add a math operation action for count plus 1, and store the value back in to count.

 You'll have to play around with the batch size a bit to find out what batch will work based on your environment and workflow.  I generally recommend starting at 25 as a first number and then increasing/decreasing to find a batch size that works consistently. 

View solution in original post

0 Kudos
Reply
naccarati
Nintex Newbie

Re: for each loop on a large list to move items to an archive list

Jump to solution

Okay. So, it will most like loop through a list of 3000 - 3500 items to find the 150 to 200 that match the criteria for the collection.  If I'm understanding you correctly, I need to pause the workflow after every 25 items that get written to the "archive" list.  So, if there are 150 items in the list, then I'll pause it for 5 minutes 6 times before the for each actually ends.  Is this correct?

Reply
naccarati
Nintex Newbie

Re: for each loop on a large list to move items to an archive list

Jump to solution

This worked quite well.  The workflow has moved 700 items in a night out of the list and into an archive list without a glitch.  Thanks!

0 Kudos
Reply
SpenSouv
Nintex Newbie

Re: for each loop on a large list to move items to an archive list

Jump to solution

Hi Naccarati,

 

Can you show us your workflow steps if you dont mind?

0 Kudos
Reply