Lookup performance

This article explains how to configure performant lookups, how Displayed property, Property used for search, Filter operator, and Include total count in suggestions work together, and what kind of client-side requests are generated.

Key idea

In lookup Default search mode, the client filters and sorts suggestions with one effective search property.

For an author, the useful mental model is this:

• most lookups use the same property for display and search
• some lookups use a dedicated SearchProperty that differs from DisplayProperty

In Neos Studio, SearchProperty is initialized from DisplayProperty, or from ValueProperty when no displayed property is configured. So in normal authoring, the common baseline is effectively SearchProperty = DisplayProperty.

The displayed property controls what users see in the input and in suggestions. The effective search property controls how suggestions are filtered and sorted.

For string and localizable string properties, the generated filter predicates and sort expressions use search.normalize(...).

Recommended design

For performant lookups, use these rules:

1. Keep DisplayProperty focused on what users should read.
2. Use SearchProperty only when users must search on something different from what is displayed.
3. Prefer a dedicated search column that concatenates the useful searchable values, for example Code + Label + AdditionalKeywords. For database-side optimization patterns built around computed columns and indexes, see Indexing with computed columns.
4. The best persistence strategy is usually to search on data that is already normalized for search, and mark that column as normalized for search so the generated query can stay compatible with an index.
5. If the business column itself cannot store that normalized form, create a computed column that materializes the normalized search value instead. An indexed computed column for that purpose can improve any lookup mode. On SQL Server, the implementation reaches that normalized-search behavior through a case-insensitive and accent-insensitive collation rather than by physically rewriting the value to uppercase and without accents.
6. In StartsWith mode, make the search column start with the displayed value, or at least with the same prefix users naturally type.
7. Make sure the suggestion content explains why a row matched. When SearchProperty is a dedicated aggregated column, prefer exposing the main business properties that feed that search column in Suggestion properties, rather than exposing the technical search column itself.
8. Keep IncludeCountInSuggestions disabled unless users really need the total number of matches while typing.

Default mode: same property for display and search

This is the common case, and effectively the default authoring experience in Neos Studio.

Example configuration:

• DisplayProperty = Description
• SearchProperty = Description

Users type against Description, suggestions are filtered on Description, and sorting also follows Description.

StartsWith without wildcards

With FilterOperator = StartsWith, the generated request stays simple:

$filter=startswith(search.normalize(description/fr), search.normalize('cho'))
&$orderby=search.normalize(description/fr) asc

This is usually the best fit for large datasets because it aligns well with normalized indexes.

The ideal persistence shape here is a search column that already stores data normalized for search and is marked as normalized for search. That lets the generated request stay on a simple normalized predicate and gives the database the best chance to reuse an index efficiently.

Contains or *value*

In Contains, or when the input explicitly switches to contains through *value*, suggestions that begin with the typed text are ranked first. Then remaining results are sorted by the same normalized property.

Representative generated request:

$filter=contains(search.normalize(description/fr), search.normalize('cho'))
&$orderby=startswith(search.normalize(description/fr),search.normalize('cho')) desc,search.normalize(description/fr) asc

So even in this simple configuration, sorting is not always a plain alphabetical sort on the displayed value: prefix matches are boosted first.

Default mode: dedicated SearchProperty

Use a dedicated SearchProperty when users need to search with data different from what is displayed.

Example:

• DisplayProperty = Description
• SearchProperty = SearchText
• SearchText contains Description + Code + Brand

Users still see Description, but matching and sorting use SearchText.

This is the main behavior change to keep in mind when SearchProperty != DisplayProperty:

• matching uses the dedicated search property
• sorting uses the dedicated search property too
• the client no longer adds the startswith(... ) desc ranking boost
• when SearchProperty is explicitly configured, multi-word input can be split into multiple contains(...) predicates, even without explicit wildcards
• this term-by-term behavior is limited to 2 to 5 terms; beyond that, the client falls back to a single contains(...) predicate on the full text

That last point is intentional for performance: keeping ORDER BY search.normalize(SearchProperty) asc gives the database a better chance to reuse a suitable normalized index, while an extra ranking expression often makes the query harder to optimize.

When the lookup only uses the implicit fallback search property derived from DisplayProperty or ValueProperty, the term-by-term split does not apply. Multi-word input now still switches to a single contains(...) predicate on the full text, but the client does not generate multiple contains(...) and contains(...) clauses unless SearchProperty is explicitly configured. This keeps existing implicit lookups away from the more aggressive term-by-term strategy while still avoiding the historical startswith(...) behavior for multi-word input.

StartsWith on a dedicated search property

Representative generated request:

$filter=startswith(search.normalize(searchText/fr), search.normalize('gran'))
&$orderby=search.normalize(searchText/fr) asc

