Custom Database Indexes
Intersoft Systems tries 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.
This blog provides some rules around adding custom indexes.
Access Databases
You 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
The Enterprise Edition of finPOWER Connect supports Microsoft SQL Server. With care, it is possible to create custom indexes in a SQL Server database.
You should be careful adding indexes for the sake of indexes – as they can bloat the database size and decrease performance when inserting and updating data.
finPOWER Connect prefixes indexes it creates with either IX_, PK_, RI_ or XX_ - so treat these as reserved for Intersoft use and DO NOT use.
Instead, prefix your custom index with your own identifier. We suggest you use "Custom_", e.g. "Custom_MySupplementalIndex".
DO NOT make indexes unique.
Obviously, this may cause problems with database queries which insert or update rows into the database and would cause a duplicate index key.