Managing Indexes

As of Release 2021 R2.1, DataSync allows you to manage the creation, update, and deletion of indexes in the destination table based your extractions. Indexes are automatically created when retrieving source tables. While indexes improve the performance of selections and joins, they take additional space and can slow performance of database inserts, updates, and deletes.

Indexes are only available in extractions of type Synchronization and Consolidation.

Adding Indexes

To create an additional index after adding a table:

  1. On the Extractions page, click the table link in the Tables column for an extraction of type Consolidation or Synchronization.

  2. On the Tables page, click the link in the Index column.

  3. On the Index page, click Add.

  4. In the Add Index dialog box :

    1. Add an index description.

    2. Select the index type.

    3. Specify if the index is unique.

    4. Add fields by clicking the + icon. Refer to the table below for details.

    5. Click Add.

  5. Click Save.

Setting Description

Type (SQL Server only)

 

  • Non Clustered: You can create several Non Clustered indexes.

  • Clustered: There can be only one clustered index per table, because the data rows can be stored in only one order.

  • Clustered Columnstore: This is a type of Columnstore index. A Clustered ColumnStore index cannot have any fields in it. There can be only one Clustered Columnstore index per table.

  • Non Clustered Columnstore: This is a type of Columnstore index. You can create several Non Clustered indexes. A Non Clustered Columnstore index cannot have sorted fields.

Unique

Unique indexes are indexes that help maintain data integrity by ensuring that there are no rows of data in a table with identical key values. To enable this property, click the check box.

Note:  A Columnstore index cannot be set as Unique.
Fields

Identifies the number of fields created in the index. The order of the fields will be the order in the database. Only synchronized fields are available to choose from in the drop-down list. A field cannot be used in an index twice.

  1. Click the row in the Fields column to select a field from the drop-down list.

  2. Set the order of fields by using the drag and drop icon in the first column.

  3. Choose the Sort Order of each field (Ascending or Descending).

Note:   The maximum number of fields that can be added is 32 for a Synchronization and 31 for a Consolidation.
For SQL Server 2014 or less, the maximum number of fields is 16 for a Synchronization and 15 for a Consolidation.
List of fields Provides the list of selected fields.

Building indexes in the destination

  1. Select an extraction of type Consolidation or Synchronization.

  2. For a Consolidation, perform a Validate and Build with the Drop the previously created object /Indexes option selected.

    For a Synchronization:

    1. Perform a Validate and Build with the Drop the previously created object /Indexes option selected.

    2. Perform a Run Extraction Now in Truncate and Load mode.

New indexes will be created and existing indexes will be modified.

Updating Indexes

To update an index:

  1. Click the table link in the Tables column for an extraction of type Consolidation or Synchronization.

  2. Click the link in the Index column.

  3. Click Edit on the toolbar.

  4. Make the required changes.

  5. Click Confirm.

  6. Click Save.

To add a newly-created index or update an existing index without reloading all the data:

  1. Select an extraction of type Consolidation or Synchronization.

  2. Perform a Validate and Build with the Only Add Missing Tables/Fields/Indexes option selected.

New indexes will be created and existing indexes will be modified.

Note  

During the Validate and Build process, you may encounter a timeout error when creating new indexes on an existing table with a lot of data. However, the query is still running in the backend and the updates can be viewed in the Logs page. If you wish to avoid the display of this error, increase the requestTimeout parameter in the web.config file of DataSyncAPI.

Deleting Indexes

To delete an existing index:

  1. Click the table link in the Tables column for an extraction of type Consolidation or Synchronization.

  2. Click the link in the Index column.

  3. Select the index.

  4. Click Delete in the toolbar.

  5. Click Confirm.

  6. Click Save.

To delete an index that was created in a destination database:

  1. Delete it from DataSync as described above.

  2. Run a Validate and Build with the Only Add Missing Tables/Fields/Indexes option selected. Indexes that were deleted in DataSync will be deleted from the destination table.