Improve Import Set performance with the coalesce index feature in Fuji

[nisha_narayanak]

Best practices for Import Set performance strongly recommends the usage of indexed fields as coalesce fields. In an import, coalescing on a field (or set of fields) means the field will be used as a unique key. If a match is found using the coalesce field, the existing record will be updated with the information being imported. If a match is not found, then a new record will be inserted into the database. If the indexes are missing, it can lead to poor performance issues especially if large amount of data processing is involved. Starting with Fuji release, Administrators have the option to create indexes on coalesce fields from the user interface.

Example of how to create indexes on coalesce fields from the UI:

Let's say you have an admin that is creating a transform map and you identify a coalesce field(s).

As soon as a coalesce setting is changed, users will be prompted with a message saying "Coalesce settings changed. After all coalesce fields are configured, use the Index Coalesce Fields related link to check if a new database index is required."   Hop down to the related links and click on "Index Coalesce Fields."

Clicking on "Index coalesce fields" will take us to the "create a new index" screen where the admin can confirm the index and schedule it for creation in the system. Here, you can generate a new index that will continue to work while the index is being run. When the index is finished, you will receive a confirmation email. Just click "Ok" to continue.

Trying to coalesce on non-indexed fields could cause performance issues. To avoid performance issues on the database, you should coalesce on a field that is unique and already indexed. Index creation can be confirmed from "Tables and Columns view by going to System Definition > Tables & Columns and looking for the blue "i" icon.

What if the coalesce fields are already indexed?

If at least one of the fields is fixed, no additional indexes are required. You will be notified that the fields are already indexed.

This feature can be used for existing and new transform maps. It can be initiated any time to create indexes on coalesce fields. If there are no coalesce fields defined in the transform map, no message about index creation will be displayed. Existing transform maps will not have indexes created automatically, administrators need to click the link to make use of the feature. If an admin edits an existing transform map with a coalesce field, following message will be displayed to prompt the index creation.

This feature will help administrators to proactively create indexes before running into import set related performance issues. There are other ways to create indexes on Fuji instances. Please check out the latest blog post by Bill Tang where he shares best practices and tips on Improving your ServiceNow instance performance by creating database indexes via the User Interface.