We try to ensure finPOWER Connect provides the best possible performance for the majority of its Users. However, it is impossible to pro-actively ensure every combination of usage is best catered for. Therefore, there may be instances where adding custom indexes to finPOWER Connect will further improve performance.
Note, there are always performance trade-offs when adding custom indexes. For instance, creating an index to improve performance in one area could impair performance in another, e.g. when inserting or updating data into the database.
A Client cannot, and should not add, or attempt to add indexes to an Access database. Access databases have a limit on the number of indexes they support, and if we subsequently added another index to a table this might cause an error.
SQL Server Databases
Clients can add indexes to a SQL Server database – if this helps improve performance for their situation. They should be careful adding indexes for the sake of indexes – as they can bloat the database size and sometimes decrease performance.
- DO NOT start the index name with IX_ PK_ PI_ or XX_ – these are reserved for Intersoft use.
- Preferably prefix the index name with “Custom_”, e.g. “Custom_MySupplementalIndex”.
- DO NOT make indexes unique. Obviously, this may cause problems with database queries inserting or updating rows into the database.
If a Client does add an index, the Full Circle team would find it helpful to review the scenario that led to this decision to see if we should standardise such an index.