About Indexes

3DSpace stores each attribute and “basic” property of an object in a separate row in the database, allowing flexible and dynamic business modeling capabilities. Basic properties include type, name, revision, description, owner, locker, policy, creation date, modification date, and current state.

This has the side effect of requiring extra SQL calls (joins) when performing complex queries and expands that specify multiple attribute or basic values. To improve performance of these operations, you can define an index. In fact, test cases show marked improvement on many types of queries and expands when an index is in place. Queries and expands choose the best index to perform the operation. If two indexes are equally qualified to perform the operation, the first one found will be used.

If a commonly executed query uses multiple attributes and basic properties as search criteria, consider defining an index. Once enabled, searches involving the indexed items generate SQL that references the index table instead of the old tables, eliminating the need for a join. A query that does not use the first specified item in an index will not use that index. Therefore, when creating an index, specify the most commonly used item first.

When an index is created or items are added or removed from it, the index is disabled by default. The new database tables are created and populated when the index is enabled. This step can be time-consuming; however, it is assumed that an index will be enabled by a system administrator once when created or modified, and will remain enabled for normal system operation.

For expands, 3DSpace looks for and uses an index that is associated with relationships; that is, an index that has a relationship attribute as the first item in it.