Troubleshooting
Problem
Importing a flat file (CSV, TXT or Excel) is slow in IBM i2 iBase, or slowing after starting. This makes importing data into iBase take too long, sometimes taking hours.
Cause
Indexes are not correctly defined. Therefore, for each new line in the file to import the database spends too much time to verify if the data is already in the database.
Environment
SQL Server
Diagnosing The Problem
Run an SQL Trace during the import.
Analyse the trace to understand where bottlenecks might exist.
Resolving The Problem
If an Entity or Link type has 2 or more fields that are used as Discriminators, go in SQL Server Managment Studio (SSMS) and add a composite Index that is made of those fields.
Example :
The iBase database has a Communication link type.
This link type has 3 fields :
- Date (indexed, mandatory, discriminator)
- Heure (indexed, mandatory, discriminator)
By default in SSMS, there are 3 indexes :
Right-click on Indexes, and select New index.
Click on the Add button.
Select the 2 column names Date_ and Heure_. Then, click on OK.
Give a name to the new Index. It is recommended to use the prefix "IX_" for the index name.
Click on OK to finish the creation of this Index.
Restart the import of the data in iBase, and check if it's faster.
If the import is still slow, an analysis of the SQL Trace would then be necessary.
Adding a composite Index is particularly useful when there are many records with the same value for one of the indexed fields. For example, a problem might arise in the case above if hundred or thousands of records have the same Date value or if lots of people have the same last name. In such a case, create a composite Index with "First name + Last name + DoB" and avoid the problem.
WARNING : Do not modify the existing indexes. You must create a new index.
Was this topic helpful?
Document Information
Modified date:
21 June 2018
UID
swg21645758