SharePoint Workflow Running Slowly

Question

Spoiler

Hello, Alteryx Community!  I was wondering if you could help w/ a performance issue we're experiencing.  Here's the situation:

Need:
There are two workflows.  Both compile data from 10 SharePoint sites on intranet and do very minimal transformation. Both workflows are saved on our company's Alteryx Gallery and we need them to run every 15 minutes.

The two workflows are very similar.  They use nearly the same data sources (same SharePoint sites, but different lists and views).  They use the same output for SQL servers. Both have 10 browse tools on the SharePoint inputs (I can remove them, but didn't realize they might be slowing things down until I published to server and at this point I don't want to remove them until I'm confident it'll help).

These workflows replace an automated Excel workbook that connected to the same data sources and was set to run every hour.  That process took only 2-3 minutes to complete for both tasks that the workflows perform.  This leads me to believe the bottle neck is not SharePoint itself.  Otherwise, Excel would be slow too.

Workflow 1) Transformation includes adding a column to identify the data source, unioning, transpose, and output to two SQL tables on premises.  
     Workflow 2) Transformation includes adding a column to identify the data source and unioning and output to one SQL table on premises.

Performance:
     Workflow 1) Ran in 3 minutes.  This performance is sufficiently fast, although I'd be interested in it being faster.
     Workflow 2) Ran for 47 minutes. This is way too slow.

Questions:
  1.  What do I do to get this faster?!  I need to consistently complete in time to be run again for the next 15 minute interval.  
  2.  Why might the performance of one be substantially slower than the other if they're so similar in structure/input/transformation/output?
  3.   AHHHH!! What do I do...AHHHH?!  
  4.   OK fine disregard the third bullet.  If I get the top two addressed, that'll be fine.

Thanks!! Kurt

sathiya · Answer

This is not a solution but Debugging steps

1. If possible recreate the whole workflow.

2. If not save the current workflow and delete tools each one from the end to see which one is the issue

3. i will also run 2 other tools like Fiddler or wireshark (for advanced issues ) to see if network delay in some calls

Everyone knows that debugging is twice as hard as writing a program in the first place. So if you are as clever as you can be when you write it, how will you ever debug it? ~Brian Kernighan

kurtwz · Answer

Hey all, still need some input on this, if anyone has advice or information to share.  Thanks!  Kurt

kurtwz · Answer

@LordNeilLord

OK here's the update: I got the DB issues resolved that were unrelated to the issues I'm experiencing with this workflow.  I also turned on the performance profiling.  Unfortunately, the performance profile hasn't helped much this morning because I now get errors with Workflow 2.  I still get information on how long certain steps of the workflow took, but the workflow won't run to completion successfully with the errors I'm getting.  Here is how the performance now stacks up:

Workflow 1: Still running at around 3 minutes.  Most of the time is spent writing to the DB and has been tweaked as much as possible.  3 minutes is an acceptable time to run.  I'm turning my attention to Workflow 2.

Workflow 2: This consistently experiences errors and does not run to completion.  I tried three times this morning.  Got two errors related to two of the SharePoint inputs timing out (same ones each time).  These two SharePoint inputs have the two highest number of records in them at 7500 and 12600.  I realized that the list with 7500 records is no longer needed.  Consequently it has been disabled.  However, the one with 12600 records is still needed, so I have to figure out how to make that run.

The Excel file that this workflow will replace is able to refresh all the data for all the feeds in 2-3 minutes.  In Excel I broke out the feed that we're having problems with in Alteryx and ran that one individually and it ran in about 1 minute.  Can anyone please tell me what I need to do in Alteryx to get the input tools from SharePoint to retrieve data any quicker?

Thanks, Kurt