Alteryx Workflow Performance: The Network Effect

[dheissner]

Introduction

Alteryx Designer makes accessing data that resides on the network possible for anyone, regardless of their technical background or experience. With tools like Input Data, Connectors, In-Database tools, Download, and others, Alteryx can access data from all over your network. This includes files on shared drives, enterprise databases, cloud-hosted data (storage services, database, data lakes, warehouses), and data services accessed through APIs, just to name a few.

Alteryx, like other data dipping tools, shields the complexity of the network from you. However, there can be times when you need to think about and understand how your workflows are utilizing the network. In performance tuning as well as troubleshooting analysis, knowing a little bit about the network can go a long way.

In this article, we look at a few scenarios where workflow performance issues were related to the network. We will also discuss ways to identify and work to resolve these.

Network Latency, Bandwidth and Packet Loss

Anyone who has built workflows on their laptop or desktop computer and then later deployed them to an Alteryx server has probably come across a workflow that did not perform as expected when running on Server. One experience I had involved a workflow that ran for about 1 hour on an analyst’s laptop, but when deployed to Alteryx Server, it ran for over 10 hours, performing the same tasks on the same datasets.

When this happened, some team members asked, “Why is the application server so slow?”. There was a company that made performance monitoring tools that gave out t-shirts at trade shows once that had printed “It’s the Network” on the front and “It’s the Application” on the back. This issue reminded me of that funny t-shirt because unless you have actionable data to review, there can be lots of finger pointing as to where the root cause of a performance issue lies.

While an experienced network engineer could use a tool like Wireshark to pull network diagnostic information from the laptop, server, and points on the network (to identify faulty or misconfigured network devices, measure high delays or points of packet loss along a network path, and more…), for many of us in our work environments this is not always possible.

On this project, fortunately, we were able to work with the network team and eventually determine that a segment of the network between the Alteryx Server and one of the data sources was experiencing intermittent bandwidth issues and high packet loss. There was also much higher network latency between the Alteryx Server and that data source compared to the network latency when run on the laptop.

In this case, we clearly needed the support of the network team to identify the root cause of the issue. However, network latency, the communication delay in going from one point to another over the network, is normal in varying degrees and should be planned for when designing and building your workflows. The next scenario looks further at network latency and how performance can be adversely impacted by a chatty workflow design.

Source: Wikipedia

The Chatty Workflow

The workflow in this scenario had data that was being retrieved from a customer service call center. The call center’s software had a log of activities and provided a way to access these through a set of REST-based API calls. To do this, the workflow used the Alteryx Download tool.

The call center API provided a way to send a request for data over a specified time (ex. last 24 hours), and this request would send back a token. After a short period of time, you could send another request passing in this token, and if it was ready, you would receive the requested data back. Using this approach, at a minimum, you could make two API calls and have your data.

But this workflow did not use those API calls. Instead, it made an API call that returned all of the call record IDs for the last 24 hours. The workflow then looped through all these records using an Alteryx macro that made individual REST API calls for each record.  This approach resulted in the workflow making over 1 million calls over the network to the call center's server at times.

By building this workflow in a way that made many individual network requests, this workflow was very sensitive to network latency. So, if you ran this workflow from a location that was close to the data source and had low network latency and then moved it to a location far away from the data source with high network latency, you could see very big differences in its performance.

Making millions of calls over the network when you could have made just a few is generally not a best practice. In application development, this design is referred to as the "Chatty I/O anti-pattern.” Even though when using Alteryx, I am not working as a software developer, when building workflows, trust me, you'll want to avoid any designs that are called "anti-patterns" if workflow performance is important to you.

SQL Database Calls

This type of chatty workflow performance issue can also be seen when making calls to a database. Workflows that contain calls to the database for reading (select) or writing (update/insert) one individual record at a time should be avoided. I've reviewed workflows that loop through a set of data 1 row at a time and then make individual calls to a database. When working with SQL databases (ex. Microsoft SQL Server, Oracle, IBM DB/2, etc.), you should investigate using IN, WHERE, and other supported clauses or leveraging stored procedures to perform operations on groups of records at once instead of on individual records.

Network Performance: Doing Your Part

So how do we know if the network segments between where my workflow is running and the data sources it accesses are performing as they should? The answer, unfortunately, most of the time is that unless you are a network engineer with monitoring tools or there is an obvious network outage, you will not know.  Regardless of your network’s performance, it is always a best practice to build your workflows in a way that minimizes calls over the network and is less chatty.

Where the Alteryx Engine Runs on the Network

When analyzing the network’s impact on our workflow performance, we should consider where the Alteryx engine is running from (ex. on my laptop, on a server, etc.) and how close it is to the network resources it is accessing (ex. Files, databases, services APIs, etc.).

A workflow I reviewed recently had steps that pulled data down from a relational database running at a cloud provider’s hosted location (ex. Azure, AWS, GCP), then created a temporary table on the same database and uploaded most of the data that had just been downloaded back to it. At some point, this workflow downloaded the data from the temporary table.

We could have reworked this workflow using In-Database tools or database-stored procedures and greatly improved performance, but the project leader was most concerned about the data transfer costs occurring from their cloud provider. So, we explored how we could run their workflows closer to their data sources. Deploying Alteryx Server into their cloud service providers environment was one option. The other was considering the use of Alteryx Designer Cloud.

For reasons that included improving workflow performance, this team decided it would be best to deploy an Alteryx Server into their cloud service provider’s environment.

Workflow Performance Tuning

If workflow performance tuning is of interest to you, one of the easiest ways to get started is to look at the "Enable Performance Profiling" runtime setting in Designer. This setting can be enabled on the Runtime tab in the Workflow Configuration panel. With this setting enabled, you will be given timing information for steps in your workflow when run in Designer. This diagnostic information shows how long each step takes to perform as well as the percentage of total runtime.

This setting is helpful when you want to

• Analyze steps in your workflow that go out over the network
• Compare the performance of individual steps in workflows when run from different locations (ex. laptop dialed into the corporate network, from the office, on a server in our data center, on a server in a cloud providers network)
• See the impact of changes made to your workflows

Closing

It is always a good practice to create your workflows with the network in mind. Hopefully, this article helped you think about your network differently and provided some ideas on how you can design workflows to make more efficient use of the network they run on.

If you find this topic to be of interest, please add a comment below and let us know if you would like to dive deeper into any areas in a follow-up article.