Dynamic Replace: Run SQL Query For Multiple Dates

Hi, I have a query that I need to run to get data from the past 52 weeks. I don't have enough spool to run it for the whole time frame, so I created the below workflow to run it one day at a time and write the results in a Teradata table. The problem I'm facing is that it takes it takes an average of 20 minutes to for my workflow to run each day through the query and write it to the table, which obviously is way to long. When I run the query in Teradata, it only takes about 20 seconds. I have also attempted creating this workflow with the In-Database tools, but I haven't found a way to dynamically replace the dates in my sql query. Is there any way that I can make this workflow work faster?

Developer Tools

Dynamic Processing

In Database

Database Connection

In-Database

Accepted answers

Claje

Once you have it saved as a macro (.yxmc extension), and have configured your query, you can insert it like a regular tool inside another alteryx workflow. So if you have a workflow with all the dates you want to run, you can right click on the canvas and choose Insert->Macro.. and browse to that file. This will insert it as a new tool. From there, you simply need to connect your date input to that tool, and configure the tool so that the drop down has your date field selected, and you should be able to run the workflow!

All comments

cmcclellan

I'm just amazed that Teradata runs that slow I know you said you've tried, but a Connect in-db -> Write Data in-db is usually really quick and you qualify the SQL to get the last year only.

The trick question is how many records are you querying ? (daily and yearly count ?)

Claje

Disclaimer: I don't use Teradata, so this is generic advice.

A few thoughts on improving performance:

Can you configure your process to run for each individual week instead of 1/day? This reduces your overall queries to 52 from 365(.25)

If you don't include the table write, how long does the query take? If it is shorter, you might be running into issues because currently this process is trying to insert ALL 365 days at once into a temp table, which might be taxing your local machine. You could try putting this into a Batch Macro so that Alteryx only holds data locally for one day at a time.

In a similar concept to the above, you could use a batch macro, connected to an Input Data tool with a very fast "dummy" select statement (IE SELECT TOP 1 ID FROM TABLE), and a PreSQL statement which inserts into your TMP table using the SQL you are running in Teradata, and using the Batch macro to replace the date in the PreSQL statement.

From an in-database perspective, you could have a batch macro which updated a Filter tool for each date.

I'm happy to offer more details on any of these recommendations if you are looking for examples of how to implement them.

bradonchid

I am too, it has never been this slow for me. As I said before though, when I run the query in Teradata SQL Assistant, it is fairly quick. But for some reason it is really slow when I run it through my workflow.

The tables I am pulling from are huge and each day probably has an average daily record count of 6 million. So that would be a little over 2 billion records for the whole year. When I attempt to run the whole year, I spool out in Teradata.

Capture 2.PNG

bradonchid

I still spool out if I run it for each week. That is a valid point though, I didn't think about it trying to write all 365 days all at once. Could you provide additional details on how I could use a batch macro to avoid this? I haven't ever created a batch macro like that. Thanks!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703