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

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

SQL performance issues (3 different ways) - Please help!

Atom

Hi All,

 

I have a query that runs against a Microsoft SQL Server.  It joins data from 2 views by concatenating two fields to create a primary key from each view and then joins them.  It results in 130k lines of data, about 5 columns.

 

In Microsoft Sql Server Management Studio, the whole query runs in 34 seconds In Alteryx it takes 30 minutes.  Here's what I've tried:

 

  1. Used Input tool and connected it to my SQL server, adding the same SQL query that runs in 34 seconds on SSMS. Takes 30 MINUTES in Alteryx.
  2. Used multiple input tools to bring in each view and did the concatenation and join resulting concatenation and join in Alteryx.  Takes 30 minutes.
  3. I have tried the IN_DB Input with both the query language - 29 minutes
  4. I have tried multiple IN_DB inputs and doing the joins in DB.  29 minutes.

Somebody please help with this.  It's making development of my workflow ridiculously time consuming!

 

 

Alteryx Certified Partner
Alteryx Certified Partner
You mention it takes 30 minutes when using the in-db tools, how are you using them? Are you still streaming data locally after you perform the task or using the in-DB browse (Which has to pull the records locally).

It sounds like a slow network issue but Not if the in-db tools are slow too.

If this is only slowing down development consider caching the data, thus meaning you don't have to wait 30 minutes every time you got run. Of course this isn't a long term fix to the speed problem but it will help your dev speed.

Ben
Atom

Thanks for your reply.  I've tried a combination of things in different workflows.  Doing as much as I can without In DB browse or streaming the data in to the local workflow. Nothing seems to make much of a difference, and, as you mention, its not a network issue.

 

I'll take your advice re: caching, take my lumps and move on.

 

Thanks!

ACE Emeritus
ACE Emeritus

 

 

As noted by a different user, this sounds like it would be a network problem to me as well because of your first comment. 

 

  • Used Input tool and connected it to my SQL server, adding the same SQL query that runs in 34 seconds on SSMS. Takes 30 MINUTES in Alteryx.

 

A way to confirm this:

 

 

I would have your DBA look up the query execution plans of your queries run from SSMS and also after it is run from Alteryx and determine if they are different at all.

 

Likewise, your DBA should be able to look at the execution of the queries on just the Sql Server database and determine any timing differences. They will be able to tell you exactly how long each query took.  If both queries return in ~34 seconds, you know the problem is moving data from your server to your alteryx machine. However if you find a significant time difference within SQL itself, then the potential solution to your problem could be very different. 

 

 

Hope that helps.

 

 

 

 

Labels