Using Indexed tables for better data loading

Indexed tables significantly enhance performance and responsiveness in Power Pages by allowing faster querying and data retrieval from Dataverse. Proper indexing ensures efficient handling of large datasets, especially in Entity Lists, Forms, and FetchXML queries.

What is an Indexed Table?

In Dataverse, an indexed table includes one or more indexed columns (fields) that speed up filtering, sorting, and searching operations. These are especially important for:

Entity Lists
Lookup filters
FetchXML-based views
REST API queries

Benefits of Using Indexed Tables

Faster page loads
Efficient data filtering and pagination
Reduced query execution time
Better scalability for large datasets

How to Use Indexed Columns

Identify frequently queried columns
- Example: Name, Status, Created On, Owner
Enable indexing in Dataverse
- Go to Dataverse > Tables > Columns
- Open the column you use for filtering/sorting
- Ensure “Searchable” is enabled (this implicitly indexes the column)
Optimize FetchXML and Views
- Use indexed columns in <filter> and <order> clauses
- Avoid full table scans (e.g., filtering on non-indexed option sets or calculated fields)

Best Practices

Limit result sets
Use top or pagination in FetchXML to avoid pulling all records at once.

Avoid filtering on non-indexed fields
Especially on large tables, this can lead to slow performance.

Review table relationships
Add indexes on lookup columns used in joins or subqueries.

Use managed indexes wisely
Dataverse manages indexes automatically for primary and foreign keys, but custom fields may need manual tuning.

Monitoring Performance

Use Portal Diagnostics or Performance insights in the portal management area.
Monitor SQL execution plans if working with Azure Synapse Link.
Use Application Insights to identify slow queries.

Security Consideration

Table permissions still apply; indexing improves performance but not access control.
Avoid exposing sensitive indexed fields unnecessarily.

Pro Tip

Use Searchable = false for columns not used in filtering/searching to reduce indexing overhead and improve overall Dataverse performance.