Issues with SQL Query/ODBC Connection

[lbardoe]

I have a query that pulls data from a table for the past 7 days. The query doesnt perform any calculations. When this query is run on the Gallery or in the GUI the query is taking around 30 mins to extract 126k records. If run this query in SQL Studio on the same machine as the GUI this extract takes around 11 seconds to extract the same data and also then causes the Alteryx Workflow to extract the data in around 40 seconds. Does anyone know why this might be occurring.

[apathetichell]

these are not necessarily the same process:

process 1:

you run a query on a db. there is an extract of the data (say 10k rows) in memory - or a push to a new view/table on the db.

process 2:

you extract all of the information form your query into a file on your local system.

If you run on canvas in Alteryx you are doing process 2 - that means that all of the data from your query has to go to your local system - this does not happen if you run a query natively on a db. you get some of that data - but not all of it. you can do this in Alteryx using In-DB tools - but once you use an on canvas tool you will be bringing your entire result into memory.

I've seen good results with alteryx and bad results. Anecdotally I ran 238mm rows earlier today out of snowflake (onto canvas - from in-db). this was by accident. It took a while (and wasn't too many columns) but not insanely long.

[lbardoe]

We found a work around where i ran the SQL Query within Powershell  around 5 mins before the Gallery was due to execute the Query and the now the workflow takes around 5 mins to complete rather than 30 mins. Why this would take so long in the Gallery without this interaction is weird but its now resolved.