IBM i2 iBase : How to improve loading performance

Resolving The Problem

Use Compound Indexes

Lack of Compound Indexes in iBase

When setting up discriminators in iBase Designer, iBase will create an individual index for each field. When loading data into SQL Server or querying constraints on multiple fields, the optimizer will choose one of these and use that. Sometimes this may work fine if the index chose is highly selective in terms of what is returned. However, more likely you will see sub optimal performance as a hit in index will necessitate a seek on the table data which may have to process many rows to find matches for both discriminators.

How iBase Creates an Index for a Field

Although iBase cannot create a compound index that contains multiple fields, in fact all iBase indexes created against fields are compound as they include:-

- the column for the field in question

- the Unique_Id column (iBase identifier) which holds the iBase identifier for the item

- the Record_Status column which is a flag indicating whether an item has been deleted

For example, looking at the index on the Number field on the telephone entity in the User_Guide database in SQL Management Studio shows:

Apply the Same Pattern to Compound Index

When building a compound index, we want to apply the same patter to the new index. As an example for the User Guide database, we will create a compound index on the Account Number and Sort Code fields for the Account entity.

Note that the ordering of the columns in the index is very important. The fields you are indexing are always first, with the most selective (most number of unique values) first. This is then followed by the Unique_Id and finally the Record_Status field.

In the case of Account discriminators, the more selective field is Account Number, so, our index will be:

The index will be Nonclustered and should have the Unique checkbox unchecked as show.

WARNING : Do NOT remove the other existing indexes ! !

Other tips for Performance Improvement

- Set the SQL Server recovery model to Simple or Bulk Logged. It can be switched to Full when the data is loaded if necessary.

- Do not use Append Only Text Fields

- Ideally collocate Bulk Insert folder, the iBase scheduler and the database on the same server, using local drives.

- Consider using Microsoft's SSIS (or any other ETL software) to load into a staging database and then load from there into iBase. This is much more complex but can in result in a faster, more robust system.

- If loading from database, consider using SELECT DISTINCT to remove duplicates prior to loading

- Consider turning off check for matching entities. Only do this if:-

o It is the initial load

o You are sure your load contains no duplicates