This is a good fit when the dedicated search column starts with the same prefix users naturally type, ideally the beginning of the displayed value.

Contains on a dedicated search property

Representative generated request:

$filter=contains(search.normalize(searchText/fr), search.normalize('cho'))
&$orderby=search.normalize(searchText/fr) asc

With a dedicated SearchProperty, this simpler sort is usually desirable because it preserves a predictable order on the normalized search column.

The strongest general-purpose optimization is still the same: persist a value normalized for search when possible, or materialize it through a computed column when the business field must remain unchanged. Once that value is indexed, the benefit applies to StartsWith, Contains, and any other lookup mode that filters or sorts on that search property. On SQL Server, that normalized-search behavior can come from the computed column collation rather than from a separately rewritten uppercase accent-free value.

If the input contains 2 to 5 terms and SearchProperty is explicitly configured, the generated filter can search each term separately. This applies both to plain multi-word input such as dark chocolate and to explicit contains input such as *dark chocolate*:

$filter=contains(search.normalize(searchText/fr), search.normalize('dark')) and contains(search.normalize(searchText/fr), search.normalize('chocolate'))
&$orderby=search.normalize(searchText/fr) asc

If the input contains more than 5 terms, the client falls back to a single contains(...) predicate on the full text. This fallback is intentional: it keeps the generated OData query under the complexity limits typically enforced on allowed filter expressions.

$filter=contains(search.normalize(searchText/fr), search.normalize('dark chocolate extra words here again'))
&$orderby=search.normalize(searchText/fr) asc

This is why a dedicated search column should be intentionally designed:

• it changes what matches
• it changes how results are ordered

When that dedicated search column is performance-critical, the most effective persistence strategy is often to materialize the query shape you need through a computed column and index it accordingly. See Indexing with computed columns.

Contains is still more expensive than StartsWith, but it can remain acceptable when:

• the search text is well designed
• matching rows exist quickly
• IncludeCountInSuggestions is disabled
• selective filters and a composite index reduce the scope first

Typical selective filters are properties such as CategoryId, Enabled, or State.

Representative generated request with selective filters:

$filter=categoryId eq 12 and enabled eq true and contains(search.normalize(searchText/fr), search.normalize('dark')) and contains(search.normalize(searchText/fr), search.normalize('chocolate'))
&$orderby=search.normalize(searchText/fr) asc

In this configuration, the filtered scope is smaller before the expensive text predicate is applied.

QuickSearch mode

When the lookup uses QuickSearch mode:

• SearchProperty is ignored
• matching uses the entity view quick search configuration
• the client still sorts suggestions from the displayed property, or from ValueProperty when no displayed property is configured

Representative generated request:

$filter=search.ismatch('dark chocolate')
&$orderby=startswith(search.normalize(description/fr),search.normalize('dark chocolate')) desc,search.normalize(description/fr) asc

In Neos Studio, switching a lookup to QuickSearch clears SearchProperty. Switching back to Default initializes it with DisplayProperty, or with ValueProperty when no displayed property is configured, so the lookup returns to a predictable default behavior.

Advanced lookup dialog

Opening the advanced lookup dialog passes the current lookup input value to the opened search UI view.

This value is available to the lookup UI view loading flow and to reference retrieving rules executed with the lookupUIView trigger.

However, the dialog's default free-text search field is not derived from the lookup SearchProperty.

When the opened search UI view exposes quick search, the dialog free-text field uses that quick search configuration and generates search.ismatch(...) filters.

When the opened search UI view does not expose quick search, users still have the regular filter bar inputs, but there is no separate default free-text search driven by SearchProperty.

IncludeCountInSuggestions

When IncludeCountInSuggestions is enabled, the client asks the server for the total number of matches.

If the server returns more records than the displayed suggestion page, the footer shows a message such as:

10 displayed / 37 found

When IncludeCountInSuggestions is disabled, the client does not ask for the total number of matches. If more rows exist, the footer only shows a limit hint:

Only the first 10 results are shown

This is usually cheaper because the server does not need to compute the full match count for each typed value.

Practical recommendations

Use DisplayProperty for readability, and SearchProperty for intentional search behavior.

When lookup performance matters, favor a column normalized for search that can use an index directly. If the source business value cannot itself be stored in that form, use a computed column to materialize that normalized value and index it. On SQL Server, this remains a normalization for search from a business point of view, even though the generated implementation may use collation rather than a separately materialized uppercase accent-free string.

Choose StartsWith by default for large datasets.

Use Contains only when users really need it, and support it with either:

• a dedicated search text column
• selective filters and a composite index
• both

Keep IncludeCountInSuggestions disabled by default.

Only enable it when the exact total number of matches is useful in the typing experience.

See also

• Indexing with computed columns