Handling Large Datasets - trillium_quality - 2.7

Trillium Quality for Dynamics Guide

Product type
Software
Portfolio
Verify
Product family
Trillium
Product
Trillium > Trillium Quality
Version
2.7
Language
English
Product name
Trillium Quality
Title
Trillium Quality for Dynamics Guide
Topic type
Administration
Overview
How Do I
Configuration
Reference
Installation
First publish date
2008

When processing a large dataset with the Standard Batch Manager, you may wish to increase processing speed. Processing speed can be increased in several ways:

  • Creating custom queries (FetchXML). Large batch jobs should be segmented into multiple, smaller jobs for processing. For example, you could create batch jobs that process records from a single city, postal code, or country. To segment batch jobs, create custom queries that identify specific records to be processed in each job, as described in Creating a Custom Query.

  • Increasing resources on a cloud instance. Users running Trillium Quality for Dynamics online should contact Technical Support or their Customer Success Manager to discuss increasing system resources (CPU cores and memory) on their cloud instance before running a large standard batch job.

  • Creating an index on key fields. Creating an index on the window keys optimizes matching performance. The following procedure outlines the process of creating index keys; consult with your database administrator about creating indexes.
    Note: Performing a CRM update can modify certain database structures. After CRM updates, delete and re-add the indexes if necessary.

To create an index on key fields

  1. Log on to the system as a user with sufficient privileges to create indexes on the CRM database(s) and start SQL Server Management Studio.

  2. Select the database for your organization and click OK. The name will be in the format OrgName_MSCRM.

  3. Expand Tables and select the table named ContactBase.

  4. Expand ContactBase and open the Indexes folder to see what other indexes are already on the table.

  5. Right-click on Indexes and select New Index.

  6. For Index Type, select Nonclustered.

  7. Click Add 

  8. Select the field tss_windowkey and click OK. The Index name auto populates, but it is recommended to change the name to something meaningful, for example, ndx_tss_windowkey.

  9. Click OK to create the index.

  10. If you are using the default matching configuration, repeat Step 8 and Step 9 for the following fields:

    tss_dr_bus_phone

    tss_dr_home_phone

    tss_dr_mobile_phone

    tss_dr_email

  11. If you modified the Trillium Quality for Dynamics configuration file to include additional fields in the match criteria, create an index on each additional window key field:

    tss_dr_str_field1

    tss_dr_str_field2

    tss_dr_num_field1

    tss_dr_num_field2

    tss_dr_date_key1

  12. If you are using a custom Fetch XML query, create an index on fields that are used in the query. For example, if your query processes records based on city, create an index on the City field.

  13. Repeat Step 3 through Step 12 for the AccountBase, LeadBase, and CustomerAddressBase tables.