Configuring multiple indexes for fast lookups on multiple fields

In some cases you may need to lookup data using multiple fields in your TracerPlus configuration. If you are using a large dataset this can be a problem since TracerPlus Desktop only supports the selection of a single session index. This can result in slower lookup times for this type of setup.

To work around this limitation you can manually write mutliple indexes using the Custom SQL option within Form Logic.

Step 1

The first thing you will want to do is add a FormInitialize event that triggers a CustomSQL action. This Custom SQL will be used to find the index so that it only builds the index a single time. Set the Action Control ID field to a control on your form so that if the index is found we can use it to determine if we should or should not build the index. An example of this is below:

SELECT INDEX_NAME FROM INFORMATION_SCHEMA.indexes WHERE TABLE_NAME = 'Session2' AND INDEX_NAME = '[*WHATEVER INDEX NAME YOU DECIDE*]'

Where the TABLE_NAME clause is appropriate for the session you are using.

Step 2

The next step is to add another FormInitialize event immediately after the first with a condition so that it only triggers once. The condition should be something like the following:

if ([*CONTROL ID FROM STEP1*] == '')

This will cause the building of the index to only occur if Step 1 returned a blank value. In other words, it did not find the index. Finally, write the Custom SQL that will actually build your multi field index. An example is below:

CREATE INDEX [*WHATEVER NAME YOU DECIDE*]ON Session2 (Field1, Field 2)

This will allow you to build a multi field index for fast lookups that use mutliple fields.