In-database

[mst3k]

A few basic questions about in-database tools-

The typical use case is where you have some set of data and you want to bump it up against say a SQL database, right. It's more efficient to stream your small table into a large database and do the processing there, then bring the results back.

So if we have a reverse scenario - something small in the database but a large dataset in our workflow, would you instead just SELECT *  and do the opposite - do all the processing locally? Then you don't really need to do a CONNECT IN-DB  -->  DATA STREAM OUT, you can just do an ordinary INPUT tool and do SELECT * right?

Even in the first scenario, is a DYNAMIC INPUT with SQL embedded into it a substitute method for skipping In-Database tools? I know In-Database has some other advantages. But I also see drawbacks - my current project I'm testing both ways, and writing data out to the SQL server is slow to do my DATA STREAM IN, even if the SQL server can eventually crunch the actual query quickly. The alternative method of DYNAMIC INPUT, and replacing the WHERE clause with the identifier from my data, runs much faster end to end, even if it's clunkier (it has to execute the SQL query in multiple "batches" over and over again because of the limitation of number of items you can put in a WHERE clause)

Next - say I want to join this data to something in the database. Is the correct way to do that a CONNECT IN-DB with just "SELECT * FROM TABLE1" followed by the JOIN IN-DB to my streamed-in data? The SELECT * feels a bit awkward, but that seems to be how you use the in-db tools to join to a table on a SQL server?

[RishiK · Accepted solution]

@mst3k  I wouldn't say that the Dynamic Input with embedded SQL is a method to skip the in-db tools. The In-db tools primarily benefit with large data volumes whereby you want to put the processing overhead on the database rather than on Alteryx Designer, so if the non in-db method works for you, then stick to that.

For your 2nd question, you don't need to default to the SELECT * in the Connect In-db.  When you connect to your database and select a table, in the Table Browser you can select specific columns you want to connect to as per the below:

The query then is specific to what you have selected:

[ChrisTX]

You mentioned...

  it has to execute the SQL query in multiple "batches" over and over again because of the limitation of number of items you can put in a WHERE clause

You also have the option to upload data from Designer to a TEMP table in the database, and use the temp table in a Join in-DB tool.  Of course uploading to a temp table requires a permission granted by the DBA.  This avoids the WHERE clause limit problem.

Chris