Performance difference between Input Data & In-DB tools

vroque

Good Evening,

I have an issue while querying a table (10 columns and about 30 mi records) with Alteryx. When exporting the data from this query using MS SQL Server Management Studio it takes about 10 seconds, but when I try it with Alteryx, using the Input Data Tool, the workflow takes about 30 minutes to run.

In order to improve our performance, I’ve tried to use the In-DB tools and the workflow took about 20 seconds.

Is there any known issue that may be causing such a difference?

This is the query I’m using on both Input Data and Connection In-DB:

SELECT *

FROM  DATABASE_NAME

WHERE CONVERT ( VARCHAR , [DT_COMPTC],102 ) + CONVERT ( VARCHAR , [DT_UPDT],102 ) IN (SELECT CONVERT ( VARCHAR , [DT_COMPTC],102 ) + CONVERT ( VARCHAR ,MAX([DT_UPDT]),102 ) FROM DATABASE_NAME GROUP BY [DT_COMPTC]) and DT_COMPTC BETWEEN '2017-07-01' AND '2018-01-14' AND CNPJ_FUNDO IN ('CNPJ_1','CNPJ_2')

Here’s some info:

Data Types:

DT_COMPTC: Date

DT_UPDT: Datetime

CNPJ_FUNDO: Varchar

Alteryx Version: 2018.2.4.47804

Ping between computer and SQL Server: bellow 1ms

TravisCraven

Victor it is important to understand the key difference of how both options process. Using the data In-out open will run on your machine's RAM. Note: Your performance may dramatically improve once your job has been published to Alteryx Server, if your server has much greater processing power than your desktop machine.

When you utilize In-Database connections, the reason you are seeing tremendous performance gains is because Alteryx is converting the objects to SQL and processing directly through the SQL Server database engine itself. That is why there is a limitation on the amount of transformation objects available for In-Database. 

***An important note I have discovered in testing Server publishing***

Local database connections will throw alias errors for In-out connections and In-Database connections. In order to avoid this issue it is recommended to create the SQL Server connections on the Gallery Admin, then build your flow using that shared connection. This will allow for connection sharing and proper publishing.

The exception to this approach is in In-Database connections. In order to bypass the alias issue we needed to create file connections and store those connection files locally until we were ready to publish. In both connection cases once the app has been published, anyone with access to the workflow can download and update the flow running the job through those published credentials. If you try to save the In-Database workflow locally, the gallery connection will be severed and a new file connection will need to be created in order to publish. 

Hopefully this detail helps answer your question and saves a bunch of time troubleshooting. In addition, I am hopeful that an Admin UI update will be coming soon to streamline this process. 

Thanks,