How to/Can I Optimize Microsoft SQL Server Connection???

[Wiggot01]

I've got a problem that I can't figure out. With Oracle, MySQL databases, and anything else I've used, Alteryx is always faster than TOAD.

I've got this query that's connecting to a Microsoft SQL Server warehouse. It's always looking at yesterday's data, with no active data flowing TO the warehouse except once each night.

In TOAD, the query runs in under 10 seconds.

In Alteryx, Input Data Tool, it is 100%, completely and utterly unusable. I've given up after letting it run more than 6 hours, just trying to get 3,530 results.

In Alteryx, Connect InDB Tool, sometimes it'll take 30 seconds, sometimes it'll run for hours and never finish.

I don't know what to do. It's the exact same query - I literally copied the language straight out of TOAD and into Alteryx, ran Test Query to verify syntax, and then tried running it.

You might say, if it runs so well under TOAD, why not leave it there... well, it's part of a 30-something step workflow, that produces more than a dozen files and edit check output and we can't schedule it in TOAD, but we can schedule it in Alteryx.

I set up the connection in ODBC Admin, and used that under Manage In-DB Connections, so it's not like it's overly complicated, or even that I have the option to change options/settings.

[ImadZidan]

Hello @Wiggot01 ,

I share your frustration. 

Out of curiosity, in TOAD do you also use ODBC or Tnsname.ora file or may a different mechanism for connection.

Just to give me an idea.

[Wiggot01]

I think I fixed it by switching to OLE instead of ODBC.  At least, it's been responsive for the last 18 hours, and several executions. I'm down to a more reasonable 10 seconds to get 3,000 results, at least.

[ImadZidan]

Hello @Wiggot01 ,

Well done for fixing the issue.

Just a word, when a situation like this occurs, it would be good to look at the environment in which the deployment is taking place. 

again well done.

[SeanAdams]

Hey @Wiggot01 

there's a few things to look at if you ever run into this again:

- As you say - try OLEDB instead of ODBC to see if that helps

- InDB tools actually wrap the queries in additional layers - if you look at the query that's ACTUALLY being executed (which you can do by putting an InDB Dynamic Output on the flow) - it's not the exact same query (they use common table expressions to break down each section of the flow on MS SQL - https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sq...)  .   Based on this - you can use a normal input tool with your SQL query, and it doesn't add the CTEs to the flow.

You can also turn off rowcounting on the input-tool - this also speeds up the query.

And finally - for any query on any SQL box - there's almost always a way to speed them up by indexing the fields that are used in joins or where clauses - well worth looking to see if there are any indexes that MS SQL would recommend, you can use SQL Server Mgmt Studio (SSMS) to run your query and get suggestions by looking at the query plan.