SSMS vs. Alteryx Speed Issues

[squigley17]

We have a testing server hosted remotely that when connected through SSMS will run 2 million records in less than 10 seconds and about 10 minutes for the same amount of data to run through In-DB Alteryx then outflowed into the regular workflow or using a regular input tool. My current setup is a local server to the wifi I am on and the SSMS and Alteryx input tool take equal time to load. The support team has not been very helpful with getting this fixed with us. My question to you all is have you ever been in a similar situation where you connected to a database remotely? Does speed become an issue? I am wondering if Alteryx is just not a tool that can be used for my situation since it will require I download the data in order to use the regular tools.

TLDR: Database connection over wifi is too slow using Alteryx and takes seconds on SSMS. Why!? Can you help me fix this?

[Claje]

Hi,

Can you share any information about the SQL driver that was configured for the In-DB connection?  I don't need server information/ips/authentication, but information as to what driver is being used could help.

Anecdotally, I have observed some slowness issues to be caused by driver/connection settings in the past.

[squigley17]

I am not sure i know what that means. When I am in Alteryx I go to Input then Microsoft SQL server. Does that answer your question or you want information about the setup of the actual server type?

[Claje · Accepted solution]

So I know in-database connections can be slightly different and as such I'm going to ask you to try this in a regular Input Data tool:

Instead of choosing "Microsoft SQL Server" choose "Other Databases"->"OleDB".  There should be a window that pops up and at least one option should have both the words "Microsoft" and "SQL Server" in it.  Try choosing that and clicking "Next".  Then, enter your server name and any other connection configuration, and try running your 2 million record query to see if it is any faster.