How to Fix Query Timeouts in Alteryx

Last week, I built a large workflow while querying a subset of the data. With the workflow complete, I need to “open ‘er up”. However, doing so resulted in time-out errors. The query was so large it couldn’t complete. Transactional finance data….am I right??? I came up with two ways to solve this problem. Read on to learn how to fix query timeouts in Alteryx.

Fix Query Timeouts with a Batch Macro

My go-to method for fixing query time-out errors is to put the query into a batch macro. With a batch macro, you feed the query WHERE clause parameters to reduce the size of the data set being queried and eliminate time-out errors. It’s like rerunning the same query over and over and bringing back bite-sized data sets that are then unioned together, and you don’t even have to do the unioning. Alteryx does it for you.

My Use Case

In the workflow shown below, the two dynamic queries on the right side pull production volumes and transactional finance data. Understand these two important points about my use case.

1. Not only did I need to create a batch macro to query bit-sized pieces, but I also needed to query dynamically based on a date range. So, my batch macro uses a Dynamic Query tool.
2. I developed the workflow querying a subset of data and may need to do this in the future, so the workflow contains Detour tools to direct the flow to different versions of my query. Left queries only one budget node. Right queries all data (the batch macro).

This is the batch macro shown above in the red square.

Before I show you the details of this solution, I also want to note…

Workflow Components

Text Input

I start all dynamic queries with a Text Input tool. As you can see, nothing exciting or functional here. It’s just a starting place.

Formula

The Formula tool creates my start and end dates to be fed into the Dynamic Query tool. At one point, I swapped out -18 with a smaller number but left myself a note to remember what the prod version of the formula should be. I also tested more than one budget node and again left myself a note.

Dynmic Query

My first Dynamic Query pulls a unique list of all budget nodes used during a specific date range.

Select

I like clean workflows, so I add in a Select tool and removed the columns that won’t be needed going forward, as well as renamed a column. The less data you push forward at any point in the process, the faster the workflow will run. That might not seem like a big deal in this workflow, but I make it a habit, which pays off in larger workflows.

Unique

Then, once I have the full list, there will be some repetition, so I use a Unique tool to make the list smaller and truly unique.

Union

The Union tool combines columns so I have the start and end dates, which still need to be used in dynamic queries farther down the flow, and the unique list of budget nodes all in one place to be fed into the Detour tool. The Results look a bit wonky, but we’ll deal with that.

Now, we are here in the workflow.

Detour

Detour tools provide direction to the flow. They send data either left or right. I set the flow to go right when I only want to query one budget node and left when I want all data.

Filter & Select

The Filter tool reduces the data set to only the records needed for the dynamic query. The Select reduces the columns for the same reasons mentioned above. Here are the results after the Select before the full query. It’s just a list of budget nodes. These will be fed into the batch macro. NOTE, I don’t need the start and end dates here because they are also part of the batch macro. When I send the flow to the single budget node query, I need them.

Here are the results after the Select before the single budget node query. After writing this I realized I could take out ‘start here’.

Now, for my production volumes, I don’t get time-out errors, so I only need a batch macro for the finance transactional data. Let me show you how the batch macro works.

Batch Macro Components

Text Input

I begin with a Text Input tool to get going.

Formula

I add in the start and end dates as shown previously. They must exist here to support the dynamic query.

Control Parameter

The macro requires a Control Parameter that just describes what is being fed in.

Action Tool

The action tool is configured to replace the budget node in my existing dynamic query. To do this, select file and then change the string value at the bottom of the configuration panel. The text box may defult to the entire query, just delete it and enter on the value to be replaced.

Dynamic Query

The dynamic query tool works exactly the same as shown previously. I replace the dates with my start and end date. And the Action tool in the macro will feed one budget node at a time into the query. What comes out is appended or unioned together for a complete data set.

Output

The output tool is a formality. I could put a better descriptor in but don’t really need to.

The last step to complete the macro is to insert it into my workflow and tell it what I am feeding into it. The column with all of my unique budget nodes is called budget node macro.

Conclusion

Therefore, batch macros fix query timeouts in Alteryx when running large SQL queries. Now, the second solution the Virtual Solution Center showed me was In-Database tools, which I have never used before but would actually work really well for my use case. I’ll cover those tools in a future post. This one has run long enough.

Other Sweet Alteryx Content

• Three Ways to Start and End Every Alteryx Workflow
• Using and Output as an Input in Alteryx Workflows
• Remove Columns with Zeros or Nulls from Alteryx Workflows