Data input tool (SQL) vs in db tool

[aatalai]

Hi all

Can anyone help me understand the difference between using a regular data input tool to pull data from a SQL DB and using the in db tools and then streaming it out?

Is one faster? Easier to set up? etc.

Ta

[OllieClarke · Accepted solution]

Hi @aatalai if you're just doing a SELECT * FROM TABLE then there won't be a difference between the two approaches. The benefit of the in-db tools is that they let you construct a complex SQL query using the familiar Alteryx tools, and send this query to the database without having to download all the data first. 

If you are moving data between tables in the same database, then using the in-db tools will let you do so without having to download the data to memory as an interim step too.

If you're happy writing SQL, then you can write your SQL query directly into the Input data tool, and input the specified data.

Does that help?

Ollie

[simonaubert_bd]

Hello @aatalai

I'm sorry but I will complete @OllieClarke (I hope this ACE won't be offended ;)   ) since there is a major difference between the in-memory and the in-database for your use case. In-database doesn't allow you to set a pre-sql statement  so it means all your set something have to be on the ODBC driver side.

Best regards,

Simon

[apathetichell]

@OllieClarke on 1mm records - there's no major difference - try doing that on a db with a few billion (or a few hundred billion) records...

[OllieClarke]

It’s a very good point @simonaubert_bd 

I was assuming a like for like connection, but you’re absolutely right. Always happy to be corrected :)