Reading Data from SQL Running Slow

[sanjdhar]

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. 

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

Thanks

[kat · Accepted solution]

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!

[OmaratRJL]

I have a CSV of heavy strings max field has 551 characters. there are 246,000 records.

The output process on the ODBC was Finished running in 32:01 minutes
So I switched it to the SQL server Bulk Finished running in 2:06 minutes

This was reasonable. In the past I have used AWS redshift and it has moved pretty fast. Is there anything I can do to improve the read speed?