FlexibleSearch Mastery: From Syntax to Query Plans

FlexibleSearch is SQL wearing the type system as an exoskeleton: you write against types and attributes, the platform translates to tables and columns, and everything good and bad about your queries flows from understanding that translation. Most performance folklore ("FlexibleSearch is slow") is actually untranslated ignorance of what the generated SQL does. This guide covers the language properly, the runtime behaviors around it (session context, restrictions, caching), and the tuning workflow that ends in evidence instead of guesses.

The Core Contract#

SELECT {p.pk}, {p.code}, {p.name[en]:o}
FROM {Product AS p JOIN CatalogVersion AS cv ON {p.catalogVersion} = {cv.pk}}
WHERE {cv.version} = ?version
  AND {p.modifiedtime} >= ?since
ORDER BY {p.code}

The braces are the type system boundary. {Product AS p} resolves to the deployment table from items.xml (which is why the type system guide's deployment decisions echo here: types sharing a table via subtyping query together; types with their own deployment do not). {p.name[en]} reaches into the localized-property table with a join you did not write; the :o marks an outer join so rows without an English name still return. Every one of those implicit joins is real SQL cost, visible in the hAC console's translated-SQL view, which should be muscle memory: write FlexibleSearch, read the SQL it becomes.

Parameters (?since) are not cosmetic. They feed prepared statements and they define the query-cache key. Concatenating values into the query string defeats both, and is the single most common self-inflicted wound in generated "dynamic" queries: a thousand distinct query strings that are semantically one query, each compiled, each cached separately, each missing.

Joins, Subqueries, and the Shapes That Work#

• Relation traversal: many-to-one attributes join directly ({p.catalogVersion}); many-to-many relations join through the link type ({Category2ProductRelation AS rel}), and knowing the relation's generated type name (from items.xml) beats guessing.
• Subqueries are first-class:

SELECT {pk} FROM {Product}
WHERE {pk} NOT IN ({{ SELECT {source} FROM {StockLevelProductRelation} }})

The double-brace block is a nested FlexibleSearch with its own type resolution. Use it for existence checks and set exclusions; prefer joins when you need the columns.

• UNION across {{ }} blocks works and occasionally saves a query that polymorphism cannot express, but each block pays its own translation; three unioned blocks scanning the same table usually wanted to be one query with an IN.
• What does not belong in FlexibleSearch: window functions, vendor-specific hints, CTEs. The escape hatch is the FlexibleSearch native SQL support or a properly reviewed JDBC template in a DAO, used sparingly and never for writes (writes bypass the cache invalidation machinery and corrupt the coherence the persistence guide describes; this rule has no exceptions).

Session Context: The Invisible WHERE Clause#

Two session mechanisms rewrite your query behind your back, by design:

1. Catalog version filtering: queries on catalog-aware types are filtered to the session's active catalog versions unless you disable it. A DAO that "randomly" returns nothing in a cronjob usually has no catalog versions in its worker session. Be explicit in service code:

final FlexibleSearchQuery fsq = new FlexibleSearchQuery(QUERY, params);
fsq.setDisableCaching(false);
// cronjob context: state your catalog versions instead of inheriting none
catalogVersionService.setSessionCatalogVersions(List.of(onlineVersion));

1. Search restrictions (the type-level filters powering frontend visibility, multi-country assortment splits, and the personalization rules from the content catalog guide) apply to any session user who is not admin. The debugging consequence: a query returns different results in hAC as admin than in the storefront as anonymous, and neither is "wrong". sessionService.executeInLocalView with a controlled user, or temporarily disabling restrictions in a local view, is how service code opts out deliberately, loudly, and narrowly.

Both mechanisms are also cache-key inputs: the same text under different catalog versions or users caches separately, which is correct and also means "one hot query" can be a hundred cache entries. Keep visibility-context surface small in hot paths.

The Query Cache Contract#

The region cache's query region stores result PK lists keyed by query text plus parameters plus context. What you control:

• Cacheability: volatile values in the query text (now(), random ordering, per-request timestamps as literals) make every execution unique. Push volatility into parameters where it belongs, and round time parameters to a sensible grain (a ?since at second precision is a cache-buster; at five-minute grain it is a hit).
• Invalidation blast radius: writes to a type invalidate query-region entries touching that type. A query joining five types is invalidated by writes to any of them; hot queries earn narrow FROM clauses.
• DAO discipline: the platform's DefaultGenericDao and friends are fine for simple lookups; hand-written queries belong in named DAO methods with the query text as a constant (one string, parameterized), not assembled per call. This is also what makes the JDBC log readable when you hunt.

Pagination and Bulk Reads#

count and start on FlexibleSearchQuery paginate, but offset pagination degrades linearly with depth on every database; page 4,000 of a full-catalog export is a table scan wearing a friendly API. For bulk processing (feeds, migrations, the maintenance jobs from the data cleanup guide), use keyset pagination on an indexed, monotonic column:

SELECT {pk}, {code} FROM {Product}
WHERE {pk} > ?lastPk
ORDER BY {pk}

with count as your batch size and the last PK as the cursor. It is restart-safe (pairs with the resumable-job pattern from the clustering guide), constant-cost per page, and index-friendly. The same shape works on modifiedtime plus PK for delta feeds, provided the composite index exists, which brings us to tuning.

The Tuning Workflow#

1. Name the query. JDBC logging (hAC) or Dynatrace database hotspots give you the SQL and its frequency. Frequency times cost is your priority order; a 40 ms query at 200/s outranks a 900 ms nightly.
2. Read the translated SQL and diff it against your mental model: surprise joins (localized attributes in a WHERE), OR-chains the optimizer hates, functions wrapping indexed columns (LOWER({code}) forfeits the index; store normalized data or use a computed index instead).
3. Get the plan from the database with production-shaped data (the local dev guide's snapshot habit pays off here). The platform generates indexes declared in items.xml; everything else is yours to add, deliberately, through an items.xml index declaration so environments stay consistent:

<indexes>
  <index name="prodModTimeIdx">
    <key attribute="modifiedtime"/>
    <key attribute="catalogVersion"/>
  </index>
</indexes>

1. Fix at the right layer: missing index (add it), unbounded result (paginate), cache-hostile text (parameterize), visibility context explosion (narrow it), or genuinely relational-hostile access pattern (that one belongs in Solr; the search architecture guide draws the browse-versus-lookup line, and FlexibleSearch powering category browse pages is the classic architectural misuse no index will save).
2. Re-measure and record. Query time, cache hit ratio, DB load; before and after; in the PR description. The performance guide's law: no tuning claim without numbers.

Habits of Teams Whose Queries Behave#

• Query text lives in constants; parameters always; string assembly never.
• Every DAO method states its context assumptions (catalog versions, restrictions on or off) instead of inheriting whatever the caller had.
• The translated SQL of any new hot-path query gets read once by a human before merge.
• Bulk reads are keyset-paginated and resumable; offset pagination is a code-review flag past the first thousand rows.
• Indexes are declared in items.xml, never hand-applied to one environment.
• hAC console literacy (translation view, execution times, restriction awareness) is onboarding material, not tribal knowledge.

FlexibleSearch rewards exactly the mindset SQL rewards, plus one addition: always remember there is a type system between you and the tables, and make it visible (translate, read, plan) whenever a query matters. The language never was the bottleneck; unread SQL was.