Input Data vs Connect In-DB

Hi Community,

I'm new to Alteryx and this may be a real simple question, but I haven't found anything (at least in the five or so minutes I've been looking). What is truly the difference between connecting to a database (Oracle in this instance) with the "Input Data Tool" and using the SQL editor with a pre-written script versus using the "Connect In-DB" tool with the same database and pre-written script?

For this example, let's say I do something really simple like execute the query and export it to an Excel workbook. I know that you get other functions with the In-DB tools if you start with the "Connect In-DB" tool, but I'm just wondering from an input perspective if there is a difference.

Thanks!

Input

Accepted answers

AmeliaG

Hi @smixon,

Thanks for your question!

In your use case, there is very little difference. Whether the SQL query runs in the input data tool or in the Connect-in DB tool, it will be executed on the database. The only difference is if the data is returned over the network. With the regular input data tool, the data will be automatically streamed over the network into local memory on your computer. With the Connect-in DB tool, the data will only be streamed out of the database if you use the 'Data Stream Out' tool.

Therefore, if you simply want to output to an Excel sheet, the two workflows would look like:

Both of these workflow are effectively doing the same thing.

All comments

AmeliaG

Hi @smixon,

Thanks for your question!

Therefore, if you simply want to output to an Excel sheet, the two workflows would look like:

Both of these workflow are effectively doing the same thing.

smixon

Awesome! Thanks for the reply!

AmeliaG

To add a bit more color, all of the regular Alteryx tool (i.e. NOT In-DB) are processed in memory on the machine where you are running Alteryx.

The In-DB tools were designed to allow you to visually build a SQL query which is processed on the database. An example where you might want to use In-DB would be if you have a huge table in Oracle and you want to blend it with a small Excel file and then write a new table in Oracle. You can use the In-DB tools to stream the Excel data into a temp table and then do the join In-DB. This would reduce the overall data movement over the network and optimize your run time. Of course, if your endpoint is a different database or an Excel file, you will need to stream the data out of Oracle regardless.

Hope this helps!

Amelia

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703