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]

are you using input data/dynamic input or connect in-db? if you are using a canvas tool all db processing is done in memory... you don't want to do that.

[akhavea2190]

Use in db tools to faster output

[lbardoe]

I have tried IN-DB connections and still no joy. The connect in-db runs really quickly but then when i connect the data stream out to get an output its taking a long time to extract the data the same as the in canvas tools. 

[akhavea2190]

When you setup your SQL connection, did you request the bulk loader ?

[lbardoe]

I am not loading any data into a Database i am reading data from it.

[akhavea2190]

Can you share your settings used for setting the connection ?

[apathetichell]

When you say that you run in a few seconds in SqlStudio - are you running the query - or exrtacting the file (ie running the file and downloading it?)... Your IN-DB experience seems typical of datastream out when your system resources are limited (not enough ram) or you are bringing a large  result into memory. I would expect a similar experience in SQLStudio (or DB Visualizer) ie - running the query is quick. Creating a .csv of your query results on your computer takes awhile...

[lbardoe]

I am running a query to extract data from the Database and then use alteryx to generate around 10 different reports of the returned data. The current data is around 130k records with about 7 columns (so not massive). We have another process that pulls around 3m records out (admittedly its grouping the records down to 130k) but that query takes around 51 seconds to run in the Gallery. The only difference is the time one runs @ 22:00 and the other @ 09:00. But even @ 09:00 i can run the SQL Studio query and it only takes 11 seconds. I am trying IN DB but it looks like i need Admin permissions to set the Type to System rather than user. 

[davidlemus]

Did you find a solution? I am having the same problem.. when I run the exact same query on Azure Data Studio I get the results in 2-3 min.. but in Alteryx Designer through ODBC v18 it takes 4-5 hours... it is insane. Also, it seems I can't even cache the data if I put all In-DB tools in a container. I tried other ODBC versions, re-creating the connections.. but still nothing seems to work to make it faster.