Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

pablo_martin
6 - Meteoroid

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,

 

 

2 REPLIES 2
BenMoss
ACE Emeritus
ACE Emeritus

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
6 - Meteoroid

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!

 

Labels