Connecting to Large ODBC datasource - Processing is slow

[wottel]

I'm new to alteryx and have a data warehouse (ODBC) table that houses doctor/hospital information by unique provider id. I'm trying to build a workflow that will allow a list of Provider Id's to be entered into an excel and pull only those providers from the warehouse tables.

The way Alteryx appears to work is that it first pulls all of the warehouse data and then applies the filter based on the provider id's. Due to the size of the warehouse table, the workflow is extremely slow.

I looked at the In-Database input option but it didn't recognize my ODBC connections, so i don't think this is configured for this database type.

Is there a way to speed up this type of querying?

Brian

[RodL]

For the In-DB tools, you can check Supported Data Sources in Help.

But if your platform isn't supported for that, and if you know basic SQL querying, you can configure your query from a simple "Select * from ..." to "Select * from ... WHERE ..." in the Input Data tool. Just go to the SQL Editor tab when you make the initial connection.

This will send the query to the database platform and run the entire query on the database server...only the records meeting the query criteria will come into the Alteryx data stream.

[wottel]

thanks RodL,

I was considering the SQL approach but was trying to develop a workflow that could use to a list of provider id's housed as separate input source since that will change frequently.

is there a way to have the SQL update by using an list from an Excel (or any input)?

[wottel]

I stumbled upon the dynamic input tool which i think will get me where i need to go. Looks like i can set it up to replace portions of the where clause allowing me to have a list of Provider Id's stored on an excel that will be passed to the dynamic input.

The initial test produced a much faster run time.

[RodL · Accepted solution]

Yes, you are going down the correct path. 

Just to point out that you can use the IN functionality in SQL with the Dynamic Input tool (with the "Group Replacement Value for SQL IN Clause"), which will send fewer queries to the database server.

[AlteryxIdeasTeam]

This is a test comment from Aha. Adding an update.