Performance: Input (green components) with In-DB (blue)

[slickshoes]

I have a process that uses green components with some blue (In-DB) throughout.

And was wondering if there is any performance gain with using all "green" components?

*Seems like there would the conversion components would take extra time/resources...

cheers,

[CharlieS · Accepted solution]

Hi @slickshoes 

You are correct in that there is a performance cost to streaming the data back and fourth from the In-DB tools. The standard Input (green) vs In-DB (blue) will depend on several variables including what you need to do, resource availability, permissions, and so on.

In-Database (blue):

- The user must have write privileges on that database because these tools write temporary tables. 

- It's common for a database host to have a larger pool of computing resources compared to your local computer. Using In-DB tools will perform a majority of the processing on the database, which is often faster.

Input (green. in local Designer):

- Old reliable. 

If you can share more information about what you're doing, I'm sure the Community could share more thoughts on the matter. 

[MarqueeCrew]

@slickshoes ,

I'm just adding my two cents here to @CharlieS 's reply/solution.

In my use of in-db tools, the "write" privileges are generally present.  User tables are used behind the scenes for Alteryx, but that's why users generally have access to create and write to user-tables.  It's not a drawback.  The reason to use all "green" components may be to make use of the "dynamic" input.  If you're querying small sets of data and want to use the dynamic input to modify your where clause, you  might want to stick to these tools.

The use of in-db tools adds to the testability of the workflow.  If you construct join and other logic within your SQL and use a green tool, your workflow is less readily explained or testable.  The SQL created by the in-db tools is likely less performant than a well thought through SQL that is analyzed by your DBA or yourself.  With substantial queries, this might be a reason to use the "green" tools loaded with pass-thru SQL.

Cheers,

Mark