Performance Factors to Consider

The factors that you must take into consideration when you deal with any performance problems that arise include:

• Designing your SIFT indexes optimally.

• Supporting too many SIFT indexes will affect performance.

• Having unnecessary date fields in the SIFT indexes of the base table will affect performance because they create three times as many entries as an ordinary field.

• Supporting too many fields in the SIFT indexes will also affect performance.

• The fields in the SIFT index that are used most regularly in queries must be positioned to the left in the SIFT index. Usually, the field that contains the greatest number of unique values must be placed on the left, with the field that contains the second greatest number of unique values on the right and so on. Integer fields generally contain the greatest number of unique values and Option fields contain a relatively small number of values.

Consider the costs and the benefits of maintaining SIFT indexes.

You can prevent the SIFT indexes from being updated by setting the MaintainSIFTIndex property of the index in the base table to No. This means that you no longer benefit from SIFT's ability to calculate sums quickly. However, the SIFT functionality is still available.

• If the base table does not grow or only grows slowly, there is no need to set the MaintainSIFTIndex property of any indexes that contain SumIndexFields to Yes. If the base table does grow, you should set the MaintainSIFTIndex property of any indexes that contain SumIndexFields to Yes.

Important
It is important that you remember to perform tests every time you make any changes to the SIFT structures. You must ensure that the changes that you have made do not cause problems in any other areas of the application. You must also ensure that your changes do not have a negative affect on performance.

Note
If you set the MaintainSIFTIndex property to No, you should not set the MaintainSQLIndex to No.