community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

Reading Data from SQL Running Slow

Meteor

I am experiencing performance issues when reading from SQL Server dB. Reads take a really long time when I use a Input tool to connect (File format ODBC Database). To speed up the process, I limited the data sets via SQL Select statements in the Table/Query options:

Select * From dbo.vw_Headcount_Final Where dbo.vw_Headcount_Final.[CCH Lvl 1] = 'L1_0009' And dbo.vw_Headcount_Final.Version = 'Actuals' And (dbo.vw_Headcount_Final.[Fiscal Qtr/Year] = '2018-Q3' Or dbo.vw_Headcount_Final.[Fiscal Qtr/Year] = '2018-Q4')

Even with this, the reads take around 10 mins to get the data out of the Input Tool.  Is there a specific reason for that? 

 

I have tried using In-Database Connect In-DB tool with the same SQL conditions, and then streaming out via the Data Stream Out tool to do some processing. That appears to be produce a faster result. SQL Input Tool.gif

 

Are there any best practices to connect and read faster? I am using Alteryx Version: 2018.3.4.51585

 

Thanks

 

 

Quasar

Hi @sanjdhar

 

I tend to have similar issues with some of my SQL connections. Often it is due to the underlying SQL table not being set up very efficiently. Alteryx can't do much if the underlying table is slow. If performance improvements to the table are possible that might help.

 

If that's not possible, then I'd always recommend connecting in-DB. Try and narrow down your data as far as possible and only stream out the data you really need. Streaming out the data essentially means that the entire data set is downloaded from the SQL DB to your computer.

 

Another tip - while you're building your workflow there are 2 suggestions to speeding up development. You can put a filter into your in-DB stream and only work on a very narrowed down data set (eg: 1 day instead of whole month). When you're done developing you can remove the filter. The other suggestion is to once off stream your SQL DB into an Alteryx DB. You can then put all your in-DB tools into a container and disable it while you're working on the workflow. You can then connect your data to the Alteryx DB, where the stream out was before. Once you're done you delete the Alteryx DB and reconnect to your data stream out. Alteryx DBs have a much quicker read speed.

 

I hope these suggestions help!

Labels