Data Load - Best Practice

[suhamore]

Hello

We have a SQL query that runs for around 40 mins in the query tool. The query is lengthy one and has lot of joins in it.
When we try the same query in a TI process, the process is hung for a painfully long time when 'Preview' button is hit.

The data load process will be run once in a month.
So, Would it be a good idea to run the query in the query tool, export the query results to a text file and use that text file in TI as a source?

When pulling data from external data sources, how should we decide which way to go, connect data source through ODBC or get the data in text file and use that as data source?

Any guidelines from your experiences?

Thanks in advance!!

[tomok]

There is no best practice as to whether you should use an ODBC source for your data or whether to use flat files. It is all personal preference. As a developer, I like flat files because they are simpler. Most clients prefer to use ODBC, especially if the source is a data warehouse.

There is best practice (at least IMO) concerning queries if you are going to use ODBC. Encapsulate your SQL either into a view or a stored procedure to minimize the size of the string you are using in the TI process. This will save you many headaches going forward.

[lotsaram]

We have a SQL query that runs for around 40 mins in the query tool. The query is lengthy one and has lot of joins in it.
When we try the same query in a TI process, the process is hung for a painfully long time when 'Preview' button is hit.

One "best practice" in this situation is to replace the query in the query box with one that returns a grid with the same number of columns (TI data source variables) of the same type per column (string vs numeric) and then in the prolog set the DataSourceQuery variable to a sting of the query you actually want to use at runtime. Then you don't get the annoying hang when you just want to edit the TI.

For very large queries it is often worthwhile exporting as text via a query tool and using flat files as the data source for TI as often the speed advantage of doing this can be quite significant (but it does add more steps, tools and complication to the overall process.)

[suhamore]

Thanks tomok and lotsaram!!