Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Input tool time reduce for SQL

8 - Asteroid

Have you tried the In-Database tools?

 

https://help.alteryx.com/2018.2/ToolCategories/In-Database.htm

 

8 - Asteroid
8 - Asteroid
My more work is in input tool.

Thanks and Regards
Rohit Gupta.
Alteryx
Alteryx

When you import data from SQL using the standard input tool, you are essentially downloading the data to your local temp space and then using it from there. This import can take awhile depending on your IT set up and the number of records you are importing.

 

If your dataset is taking awhile to load, I recommend narrowing down the data you're trying to import by specifying specific columns and or/rows rather than a select *.

 

When running the workflow the first time, you can also cache the data so you're not re-querying the data with each subsequent run. To do this, right click on your input tool an select Cache and Run workflow. Information can be found here: https://community.alteryx.com/t5/Engine-Works/Just-take-the-Cache-and-Run-Caching-in-2018-3/ba-p/306...

 

Another option is to use the in-DB tools to import the data and do your processing. They essentially build a SQL query that is then offloaded to SQL for processing. If you want to bring this data back into Designer to use some of the additional functionality since they in-DB tools are more limited, you can use the Data Stream Out tool once you have consolidated your records and columns to a more manageable size. 

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/FAQ-How-Do-the-In-Database-tools-Wo...

 

Hope this helps!

12 - Quasar

select * from is a last resort for me. I rarely do that as you are pulling the entire table into your workflow.

 

It would be more efficient to specify the specific fields you need and add a where clause to select only the rows you need based on filtering the columns.

 

I would agree with @DanielG and @echuong1 that switching to and in-db and stream out may be a better approach. The in-db input tool has a query editor and field selection functionality that would help you build you sql statement.

Alteryx Partner
Alteryx Partner

Hi @rohit782192,

 

Here are a few things that you can do to reduce the time taken to bring in the data using SQL:

 

1) Try to make use of In-DB tools wherever possible. When you use standard input tool it uses your local temp space to load the data hence more time, while In-DB tools essentially build a SQL query that is then offloaded to SQL for processing.

 

2) Instead of doing a select * and bringing in all the data try fetching only those columns which are required for your Workflow.

 

3) Instead of making use of Filter tool after bringing in all the data, try and make use of the "WHERE" clause(if possible) in your SQL to limit the number of records and bring in only the required data.

 

These are some of the steps that would help your process to speed up.

 

I hope I have been of help to you.

 

 

Sapna Gupta
Labels