SQL Server Input x300 slower in Alteryx than in actual SQL Server

[pablo_martin]

Hi everyone,

I'm a having a problem with an Input tool that is driving me crazy.

I have an Input box querying an SQL Server view. Its a regular server I usually work with in other workflows without any problem.

But in this case, for some reason, the performance difference between executing the same SQL statement in SMSS and Alteryx is 300 fold. That means, in SMSS querying for 700 rows takes a second, whereas in Alteryx takes 5 minutes.

This performance is unacceptable as I must execute queries on the scale of thousands of rows, and the ETL process can't take hours (or even days).

Could you help me out troubleshoot this one? What could I look for? I'm out of ideas...

Thanks a lot,

[BenMoss · Accepted solution]

What about the width of the table and the column types? Are there a lot of string fields?

Could you perhaps look to leverage the in-db tools to perform your manipulation or aggregate your data before then streaming out into alteryx to perform any necessary manipulation.

Ben

[pablo_martin]

Hi Ben,

We ended up discovering the reason: apparently, using SQL Server functions inside the Input SQL statement was causing the massive slowdown. Removing them make the query perform with the usual performance in Alteryx.

But I'll actually mark your answer as the solution since we have also tested executing the SQL statement with functions insde an InDB tool as you suggested, and the query performed as fast as in SQL Server then, so that is also a solution.

In any case, thanks a lot!