Relationship Performance Optimization

Beginner

Identify and fix the most common relationship-related performance problems: slow portals, unindexed match fields, and calculation fields used as relationship keys.

What you'll learn

How to verify that relationship match fields are indexed
Why calculation fields in relationships are slow
How to use Sort and constrain portal settings appropriately
How to measure relationship resolution time with the Data Viewer

Relationships that feel instant in a small database become painfully slow at 100,000 records. The culprits are almost always the same: match fields that are not indexed, calculation fields in relationship predicates, portals configured to show too many rows, or relationship chains so deep that FM must traverse multiple hops to resolve a value.

1/4

Verifying match field indexing

Both sides of a relationship key must be indexed for the relationship to be fast. In FM, go to File -> Manage -> Database -> Fields, select each match field, click Options, and check the Storage tab.

TEXT

// Indexing requirements:
// Relationship predicate:  Contacts::AccountID = Accounts::AccountID
// Contacts::AccountID     -> must have indexing ON (All or Minimal)
// Accounts::AccountID     -> must have indexing ON

// Fields that CANNOT be indexed (never use as match fields if performance matters):
// - Global fields (# gField) -- global fields have only one value, so indexing is irrelevant
// - Unstored calculation fields -- computed per record, no index possible
// - Container fields

// To check: File -> Manage -> Database -> select field -> Options -> Storage -> Indexing

Table Occurrence Naming Conventions in Depth Avoiding and Resolving Circular Relationships