Alteryx vs MS SQL Server Management Studio: Why Alteryx is so Slow?

I believe I'm doing something wrong in Alteryx since I'm a new user. My company uses Alteryx as its dataflow tool and I need to extract the data from a simple query (below) and saved to some local file. I'm using CSV. The results have 5.7M rows.

Issue: When exporting the data from this query using MS SQL Server Management Studio, the total time is 0:40 to 0:60s. When running the same query in Alteryx using either in the Input Tool or the In-Db (Connect DB + Data Stream Out) it takes around 20-25 minutes. My guess is that I'm doing something very wrong.

I followed all the connection instructions in Alteryx website for both data connections and In-Db Connections and I'm still getting very slow extraction times. My colleagues have the same issues (though most of them never used other tools before - they believe this to be normal).

Before posting here, I browsed the forum for similar issues and while found some complaints on the extraction being slow, no real solution was provided. So I decided to come here and ask the community how to make the SQL Server data extractions faster in Alteryx as we (me and my team) must be doing something wrong.

Thanks in advance.

Complete Query:

SELECT
a.LeadID
,a.Employee_ID
,a.Form_Name
,a.Date_Submitted
,l.SalesCycle
FROM [Lead_Activity_Reporting].[dbo].[Activity] a
INNER JOIN [Lead_Activity_Reporting].[dbo].[Lead] l ON a.LeadID = l.LeadID
WHERE l.SalesCycle IN ('Nov 2018', 'Dec 2018', 'Jan 2019')

Input

Output

Best Practices

Optimization

Database Connection

Accepted answers

nelsonwcf

Hi Claje,

I'm replying to you now just to summarize the problem and also mark this as the solution, since there are at least two other related posts regarding ODBC slowdowns in Alteryx. However, none of them have solutions.

In summary, the issue is that Alteryx doesn't work very well with the ODBC driver. More specifically, if you select Microsoft SQL Server when creating the connection.

There are three solutions:

- Use OleDB Driver instead;

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

- Use a Python script;

I'm contacting Alteryx support to check this issue/bug? but these options above should do the trick for now.

All comments

cmcclellan

The key question is - how are you implementing this in Alteryx ? As in, what does your equivalent workflow look like ?

nelsonwcf

Very simple:

Input Tool with the SQL Query -> Output Tool to CSV.

Also tried to use

DB Connector -> Data Stream Out -> Output tool to CSV.

I don't think it can be more basic than that.

Additional information: it seems it is not just MS SQL Management Studio, extracting data using DBeaver through a jdbc connection takes the same time to query the data but an additional minute to extract it to CSV.

Can't find any settings in these Alteryx tools. If there are, they are located somewhere else.

That's the reason I believe something is really wrong with our Alteryx. How can I get more information for the troubleshooting?

Pjathar

Hi All,

I am also facing slowness issue while pulling data from AZURE SQL Server using ODBC connection. I would like to try extracting data using OLE db connection. However I am struggling to make the OLEDB connections. I have downloaded OLE DB drivers using following https://docs.microsoft.com/en-us/sql/connect/oledb/download-oledb-driver-for-sql-server?view=sql-server-2017 link. Can someone share the steps to make the connections.

Thanks in advance.

Thanks and Regards,

Prasad Jathar

OLeDB Connection Issue.PNG

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703