Define Additional Indexes
Define additional indexes on columns that are often queried.
The Rules Engine will automatically generate unique indexes on all keys (primary, secondary, etc.) you defined for the various database tables. To further optimize performance, you may define additional indexes on other (combinations of) columns. In particular, adding indexes to foreign key columns may greatly enhance the performance of your application. After defining indexes in the repository, you must have them actually created in the database.
When to use
The main development effort concerning indexes is to determine where additional indexes are improving the performance. In general, the performance of your application may benefit from:
Always manually test if additional indexes are really effective. Use USoft BenchMark index hints as a first step in increasing performance through indexing.
You should particularly consider index hints where no Available Index is listed, the column is a Foreign Key, or there are SQL statements in the corresponding Profile that take a long time to execute.
Index hints cannot be absolute because too much depends on your database, its tuning, your network configuration and workload.
To define additional indexes:
The Indexes window appears.
Blindly following index hints can damage performance. Always confirm that an additional index is actually used, and that it speeds up performance without decreasing data manipulation on the same table to a degree that is unacceptable. For confirmation, perform a comparative test with BenchMark or use the database vendor's diagnostic tools.