Workflow Taking Too Long to Run

[JustinZeh]

I have tried to construct this workflow about a thousand different ways without any luck. I am currently trying to create a workflow that will replace an old antiquated canned report. The problem I am running into is a couple of tables contain a significant amount (one table contains a colossal amount) of data, which is unfortunately necessary for the report I am trying to create. Fortunately not all of the columns are required for each table and therefore I have tried joins and multi joins after the select tool but as stated above, the workflow still takes too long to run. I thought about using In-Database tools; however, I do not think that will work due to the fact I only have read only access to the database. If I am wrong about this, please let me know.

Does anyone have any suggestions on how I can construct a workflow that would speed things up or even a pre-SQL statement, macro, or join that maybe I have not thought about, that would help? Any advice would be greatly appreciated and thank you in advance!

[dheissner]

In-Database tools could speed up processing time depending on where the bottle neck in performance is.

There can be times when returning very large amounts of data over the network, the network transfer time will be the cause of most of the workflows delay. Also it can make a big difference if your workflow is making one request to the database vs. 100's or 1000's of calls to the database.

Do you have an estimate on the amount of data that is being pulled out of that database for this report? what the network path is (from the database to the machine where you are running the workflow on), and any key network metrics such as latency, bandwidth, congestion or packet loss %. 

So the network information (size of data being transferred) along with the # of calls being made to the database are areas that could be worth looking into for speeding up this workflow.

[DavidSkaife]

Hi @JustinZeh 

One idea to consider; If the issue with the length of time it's taking to run is due to the data being extracted, have a think about creating an extraction workflow that extracts all the data you need and places into Alteryx Database Files.

You can then build the replacement report workflow on the back of the Alteryx Database Files so you only need to update the database files once, and the report workflow can be ran multiple times while building/testing?

[FreeRangeDingo]

Have you enabled performance profiling to ensure you know where the flow is taking so long?  Click in the Canvas and then go to Runtime in the Workflow - Configuration.  Click the checkbox that says "Enable Performance Profiling".  After your un the flow, you'll see something like this in the Results panel.  This will tell you if it's your query or a join or something else.  If it is a query problem, then I would try using Dynamic Input or Batch macro as described here and here.

https://www.bigmountainanalytics.com/solve-query-timeout-issues-in-alteryx/

https://www.bigmountainanalytics.com/how-to-fix-query-timeouts-in-alteryx/

[FreeRangeDingo]

You could also try this...

https://www.bigmountainanalytics.com/how-to-reduce-the-size-of-alteryx-data-sets/