Seeking solutions to run my workflow faster

Question

Hello Everyone,

I created a workflow connected to a database with three data tables. Ultimately I would like my output containing data from previous year (2021) and current year (2022) so I created a filter ("INVOICE_DATA_PK > '2020-12-31') but it's taking more than more than one hour to run. If anyone know a solution to decrease the running time it will greatly appreciated. Thank you in advance.

Adrian_T · Answer

Hey @rdasilva,

Essentially we would want to explore if resource intensive queries are more performant in Alteryx compared to when they are run on your DB. In your workflow, I would add a Data Stream Out Tool just before each of the Join In-DB Tools and use the normal Join, Formula and Select Tools (found in Join and Preparation tool categories) to perform the rest of your workflow logic.

Remember to also enable AMP. Here's how you can do so: https://help.alteryx.com/20221/designer/alteryx-amp-engine#:~:text=selection%20from%20the-,Use%20AMP%20Engine,-check%20box

rdasilva · Answer

Hi @Adrian_T ,

Thank you for the recommendation. I've been researching on how to use Data Stream Out but I wasn't able to find tutorial on the subject. Based on the screenshot i shared where would you use the Data Stream Out in the workflow?  Essentially I only want data from current year and prior year as my output.

Thank you,

Adrian_T · Answer

Hi @rdasilva,

Your workflow contains all In-DB tools where queries are pushed to your DB for In-Situ processing. This would mean that the performance issues are attributed to your underlying database and how fast it can process queries. That said, here are a few suggestions on your next steps:

* Workflows containing multiple joins and unions can increase the length and complexity of the query taking more time to process. Your workflow contains multiple joins, so it might be more performant to use the Data Stream Out Tool to stream data into Designer for processing instead, especially with the AMP Engine.
* Use performance profiling to determine which part of your In-DB processing is taking the most time.

* Here is a useful resource on performance of In-DB tools and when processing in Designer might prove more performant: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/FAQ-How-Do-the-In-Database-tools-Work/ta-p/369738

Hope the above helps to frame your next steps to developing a more performant workflow! 🙂