IBM Support

Importing data from CSV or TXT file takes a very long time (poor performance when importing from GL export file)

Troubleshooting


Problem

User clicks 'Transfer - External Data - Import from Flat Files'. User chooses 'import specification' and selects 'client' as import file provider. User browses to data file (CSV / TXT file) and clicks 'run' button. This takes a very long time (for example 30 minutes for a CSV file that is less than 200kb in size).

Symptom

A small import (for example which should take only about 1 minute) may take a long time (for example 30+ minutes).

Cause

There are other possible causes for slow performance when importing data.

  • TIP: For more examples, see separate IBM Technote #1686200.

This IBM Technote specifically relates to the scenario where the cause is that the customer has forgotten to untick the box 'Track Data' (after using it during development/testing phase).
  • It is recommended that you only use 'track data' when designing/testing an import specification
  • When the system is in production, please remember to untick 'Track Data'.

More Information (from the Controller help file):
    Track Data - Select this option to use the External Data Tracking log reports available in the 'Log Reports' window:

    If selected, you will have the choice to either exclude (or include) transformation information.

    Include Transformation Information - Select this option if you want to include the transformation information when using the Track Data option. We recommend using this option only when designing and testing the import specification, and using the server preference TRACKDATAROWS. You select TRACKDATAROWS on the Maintain/Configuration/General - Server Preferences tab. TRACKDATAROWS should be limited to a range of 30 000 rows. Note that when this option is selected, performance will be reduced.

Diagnosing The Problem

There is no abnormal CPU load on either the client, application server or SQL server.

  • Therefore, the root cause will not be obvious.

Resolving The Problem

Disable ('untick') the 'track data' box for all the import specifications that have been built/finished/tested.

Steps:
1. Click 'Transfer - External Data - Define Import Specifications
2. Select the import specification (for example 'ABC1')
3. Open the 'Properties' section
4. Scroll down the list to find the 'Options' section
5. Untick the box 'Track Data'

6. Click 'Save'
7. Repeat for all the 'production' import specifications
8. Test.

[{"Product":{"code":"SS9S6B","label":"IBM Cognos Controller"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Controller","Platform":[{"code":"PF033","label":"Windows"}],"Version":"10.2.0;10.1.1;10.1;8.5.1;8.5;8.4;8.3","Edition":"Not Applicable","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
15 June 2018

UID

swg21376909

Page Feedback