This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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 *.
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.
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.
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.