Connecting to AWS to pull data down in a query (very slow vs on premise) HELP PLEASE

[Mr_Visual]

Hi all,

Now i know im doing something wrong here so hoping it will be a quick fix. 

Scenario is: Alteryx on prem, SQL server in same country (physical one)   runtime for SQL query used to be ~2.5hours using the input tool for ~140mill rows. (the old server was dying hence the move to the cloud) 

however today with testing the extract is going to take over 8 hours to do the same thing. Server is now in the AWS cloud in Another country. Can latency be the single cause of the issue? 

So far:

In db connector works really fast however i need to stream out to my workflow at some point, its still very very slow. 

is my problem

1) alteryx needs to be in the cloud in the same AWS "sandpit" 

2) am i using the wrong connector - i.e. can the AWS downloader tool run a SQL command? currently using the input tool

3) something else? 

Please let me know what you need to see or know to help me get this working. 

[GiuseppeC · Accepted solution]

Hi @Mr_Visual

There's a lot on Community around this and I'll try to summarize what I found for you.

1. Yes, the network can be the cause of the massive delay, and no, in my opinion putting Alteryx in the cloud in the same AWS sandpit would be the ideal solution. You'll have to work remotely with Alteryx every time, regardless of dealing or not with data stored in AWS, plus you can't guarantee what path the data is taking to travel from the SQL Server to your Designer in the cloud.

2. I'd encourage you to try different ways to connect to your SQL Server instance. And in particular:

- check that you are using a driver which is supported by your version of Alteryx. List of supported driver here (check your Designer's version).

- use OleDB Driver instead;

- Create the ODBC connection using the ODBC Admin (did not test this one but Claje confirmed it works);

3. Unless you are running a purely ETL process, try and reduce the amount of data needed for your analysis with the In-DB tools before you stream out.

Part of this has been taken from the accepted solution here 

Finally, check this post about SQL functions within your Input Tool.

See if any of this helps!

Best,

Giuseppe