Long run times when feeding data streams into SQL

[tonyhoang]

Hello,

My name is Tony, I'm relatively new to Alteryx. I have a question regarding a part of my workflow below.

I drew a red dot where the issue is.

The workflow left of the red dot runs in under 1 second. I determined this by disconnecting the Union from the DB-data stream in, and creating an output file of ~7000 records.

The workflow right of the red dot runs in about 30 seconds. I determined this by creating an input module, referencing the output file mentioned above, and feeding that into the Data Stream In.

However, when I directly connect the Union to the Data Stream In, the workflow in its entirety takes over 7 minutes. Does anyone know why this might be, or how I could investigate further? I can't seem to find any detailed runtime logs.

[CharlieS · Accepted solution]

Hi Tony.

It sounds like you've done some good testing so far. Your isolation process using tool containers is a great troubleshooting strategy in Alteryx. I don't have an answer for you, but I can shed some light on a few things.

- You can enable Performance Profiling from the Workflow Configuration window Runtime tab. This will report a list of the time it takes each tool to execute in the Results window the next time you run your module. (in ms time and workflow percentage)

- That being said, those runtime estimates along with the bold lines that Designer shows are just estimates of tool processes. It's my understanding that many processes in the Alteryx Engine don't lend themselves to be quantified in such a linear fashion. So they are just estimates.

- If you were able to use an Input file with the 7000 records and the second half ran quickly, it seems like the act of lumping that data together for one stream may be the issue. You could also try putting the tools in the container into a Macro. That might help transfer all the data to those In-DB tools at once while still remaining a dynamic process overall.

[tonyhoang]

Thank you for the prompt reply, Charlie.

I turned on Performance Profiling, and the Data Stream In module is definitely the culprit, taking 98% of the total runtime.

If your hypothesis is correct in that the act of lumping data into one stream is the issue, would we expect the runtime to resolve if we remove the PII Template? How about bypassing the Union tool altogether and simply connecting the Multi-Field Formula tool straight into Data Stream In?

[tonyhoang]

Charlie,

I went ahead and did some diagnostics troubleshooting.

For my specific workflow, I could have unioned the streams after the data comes out of the DB. Thus, I moved the Union to a later part of the workflow.

This resolved my issue. Thanks for pointing that out!