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

[nelsonwcf]

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')

[KaiC]

I had the same issue.

Switching to the Microsoft SQL SERVER OLE DB connection fixed this for me .

[wlhale]

Wow, i just found this after running a query from SSMS and then doing it in Alteryx with an ODBC input connection. It went from 2 minutes in SSMS to 40+ minutes in Alteryx (using ODBC) so i built an OLE connection and it's back down to a couple minutes. Not sure why but i guess i'll try and use OLE when and where i can

[patrick_digan]

I just wanted to say thanks to @nelsonwcf  and @Claje / @CassC  for suggesting OLE db. I had a query that basically wasn't running using the SQL Server driver and OLE db solved it!

[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-ser... link. Can someone share the steps to make the connections.

Thanks in advance.

Thanks and Regards,

Prasad Jathar