Data Stream Out Tool is very slow

[beadysquared]

Hello,

I am trying to output a query I did using the in-DB tools. It only results to 2,667 rows, but the run is taking more than an hour to finish.

I created another workflow that has more rows but runs faster than it (can be done in 1 min up to 5 mins), and I am at a loss as to how to troubleshoot it. Both use an ODBC connection string to connect to a SQL Server Database, but the performance of the two are very different.

May I get some guidance on what to check to determine what are the possible differences between the two?

[mceleavey]

Hi @beadysquared ,

The number of rows is only tangentially related as it boils down to the size of the data being streamed. At the point of streaming out you are now pulling data across the network, and so the larger the data the slower the run.

Does the data with fewer rows have considerably more columns or columns which contain large data sets?

M.

[ChrisTX]

Are you referring to the timing difference of the entire workflows, or only the Data Stream Out tool?

I'm guessing the SQL is different?

Try running both SQL statements using a tool like Microsoft Access and see if the run time is significantly different.

Chris

[beadysquared]

Hello @mceleavey,

The two workflows are sourced from the same underlying tables, but the slower workflow has a part where one table is joined to another table twice on different join columns & does not use a summarization tool - do you think these affect its performance? With respect to number of columns, I think the two workflows are comparably similar

[mbarone]

Longshot here - but on the workflow that takes over an hour, and has a join, is that join producing multi-dimensional joins?  That will kill a workflow's speed, along with probably giving you records you don't want.

[ChrisTX]

For the joins on the slower workflow.... are all of the Join fields indexed in the database?

I suspect if you take the same SQL and try it with the same ODBC driver in Access, you'll see the same slow run time.  Which would mean the problem is with the SQL (missing indexes, etc), and not within Alteryx.

Chris

[beadysquared]

Hello,

I would like to try this solution, but is there a way to create and alter table indices within a workflow that is built with in-DB tools? I know there is a Post SQL Statement for the Input Data tool, but there isn't a similar interface in the Connect In-DB tool.

[ChrisTX]

Table indices can only be added, typically by a DBA, directly within the database.

Chris