cancel
Showing results for 
Search instead for 
Did you mean: 
Workflow Hero

Run Site Workflow on large amount of items

Jump to solution

Hi Guys,

I need to develope a workflow for a customer and have the following use case:

  • I have to create a site workflow which is supposed to run once per month (no problem so far)
  • The workflow has to analyze a huge amount if list items (up to ~36000, this is where it get's tricky)
  • The workflow should loop throguh the list and fetch some column values (easy)
  • The column values are concatenated to create a some reports (easy)

So far so good. Problem is, I can't get my workflow to run on this amount of items. After some time the workflow always runs into an error ("[Workflowname] failed to start"). Additionally I always get a correlation ID, but funny thing is there is no ULS log entry with the specified correlation ID (probably micrososft thinks that would be to easy for me). I ran the different workflows at least 5 times and always receive the same useless error.

What I've tried so far:

     1. Create 10 lists with 3000 items each and let the site workflow run over all of the lists

     Result: The workflow runs over the first three lists and executes fine. After the third list, the above mentioned error occurs and the workflow stops (he remains      "In Progress" but shows the error in the history and doesn't continue). So the workflow is running over 3 lists with 3000 items each (=9000 items) and stops         afterwards.

     2. Create a Site Workflow to Start another Site Workflow (5 times) that each only runs over one list

     Result: Quite the same. The Main Workflow starts the other workflow properly for the first three or four times. But then i receive the same error again.

So I just cannot seem to get my Site Workflow to run over more than approx. 10000 items. Of course I could create one Site workflow per list, but in reality I will have up to 300 lists (one for each of the customers branch) and creating/maintaining 300 site workflows doesn't seem like a best practice to say the least.

I took a look at the SharePoint Workflow Tresholds on Software boundaries and limits for SharePoint 2013 but can't find anything like "max items queried in one workflow instance" (am I missing/misunderstanding something?). I know there is a "maximum loop threshold" you can set in the global nintex settings, but the explanation doesn't seem like this is the reason for the problem. Besides, even after deactivating this option completely I had the same error again. Safe looping is disabled as well.

This is what the workflow that actually queries the list items look like:

Capture.JPG

I'm using Query-List actions to get all item-IDs and another query-list action inside a for each action to receive the column values of each single item. I know this workflow doesn't look very useful, but this is only a dev version I'm testing on my local VM. Although the queried values are more meaningful on the customers environment, the basic workflow structure is the same.

Did any of you have this or a similar issue before? Is there anything I can do to let my site workflow run over this amount of items (>30k)? If anyone could also explain me why this is actually happening, I would be very grateful.

Any help/idea/comment is highly appreciated! If you need further information, please don't hesitate to ask!

Thanks in advance

Philipp

update: I added a "Commit pending changes" action in the loop without any effect.

0 Kudos
Reply
9 Replies
Workflow Hero

Re: Run Site Workflow on large amount of items

Jump to solution

Allright, seems like I found a solution/workaround. Between calling each workflow that queries multiple k items, i put an "pause for..." action and make my workflow stop for 5 minutes. I'm running another test at the moment but it's looking good.

This seems to do the trick, although I don't exactly understand the issue. I guess it's some database problem maybe caused by to many operations on the workflowprogress table. Maybe someone else can shed some light on this.

Cheers

Philipp

Reply
Workflow Hero

Re: Run Site Workflow on large amount of items

Jump to solution

I am having a similar, but slightly different problem with a large WF. In my case, I put in a pause and it gets to a counter variable and then pauses and it seems like the WF starts from the beginning again. I think it may have something to do with Db size and WF timers / dehydration.

0 Kudos
Reply
Workflow Hero

Re: Run Site Workflow on large amount of items

Jump to solution

Hey ‌,

any progress on your issue yet?  I've never had this problem before but I'd love to know the reason for that and how we can try to fix it.

Cheers

Philipp

Reply
Workflow Hero

Re: Run Site Workflow on large amount of items

Jump to solution

Hello Phillip. Sorry for the delay. I guess I don't come into the Community as often as I should.

Yes, I did resolve my issue. I have a large Collection and was trying to compare everything in that Collection to other records in another list. After it was kicked off the workflow seemed to start again after comparing ~1500 records from the Collection. So, to chunk it up; I inserted a pause step every 500 records. The workflow runs to completion as expected and does not restart. I really think the issue with large Collections is that the workflow dehydrates and restarts. But, cannot be 100% sure...

0 Kudos
Reply
Workflow Hero

Re: Run Site Workflow on large amount of items

Jump to solution

Hey Jeff,

thanks alot for your feedback. Seems like using a pause is required when processing this amount of records.

In general I would recommend to - if possible - not use workflows for these kind of tasks. It's just not what they are made for. However, many people do not have the skill/time/permissions to create e.g. a PowerShell script and therefore make use of the workflow engine. With all the cloud stuff coming up I'm afraid we can bury all hope for Microsoft to improve their onPrem workflow engine. Let's just hope that Nintex Workflow Cloud will be our savior one day

Cheers

Philipp

0 Kudos
Reply
Workflow Hero

Re: Run Site Workflow on large amount of items

Jump to solution

I strongly recomend you to use the PowerShell script (started from scheduled tasks) because this type of workflow will heavily utilize your timer service and make problems (slow run and delays) with all another workflows.

And your problem? I think, that this is timeout issue.

Have you ever tied to start this workflow via PowerShell? Try to set corresponding value to $proxy.Timeout.

    [String]$associationData = "<Data><Action>Create</Action></Data>"
    $username = "Domain\username"
    $password = "Password"
    $secpasswd = ConvertTo-SecureString $password -AsPlainText -Force
    $credential = New-Object System.Management.Automation.PSCredential($username,$secpasswd);
    $proxyWSUrl = $siteCollectionURL + "/_vti_bin/NintexWorkflow/workflow.asmx"
    $proxyWSUrl = $proxyWSUrl
    $proxy = New-WebServiceProxy -Uri $proxyWSUrl -Credential $credential
    $proxy.Timeout = 600000
    $proxy.Url = $proxyWSUrl
    $workflow = $proxy.StartWorkflowOnListItem($listItemId,$spList,$workflowName,$associationData)

Reply
Workflow Hero

Re: Run Site Workflow on large amount of items

Jump to solution

Just a minor improvement in case you're not going to store the password openly: 

$credential = Get-Credential -UserName $username

Reply
Workflow Hero

Re: Run Site Workflow on large amount of items

Jump to solution

There is one additional way (better for automation) if someone uses Workflow Constants:

[void][System.Reflection.Assembly]::LoadWithPartialName("Nintex.Workflow")
$spWeb = Get-SPWeb https://web.domain.com;
$spWebId = $spWeb.ID;
$spSiteId = $spWeb.Site.ID;
$workflowConstantCollection = New-Object -TypeName "Nintex.Workflow.WorkflowConstantCollection"
$workflowConstantCollection = [Nintex.Workflow.WorkflowConstantCollection]::GetWorkflowConstants($spWebId, $spSiteId);
$workflowConstant = $workflowConstantCollection | Where-Object {$_.Title -eq "adminCredentials"}
[System.Xml.XmlDocument]$workflowConstantValue = new-object System.Xml.XmlDocument
$workflowConstantValue.LoadXml($workflowConstant.Value)
$username = $workflowConstantValue.CredentialValue.Username
$password = $workflowConstantValue.CredentialValue.Password
$secpasswd = ConvertTo-SecureString $password -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential($username,$secpasswd);

Reply
Workflow Hero

Re: Run Site Workflow on large amount of items

Jump to solution

This must be run on the server unlike your previous version.

0 Kudos
Reply