Performance tips when working with files and web services

Use server storage when possible

It is usually faster to work with files stored in server storage than remote files or cloud storage.

• Step 1. Create a Flow, which copies/moves files to server storage.

• Step 2. Create a Flow, which works with a file in server storage.

• Step 3. Create a nested Flow, which combines the Flows created in Steps 1 and 2 together.

Use streaming if possible

To enable streaming, go to MAPPING > Parameters and enable Stream Data.

Note that streaming might not work if your source or destination files are complex nested data objects. 

Force streaming if regular streaming is not possible

In many cases, when the source is a file or response from a web service and the destination is a database table, the streaming (and the ability to use bind variables) will automatically be disabled by the engine, even when it is enabled for the specific transformation. For example, if the the source is XML or JSON document, or if the SQL queries used to filter out records in the response from the web service call. 

In Etlworks, it is possible to force streaming, even when it is not allowed, by enabling the option Force Streaming under MAPPING > Parameters. When streaming is forced, the system automatically creates a stream-able view from a non-stream-able source. It still keeps all the records in memory, but does not keep all the SQL statements in memory and it is possible to use bind variables with a stream-able view.

Split large files

If the source file is large, especially if it is an XML file, it makes sense to split it into multiple smaller files, and then process them one by one in a loop. Read more about splitting files.

Use parallel loops when iterating through a large number of identical tasks

Read about parallel loops.

Enable parallel file operations when processing files by a wildcard name

Read about enabling parallel file operations.

Zip multiple files before copying then 

If you can, Zip Files before copying/moving them to remote or cloud file storage.