Dataverse Week Day 6: Dataverse for Performance - Choices, Indexing & Delegation Tips

Delegation Limits and How to Work with Them 

When building apps with Dataverse and Power Apps, one of the most critical performance concepts to understand is delegation. Delegation refers to the platform’s ability to execute data queries server-side rather than pulling entire datasets into the client for processing. This is the difference between an app that remains responsive with millions of rows versus one that becomes sluggish with just a few thousand. 

By default, when a query cannot be delegated, only a limited number of rows are retrieved from the server. In canvas apps, this limit is 500 records (configurable up to 2000). Anything beyond that is silently ignored, which introduces the risk of incomplete data and logic errors—especially when filtering or sorting. 

To maximize performance, developers need to design queries using only functions and patterns that Dataverse can delegate. Functions like Filter, Sort, StartsWith, and LookUp are typically safe, while others like Mid, Len, Left, and complex If or Or conditions often are not. When in doubt, Power Apps will usually warn you with a blue underline and a delegation notice. 

A powerful tactic during development is to set the delegation row limit to just one. This makes non-delegable queries fail visibly, allowing you to catch problems early. Splitting complex queries into smaller, sequential operations can also help maintain delegation. For advanced scenarios, it’s often better to offload logic to Dataverse by creating server-side views, which ensure consistent performance across clients. 

For lookup-heavy forms, avoid binding large datasets directly to dropdowns or combo boxes. Instead, enable search-as-you-type filtering, preload smaller subsets of data, or implement cascading filters to reduce the data volume passed to the client. These choices help preserve responsiveness even in apps used globally or across large enterprises. 

Using Choice Fields and Global Option Sets Smartly 

When structuring data models in Dataverse, developers often face the choice between using local choice fields and global option sets. While both support predefined lists of values, their long-term impact on performance and maintainability is quite different. 

Local choice fields are confined to the table in which they're created. This works well for isolated use cases but becomes a maintenance burden when the same set of values—like Status, Department, or Priority—appears in multiple tables. Each instance of a local choice field becomes a separate object to manage, update, and synchronize. 

Global option sets solve this problem by providing reusable lists of values that can be referenced across multiple tables. This design not only simplifies updates—one change applies everywhere—but also improves the consistency of data across your system. For apps that scale beyond a prototype, global sets are almost always the better option. 

Beyond maintainability, global option sets contribute to a more structured schema, which benefits reporting, API usage, and integration scenarios. External systems accessing your data via connectors or APIs can rely on standardized values, reducing data mismatches or transformation requirements. 

While global option sets should be the default choice for shared categories, local fields are still appropriate for one-off, static fields that are unlikely to be reused or changed. The key is in strategic planning: understanding the scope of your data model and anticipating reuse early in the design process. 

For larger teams or environments with multiple makers, using global option sets also enforces a standard vocabulary. This creates consistency not only in the data itself but in the user experience across different apps in your ecosystem. 

Indexing and API Call Reduction Strategies 

Another foundational element of Dataverse performance is how you structure your tables and manage data retrieval. One of the most overlooked tools for performance tuning is indexing. While primary keys and lookups are automatically indexed, developers have the option to create alternate keys on any text or number field that needs to support quick lookups. 

An alternate key is a field or combination of fields that enforces uniqueness and enables fast querying. For example, if you're constantly searching records by a unique Employee ID, defining that column as an alternate key ensures the backend creates a SQL index, drastically reducing lookup times. 

When working with large datasets or executing many queries, indexed fields can mean the difference between sub-second and multi-second response times. But indexing isn’t free—it comes with a small write overhead and should be applied strategically. Only index fields that are queried often and return a narrow result set. 

Beyond indexing, reducing the number of API calls is crucial in environments with usage caps or where user experience is a priority. Apps often make multiple redundant calls to Dataverse—either to validate data, update records, or retrieve lookup information. These calls add up quickly and can throttle performance or hit service limits. 

Batch operations are an effective solution. Functions like Patch can be used to send multiple updates in a single request. Similarly, using ForAll to loop through records should be done with caution—preferably by staging data in collections and only syncing differences with Dataverse. 

Caching plays a significant role too. Storing static or semi-static datasets in a local collection when the app loads can prevent repeated server queries. This is especially important for dropdowns, configuration tables, and lookup data that doesn’t change often. Apps can further benefit from using context variables to avoid querying the same value repeatedly during a session. 

Lastly, consider moving logic into server-side Dataverse components like views, calculated columns, or Power Automate flows. This not only offloads processing from the client but centralizes business logic, improving maintainability and consistency across multiple apps. 

Source: Dataverse Week Day 6: Dataverse for Performance - Choices, Indexing & Delegation Tips