When working with big datasets within a model-driven app, it can be a very frustrating thing, the performance on lookup columns when the dataset is big. Examples of these could be Accounts, Contacts, Products, or other cases, of course, depending on the business you are operating in.
When I receive questions like these, I would first check what the view is that is related to the column on the form, potentially this can be updated so it perhaps only navigates on:
- Active records
- Recent records
- My records
- My business Unit
But of course, there are plenty of scenarios where you can’t specify that even further, and the datasets remain big to choose from, and therefore, the performance on lookup columns remains poor.
Now, within the Power Platform Admin settings in the Behavior area, there are two potential settings that could help you increase the performance.
| Settings | Description | Default value |
|---|---|---|
| Minimum number of characters to trigger typeahead search | Enter the minimum number of characters that trigger an automatic typeahead search in a lookup field in model-driven apps. This setting can be used to improve lookup performance, particularly when the underlying environment database has huge amounts of data. | Field is empty by default. |
| Delay between character inputs that trigger a search | Enter the delay interval in milliseconds that is introduced in lookups before a search is executed. This can be used to improve lookup performance, particularly when the underlying environment database has huge amounts of data. | 250 ms |
Link to official and full Microsoft documentation: Manage behavior settings – Power Platform | Microsoft Learn
With the default behavior where the minimum number of characters is set to 0 (empty) and the delay to 250 ms you will see in the recording below that after every character that is typed in the search is being triggered.
When updating the setting of a minimum number of characters to be 3 characters, the search will only be triggered after 3 characters are entered, which you will see in the recording below.
Finding guidelines on when to change these settings, or perhaps considering this, I was not able to find this myself. If you do have guidelines, either found yourself or perhaps based on your own experience, I would love to hear your thoughts on this.
The one thing that I found a bit disappointing, and you will experience this yourself when you want to configure this, is that this behavior is a setting for the whole environment. Therefore, you can’t change this behavior per lookup on the form, as you, for example, can do when you want to disable the recent items for a lookup
Configure performance on lookup column
In order to tweak the settings on lookup columns, you need to do the following:
- Open the Power Platform Admin Center
- Navigate to Environments
- Select the environment where you want to enable this for
- Select Settings
- Navigate to Behavior in the Products section
- Find the Lookup section
- Tweak the following options to your needs:
- Minimum number of characters to trigger typeahead search
- Delay between character inputs that will trigger a search
- Press Save
Hi, interesting post – thanks. When I used to train on D365 Customisation I always advised that adding a lookup to the Quick Find view improved the performance as a SQL index would then be added. Is that still the case – I assume it is?
Hi Peter thank you very much for your reply! You could be very well right with that, so definitely something that I will need to assess. Indexes are important to be set right and they will definitely help once set correctly. The answer is probably in the middle, that all puzzle pieces can help or contribute to a better performance.