SAP Commerce Architect Series — Part 3

Learning how to write FlexibleSearch queries is easy. Learning how to design queries that remain understandable, secure, and efficient as enterprise data grows is where the real engineering begins.

Introduction

If you’ve worked with SAP Commerce for even a short time, you’ve probably written a query like this:

			
SELECT {p.pk}
FROM {Product AS p}
WHERE {p.code} = ?productCode

At first glance, FlexibleSearch feels simple.

You identify the ItemType.

You select the attributes you need.

You add a condition.

You execute the query.

And you get the result.

That’s enough for many development tasks.

But enterprise applications introduce another layer of complexity.

What happens when:

the Product table contains millions of records,
the same product code appears in multiple catalog versions,
the query joins several relation tables,
Search Restrictions modify what the current user can see,
the UI asks for thousands of records,
the query behaves differently in production than in development?

At that point, FlexibleSearch stops being only a query language.

It becomes a data-access design problem.

And that’s what we’re going to explore in this part of the SAP Commerce Architect Series.

We’re not going to memorize every FlexibleSearch syntax option.

Instead, we’ll learn how to think about a query before writing it.

We’ll start with something simple.

Then we’ll gradually introduce catalog context, relations, B2B data, pagination, Search Restrictions, indexes, and production performance.

The goal is not simply:

“Can I write a FlexibleSearch query?”

The better question is:

“Can I design a query that returns the right data, in the right context, and continues to behave well as the application grows?”

Let’s work through that together.

1. Start With the Business Question

Before we write FlexibleSearch, let’s start with the requirement.

Imagine the business says:

“Find a product using its product code.”

That sounds straightforward.

We might write:

			
SELECT {p.pk}
FROM {Product AS p}
WHERE {p.code} = ?productCode

At a basic level, the query makes sense.

But I want you to pause here.

Is product code enough to identify the correct product?

In many SAP Commerce implementations, Product is associated with a CatalogVersion.

So the same business code may exist in different catalog versions.

For example:

			
Product Code: ABC-100
Catalog A
 ├── Staged
 └── Online
Catalog B
 ├── Staged
 └── Online

		

Now the original requirement becomes more precise:

“Find product ABC-100 in the Online version of Catalog A.”

That’s already a better business question.

💡 Design Insight

Before writing a query, make sure the requirement contains enough information to identify the correct business record.

Many query problems are actually requirement-definition problems.

A technically valid query can still return the wrong business record.

2. What FlexibleSearch Is Actually Doing

Before going deeper, let’s build a simple mental model.

When your application executes FlexibleSearch, the flow is conceptually:

			
Application Code
      │
      ▼
FlexibleSearch Query
      │
      ▼
SAP Commerce Type System
      │
      ▼
Generated Database SQL
      │
      ▼
Database
      │
      ▼
Result / PKs
      │
      ▼
SAP Commerce Models

		

FlexibleSearch lets us work with SAP Commerce type-system concepts instead of writing database-specific SQL directly.

For example:

			
SELECT {p.pk}
FROM {Product AS p}

Product refers to the SAP Commerce ItemType.

The platform resolves the underlying table and attribute mappings.

That abstraction is useful.

But the database still has to execute a real query underneath.

That’s an important point.

FlexibleSearch does not remove database-performance concerns.

It simply gives us a type-system-aware way to express our data requirements.

3. Why Do We Usually Select the PK?

If you’re new to FlexibleSearch, you may wonder why you see this so often:

SELECT {p.pk}

instead of:

SELECT *

In normal SQL development, returning multiple columns may feel natural.

But SAP Commerce works heavily with models.

When you need ProductModel objects, the PK is usually enough for the platform to identify the item and construct the model representation.

For example:

			
final FlexibleSearchQuery query =
        new FlexibleSearchQuery(
                "SELECT {p.pk} FROM {Product AS p} WHERE {p.code}=?code");
query.addQueryParameter("code", productCode);
final SearchResult<ProductModel> result =
        flexibleSearchService.search(query);

		

The query is focused on identifying the matching Product items.

Let’s Challenge the Query

Suppose someone writes:

			
SELECT {p.pk}, {p.code}, {p.name}, {p.modifiedtime}
FROM {Product AS p}

Is that automatically wrong?

No.

Sometimes you genuinely need a custom result set rather than full model objects.

But you should retrieve additional columns intentionally.

The principle is simple:

Retrieve what the application needs—not everything the database happens to contain.

💡 Design Insight

Don’t treat FlexibleSearch like:

“Give me the whole row and I’ll decide later.”

Start with:

“What does this use case actually need?”

That mindset becomes increasingly important as result sets grow.

4. Always Prefer Query Parameters Over String Concatenation

Let’s look at two implementations.

Approach 1

			
final String query =
        "SELECT {p.pk} FROM {Product AS p} WHERE {p.code} = '" + productCode + "'";

Approach 2

			
final String query =
        "SELECT {p.pk} FROM {Product AS p} WHERE {p.code} = ?productCode";
final FlexibleSearchQuery flexibleSearchQuery =
        new FlexibleSearchQuery(query);
flexibleSearchQuery.addQueryParameter("productCode", productCode);

		

The second approach is much better.

Why?

Because parameterized queries provide:

clearer code,
safer value handling,
better separation between query structure and input data,
less risk of malformed query construction,
more maintainable query logic.

This becomes even more important when there are several input parameters.

⚠️ Common Mistake

Avoid building FlexibleSearch by concatenating user or business input directly into the query string.

Even when the input comes from a trusted source, parameterized queries are cleaner and safer.

5. The Product Query Is More Complicated Than It Looks

Let’s return to our original requirement.

We need a Product using:

			
productCode
catalogId
catalogVersion

Conceptually:

			
Product
   │
   ▼
CatalogVersion
   │
   ▼
Catalog

		

One possible query might look like:

			
SELECT {p.pk}
FROM {
    Product AS p
    JOIN CatalogVersion AS cv
        ON {p.catalogVersion} = {cv.pk}
    JOIN Catalog AS c
        ON {cv.catalog} = {c.pk}
}
WHERE {p.code} = ?productCode
  AND {c.id} = ?catalogId
  AND {cv.version} = ?catalogVersion

		

Now notice what happened.

We didn’t begin by thinking:

“I need two JOIN statements.”

We began with:

“What business data uniquely identifies the product I want?”

Then we followed the model relationships.

That’s a much better way to design queries.

Business Relationship → Model → Query

Try to build this habit:

			
Business Requirement
       │
       ▼
Business Objects
       │
       ▼
Model Relationships
       │
       ▼
FlexibleSearch

		

Instead of:

			
Requirement
     │
     ▼
Start writing JOINs
     │
     ▼
Hope it works

		

💡 Design Insight

A good FlexibleSearch query usually begins with a good understanding of the data model.

If you don’t understand the relationships between ItemTypes, you’ll often end up with unnecessary joins, duplicate results, or incorrect filters.

6. Joins: More Isn’t Better

Let’s say we have:

			
Product
   │
   ▼
CatalogVersion
   │
   ▼
Catalog

		

If the requirement only needs the Product and we already have a CatalogVersionModel, we may not need to join Catalog at all.

For example:

			
SELECT {p.pk}
FROM {Product AS p}
WHERE {p.code} = ?productCode
  AND {p.catalogVersion} = ?catalogVersion

with:

query.addQueryParameter("catalogVersion", catalogVersionModel);

This may be much simpler than joining additional types solely to re-identify a model we already have.

That’s an important lesson.

✅ Best Practice

Use existing model references as parameters when appropriate.

If the application already has the correct CatalogVersionModel, don’t automatically re-query Catalog and CatalogVersion again.

Every join should exist for a reason.

7. Relations Can Make Queries Interesting

Now let’s introduce a B2B scenario.

Suppose the business says:

“Show products that are explicitly available to this customer organization.”

The model might look like:

			
B2BUnit
   │
   ▼
CustomerProductAssignment
   │
   ▼
Product

		

The assignment contains:

			
customerUnit
product
active
effectiveDate

A query might conceptually be:

			
SELECT {p.pk}
FROM {
    Product AS p
    JOIN CustomerProductAssignment AS a
        ON {a.product} = {p.pk}
}
WHERE {a.customerUnit} = ?customerUnit
  AND {a.active} = ?active

		

This looks manageable.

Now imagine we hadn’t created CustomerProductAssignment.

Instead, the data was distributed across several unrelated collections.

The query could become much more difficult.

This is why Part 2 and Part 3 are closely related.

💡 Design Insight

If a simple business question consistently requires extremely complicated queries, don’t automatically assume FlexibleSearch is the problem.

Sometimes the query is exposing complexity in the underlying data model.

Good modeling usually leads to clearer queries.

8. When a Join Can Produce Duplicate Results

Let’s look at another common issue.

Suppose:

			
Product
   │
   └── Multiple Assignments

A Product may have multiple matching assignments.

If we write:

			
SELECT {p.pk}
FROM {
    Product AS p
    JOIN CustomerProductAssignment AS a
        ON {a.product} = {p.pk}
}
WHERE {a.customerUnit} = ?customerUnit

		

the database may return the same product multiple times if several assignments satisfy the criteria.

A developer might respond with:

SELECT DISTINCT {p.pk}

Sometimes that’s appropriate.

But don’t use DISTINCT automatically.

First understand why duplicates exist.

Ask:

Is the relationship truly one-to-many?
Should multiple assignments exist?
Are our conditions incomplete?
Is the query joining more records than intended?

DISTINCT can hide a modeling or query problem.

⚠️ Common Mistake

Don’t treat DISTINCT as a universal duplicate-removal switch.

Understand the cardinality first.

Then decide whether duplicate elimination is genuinely part of the business requirement.

9. `EXISTS` Can Sometimes Express the Requirement Better

Let’s slightly change the requirement.

Find Products that have at least one active assignment for this B2BUnit.

Notice the phrase:

“at least one”

We’re not interested in returning assignment data.

We only care whether a matching assignment exists.

That can sometimes be expressed naturally using EXISTS.

Conceptually:

			
SELECT {p.pk}
FROM {Product AS p}
WHERE EXISTS (
    {{
        SELECT {a.pk}
        FROM {CustomerProductAssignment AS a}
        WHERE {a.product} = {p.pk}
          AND {a.customerUnit} = ?customerUnit
          AND {a.active} = ?active
    }}
)

		

The exact best-performing form depends on the database, indexes, data distribution, and generated SQL.

So I don’t want you to remember:

“EXISTS is faster than JOIN.”

That’s too simplistic.

Instead remember:

Use query structures that express the business condition clearly, then validate performance using actual data and execution plans.

💡 Design Insight

Choose between JOIN, EXISTS, and other patterns based on what you’re actually asking the database.

If the requirement is:

“Return matching relation records”

a join may make sense.

If the requirement is:

“Return the parent when at least one matching child exists”

EXISTS may express the intent more clearly.

10. Search Restrictions: The Same Query Can Return Different Results

This is one of the most important FlexibleSearch topics in SAP Commerce.

Imagine this scenario.

You run a query in HAC using an administrative account.

Result:

25 records

The storefront executes what appears to be the same query.

Result:

7 records

The first reaction is often:

“Something is wrong with the DAO.”

Maybe.

But maybe the DAO is working perfectly.

The difference could be Search Restrictions.

Let’s Understand the Situation

SAP Commerce can apply restrictions based on the current user and context.

Conceptually:

			
Your FlexibleSearch
       │
       ▼
Search Restriction Context
       │
       ▼
Effective Query
       │
       ▼
Database

		

This means the query you wrote isn’t always the full picture.

The current session, user, catalog context, and restrictions can affect the effective result.

Example

Suppose a customer is allowed to access only products associated with their business unit.

An administrator may see:

			
Product A
Product B
Product C
Product D

The customer may see:

			
Product A
Product C

That might be exactly what the security model intends.

🏢 Enterprise Insight

When FlexibleSearch behaves differently between HAC and the application, investigate execution context, not just query text.

Ask:

Which user is running the query?
Are Search Restrictions enabled?
Which catalog versions are active?
Is the same session context being used?
Are there additional service-layer filters?

This saves a lot of debugging time.

11. Don’t Disable Search Restrictions Just to Make a Query Work

There are legitimate administrative and background-processing scenarios where restrictions may need to be controlled.

But a dangerous pattern is:

“The query doesn’t return enough records, so let’s disable restrictions.”

That may solve the technical symptom while creating a security problem.

Before bypassing restrictions, understand:

why the restriction exists,
who owns the data,
which execution context should legitimately access it,
whether the background operation should run under a different user or context.

⚠️ Common Mistake

Never treat Search Restrictions as an annoying query filter.

They’re often part of the application’s authorization model.

Removing them without understanding the business intent can expose data the current user shouldn’t access.

12. Pagination: “Return Everything” Is Rarely a Good Requirement

Let’s imagine another requirement:

Show all orders for this customer.

The customer has 75 orders.

No problem.

Six months later:

Customer A → 12,000 orders

Now what does “show all” really mean?

Does the UI display 12,000 records at once?

Probably not.

The interface likely displays something like:

			
Page 1 → Orders 1–50
Page 2 → Orders 51–100
...

The data-access layer should reflect that.

FlexibleSearch Pagination

You can limit the result set using the query’s start and count.

For example:

			
final FlexibleSearchQuery query = new FlexibleSearchQuery(FIND_ORDERS);
query.addQueryParameter("user", customer);
query.setStart(start);
query.setCount(pageSize);
final SearchResult<OrderModel> result =
        flexibleSearchService.search(query);

		

The exact pagination approach may depend on your service architecture and framework utilities, but the principle remains the same.

Don’t retrieve thousands of records just to display a small subset.

Stable Sorting Matters

Pagination without a predictable sort can create strange results.

For example:

ORDER BY {o.creationtime} DESC

In some cases, you may also consider an additional deterministic field when several rows can share the same timestamp.

The broader principle is:

Pagination should have a stable ordering strategy.

💡 Design Insight

Pagination is not only a UI concern.

It’s a data-access concern.

Whenever the expected result set can grow significantly, design pagination early.

13. Don’t Query Inside a Loop

Let’s look at a pattern that appears often.

			
for (ProductModel product : products)
{
    findAssignments(product);
}

If there are 1,000 products, this may execute 1,000 additional queries.

This is often called the N+1 query problem.

The code looks simple.

The database workload does not.

Conceptually:

			
1 query → products
+
1000 queries → assignments

versus:

			
1 well-designed query
or
small number of batched queries

⚠️ Common Mistake

Always be suspicious when DAO or service calls appear inside loops over large collections.

Ask:

Can we retrieve the required data in fewer queries?

That doesn’t mean one giant join is always the answer.

The right solution depends on the data and use case.

But the potential N+1 problem should always be considered.

14. Indexes: Don’t Start With “Add an Index”

Suppose this query becomes slow:

			
SELECT {c.pk}
FROM {CustomerCertificate AS c}
WHERE {c.customer} = ?customer
  AND {c.status} = ?status
ORDER BY {c.expiryDate}

		

A common reaction is:

“Let’s add an index.”

Maybe that’s correct.

But let’s not jump ahead.

First understand the access pattern.

Ask:

How many records are in the table?
How many belong to this customer?
How selective is status?
Is expiryDate used for filtering or only sorting?
Which indexes already exist?
Does the database use the expected index?
Is another join creating the real cost?

Only then should we decide whether an index change is appropriate.

Example Thinking

Suppose:

10 million certificate records

and:

			
customer = very selective
status = not very selective

An index involving customer may be useful.

But if nearly every query filters by both:

customer + status

then a composite index may deserve evaluation.

This is database-design territory.

The important thing is to connect the index to actual query patterns.

✅ Best Practice

Don’t add indexes because an attribute “looks searchable.”

Design indexes around high-value, frequent access paths—and validate their effect.

Indexes improve reads but also have costs:

storage,
insert/update overhead,
maintenance,
migration impact.

More indexes are not automatically better.

15. A Query That Works in Development Can Fail at Enterprise Scale

Let’s walk through a realistic scenario.

A developer creates a query.

Local environment:

40 ms

QA:

90 ms

Production:

4.8 seconds

Same code.

Why?

Because production isn’t QA with more users.

Production usually has different:

row counts,
data distributions,
index statistics,
user contexts,
concurrent workloads,
relationship cardinalities,
history depth.

How I Would Investigate It

Instead of immediately rewriting the FlexibleSearch, I’d work systematically.

Step 1 — Confirm the Actual Query

Make sure the expected FlexibleSearch is really being executed.

Sometimes the slow path isn’t the query you think it is.

Step 2 — Check Result Size

How many records are being returned?

			
Development: 20
Production: 45,000

The query may be behaving exactly as requested.

The requirement itself may need pagination or tighter filtering.

Step 3 — Check Data Volume

How many records exist in each table?

For example:

			
Product: 5 million
Assignment: 80 million
B2BUnit: 500,000

A join involving those tables deserves careful attention.

Step 4 — Check Cardinality

Does one Product have:

			
2 assignments?
20?
2,000?

Cardinality can completely change how a join behaves.

Step 5 — Check Search Restrictions

Is production executing the query under a different user/context?

Are restrictions introducing additional filtering?

Step 6 — Check Sorting

Sorting a tiny dataset is cheap.

Sorting millions of candidate records may not be.

Step 7 — Review Indexes

Do useful indexes exist for:

join columns,
high-selectivity filters,
common access paths?

Step 8 — Inspect Generated SQL and Database Execution Plan

This is where you stop guessing.

The execution plan can help answer questions like:

Is the database scanning a huge table?
Is an index actually being used?
Is the join order expensive?
Is sorting consuming significant time?
Are estimated and actual row counts very different?

🏢 Enterprise Insight

Performance tuning should be evidence-driven.

Don’t optimize a FlexibleSearch query based only on how the string looks.

Understand what the database is actually doing.

16. Query Complexity Often Reflects Data-Model Complexity

Here’s an interesting situation.

The business asks:

“Find all products available to this customer.”

You start writing the query.

Soon you have:

			
Product
   ↓
Category
   ↓
Contract
   ↓
Account
   ↓
Sales Area
   ↓
B2BUnit
   ↓
Customer

		

Seven joins.

Nested subqueries.

Several conditions.

At some point, it’s worth asking:

Is this query complicated because the business is genuinely complicated, or because our data model makes a simple concept difficult to express?

That question is uncomfortable—but valuable.

Sometimes the query is correct.

Sometimes a denormalized lookup, dedicated assignment model, search index, or other architecture may better serve the access pattern.

Not every read requirement should be solved by adding more FlexibleSearch.

💡 Design Insight

FlexibleSearch is a tool.

It is not automatically the correct solution for every high-volume retrieval problem.

For transactional data access, it’s extremely useful.

For large-scale search/discovery problems, other platform capabilities—such as Solr—may be a better fit.

Architecture is choosing the right tool for the workload.

17. FlexibleSearch vs Solr: Know the Difference

This is worth clarifying.

Suppose the requirement is:

Find one customer’s orders.

FlexibleSearch is a natural fit.

Now suppose the requirement is:

Search millions of products by keywords, categories, brand, price, color, and facets.

Trying to solve that entirely with FlexibleSearch is probably the wrong direction.

That is the type of problem Solr is designed to handle.

Conceptually:

			
Transactional Retrieval
        │
        ▼
FlexibleSearch

versus:

			
Search / Discovery / Faceting
        │
        ▼
Solr

There is overlap, but the workloads are different.

✅ Best Practice

Before optimizing a very complicated FlexibleSearch query, ask:

Should this use case be using FlexibleSearch at all?

Sometimes the biggest performance improvement is choosing a more appropriate architecture.

18. FlexibleSearch Belongs in the Right Layer

Let’s talk briefly about code organization.

A clean application flow typically looks something like:

			
Controller
    │
    ▼
Facade
    │
    ▼
Service
    │
    ▼
DAO
    │
    ▼
FlexibleSearchService
    │
    ▼
Database

		

Not every project follows this exact naming convention, but the separation of responsibilities matters.

The controller shouldn’t normally contain large FlexibleSearch query strings.

The facade shouldn’t become a data-access layer.

Query logic belongs in an appropriate DAO/repository-style component.

Example DAO

			
public List<CustomerCertificateModel> findCertificatesForCustomer(
        final CustomerModel customer)
{
    final FlexibleSearchQuery query =
            new FlexibleSearchQuery(FIND_CERTIFICATES_FOR_CUSTOMER);
    query.addQueryParameter("customer", customer);
    return flexibleSearchService
            .<CustomerCertificateModel>search(query)
            .getResult();
}

		

The service then coordinates business behavior.

This makes the data-access logic easier to test, understand, and evolve.

💡 Design Insight

Where a query lives matters almost as much as the query itself.

Keep business orchestration and persistence concerns separated.

19. Be Careful With `getResult()` on Large Queries

This looks harmless:

searchResult.getResult();

But ask:

How large can that list become?

If the answer is:

Potentially 100,000+

you should reconsider the access pattern.

Large result sets create impact beyond the database:

			
Database
   ↓
Network / JDBC
   ↓
Application Memory
   ↓
Model Conversion
   ↓
Business Processing

		

Even a database query that executes relatively quickly may still create application-level pressure if it returns huge amounts of data.

🏢 Enterprise Insight

Query performance isn’t only measured by SQL execution time.

Consider the entire path:

database → application → model layer → business processing

Large result sets can become expensive anywhere along that path.

20. Be Intentional About Sorting

Consider:

ORDER BY {p.name}

That seems harmless.

But questions matter.

Is name localized?

How many candidate records must be sorted?

Is the sorting happening after large joins?

Does the UI genuinely need database sorting by this field?

Sorting can become expensive on large datasets.

This doesn’t mean avoid ORDER BY.

It means:

Use sorting because the business requires it, and understand the cost when the dataset is large.

21. Query Only the Data You Need

Imagine a scheduled job processes expired records.

Requirement:

Find certificate records whose expiry date is before today.

Don’t retrieve every certificate and filter them in Java:

			
for (CustomerCertificateModel certificate : allCertificates)
{
    if (certificate.getExpiryDate().before(today))
    {
        ...
    }
}

		

Push appropriate filtering to the data layer:

			
SELECT {c.pk}
FROM {CustomerCertificate AS c}
WHERE {c.expiryDate} < ?today

The database exists to perform filtering efficiently.

⚠️ Common Mistake

Avoid this pattern:

Fetch everything → filter in Java

when the condition can reasonably be expressed in the query.

Reduce the dataset as early as possible.

22. Another Common Problem: Unbounded CronJob Queries

Imagine a CronJob processes records with:

status = PENDING

Initially:

500 records

Easy.

One day, an integration outage creates:

800,000 PENDING records

The next CronJob run tries to load all of them.

Now you have a production incident.

A better design may process records in batches.

Conceptually:

			
Find 500 pending records
        ↓
Process
        ↓
Commit
        ↓
Next 500

		

Depending on the use case, there may be additional considerations around ordering, retries, concurrency, and status transitions.

🏢 Enterprise Insight

Every background query should answer:

What happens if the backlog becomes 1,000 times larger than normal?

Enterprise systems must be designed for abnormal conditions, not only healthy-day behavior.

23. FlexibleSearch Query Review: A Practical Example

Let’s put several concepts together.

Suppose someone submits:

			
SELECT DISTINCT {p.pk}
FROM {
    Product AS p
    JOIN CustomerProductAssignment AS a
        ON {a.product} = {p.pk}
    JOIN B2BUnit AS u
        ON {a.customerUnit} = {u.pk}
}
WHERE {u.uid} = ?unitId
  AND {a.active} = 1
ORDER BY {p.name}

		

Instead of immediately approving it, let’s review it.

Question 1

Do we already have B2BUnitModel?

If yes, could we avoid the B2BUnit join?

Potentially:

WHERE {a.customerUnit} = ?unit

Question 2

Why DISTINCT?

Are there genuinely multiple valid assignments per Product?

Or is the join producing duplicates because the conditions are incomplete?

Question 3

Why order by Product name?

Does the use case require it?

Is the result paginated?

Question 4

How large is the assignment table?

Thousands?

Millions?

Hundreds of millions?

Question 5

Which indexes support:

			
customerUnit
product
active

Question 6

Are Search Restrictions also applied to Product?

This is what a useful code review looks like.

Not:

“Query compiles. Approved.”

But:

“Does this query represent the business correctly and scale with the expected access pattern?”

24. Common FlexibleSearch Mistakes

Let’s summarize the patterns worth watching.

Mistake 1 — Concatenating Query Parameters

Use named query parameters instead.

Mistake 2 — Joining Types You Already Have

If you already have the referenced model, consider passing it directly as a parameter.

Mistake 3 — Using `DISTINCT` Without Understanding Duplicates

Understand cardinality first.

Mistake 4 — Returning Huge Result Sets

Use pagination or batch processing.

Mistake 5 — Querying Inside Loops

Watch for N+1 query patterns.

Mistake 6 — Ignoring Search Restrictions

HAC results do not automatically represent storefront results.

Mistake 7 — Filtering Everything in Java

Let the database filter data where appropriate.

Mistake 8 — Adding Indexes Without Investigation

Use evidence and access patterns.

Mistake 9 — Treating QA Performance as Production Proof

Production data scale changes everything.

Mistake 10 — Using FlexibleSearch for Every Search Problem

Sometimes Solr or another architecture is more appropriate.

25. A FlexibleSearch Review Checklist

Before finalizing an important query, I like to think through questions like these.

Business

What exact business question does this query answer?
Are all required dimensions included?
Could the query return an ambiguous record?

Data Model

Which ItemTypes are involved?
What is the cardinality between them?
Are all joins necessary?
Could duplicates occur?

Parameters

Are values passed as query parameters?
Can an existing model reference remove a join?

Security

Which user executes the query?
Are Search Restrictions active?
Is catalog/session context relevant?

Volume

How many records can match?
Is pagination required?
Could a backlog become extremely large?

Performance

Which filters are selective?
Which indexes support the query?
Is sorting necessary?
Are large joins involved?

Application

How many models will be materialized?
Is the query executed inside a loop?
Is this a transactional retrieval use case or actually a search/discovery use case?

Troubleshooting

Have we inspected generated SQL?
Have we reviewed the database execution plan?
Is production data distribution different from test environments?

You won’t need every question for every query.

The goal is developing the habit of asking them when the query matters.

26. Interview Perspective: Think Through the Scenario

Instead of memorizing:

“What is FlexibleSearch?”

I prefer scenario-based questions.

They reveal whether someone actually understands the platform.

Scenario 1

A FlexibleSearch query returns 100 products in HAC but only 20 products when executed through the storefront.

What would you investigate?

A strong answer should consider:

Search Restrictions,
current user,
active catalog versions,
session context,
different query parameters,
additional service-layer filtering.

Scenario 2

A query works in QA but takes several seconds in production.

What would you check before rewriting it?

Look for:

production data volume,
result count,
cardinality,
indexes,
Search Restrictions,
sorting,
generated SQL,
execution plan.

Scenario 3

A loop over 2,000 Products invokes a DAO query for each Product.

What problem do you see?

This should immediately raise concern about:

N+1 queries

and whether data can be retrieved in batches or with a better access pattern.

Scenario 4

A requirement says:

Find Products that have at least one active assignment for a customer organization.

Would you automatically use a JOIN?

Not necessarily.

A strong engineer should consider what the query actually needs and whether a JOIN, EXISTS, or another pattern expresses it more clearly.

Then test using realistic data.

Scenario 5

A developer adds an index because a FlexibleSearch query is slow.

Is that enough?

No.

You first need to understand:

query structure,
data volume,
selectivity,
existing indexes,
execution plan,
write/update trade-offs.

☕ Before You Move On

Think about the last FlexibleSearch query you wrote.

Don’t look at the syntax.

Instead, ask:

Did I understand the business relationship first?

Was every join necessary?

How large can the result become?

Did I consider Search Restrictions?

Would this still work well with 100 times more data?

If it’s slow in production, do I know how I would investigate it?

If those questions become part of your normal development process, you’re already moving beyond query writing into data-access engineering.

Final Thoughts

FlexibleSearch is one of the most important tools in SAP Commerce.

But learning FlexibleSearch is not about memorizing syntax.

The syntax is the easy part.

The difficult part is understanding:

the data model,
relationship cardinality,
execution context,
access patterns,
database behavior,
production scale.

A query is not good simply because it returns the correct data in your local environment.

A good enterprise query should return:

the correct data,

for the correct user/context,

with a predictable access pattern,

and with a design that remains manageable as the dataset grows.

That’s the mindset I want you to take from this article.

The next time you open HAC or start writing a DAO query, don’t begin with:

“What’s the FlexibleSearch syntax?”

Start with:

“What business question am I asking the data?”

Then understand the model.

Then understand the scale.

Then write the query.

That’s a much more reliable path.

Frequently Asked Questions

Is FlexibleSearch the same as SQL?

No.

FlexibleSearch is SAP Commerce’s type-system-aware query language.

It refers to ItemTypes and attributes and is translated into database-level SQL.

Should I always select only the PK?

When retrieving SAP Commerce models, selecting the PK is commonly appropriate.

There are legitimate use cases for selecting custom columns or scalar values, so choose based on what the application actually needs.

Are joins bad in FlexibleSearch?

No.

Joins are normal and often necessary.

The goal is to avoid unnecessary joins and understand the cardinality and performance impact of the relationships being joined.

Is `EXISTS` always faster than a JOIN?

No.

Performance depends on factors such as database engine, indexes, data distribution, query structure, and execution plan.

Choose the structure that expresses the requirement well, then measure.

Why does my query return different results in HAC and storefront?

One important possibility is Search Restrictions or execution context.

Also compare:

active user,
catalog versions,
session state,
query parameters,
additional business filters.

Should I disable Search Restrictions for background jobs?

Not automatically.

Some background or administrative processes legitimately require a different restriction context, but that should be an intentional security decision.

Understand why the restriction exists before bypassing it.

Should all queries be paginated?

No.

If the expected result is naturally small—for example, one configuration record—pagination provides little value.

But result sets that can grow significantly should be designed with pagination or batching in mind.

When should I use Solr instead of FlexibleSearch?

FlexibleSearch is well suited for transactional/domain retrieval.

Solr is generally better suited for search-oriented workloads such as:

keywords,
facets,
large product discovery,
relevance,
filtered search experiences.

Use the tool that best matches the workload.

🎯 Key Takeaways

If you remember only a few lessons from Part 3, remember these:

1. Start with the business question, not the query syntax.

2. Understand the data model before writing joins.

3. Retrieve only the data the use case actually needs.

4. Use query parameters instead of string concatenation.

5. Don’t add joins simply because they’re easy to write.

6. Understand cardinality before using DISTINCT.

7. Search Restrictions can change query results based on execution context.

8. Design pagination and batching before datasets become huge.

9. Watch for N+1 queries and DAO calls inside loops.

10. Don’t add indexes blindly—investigate the access pattern and execution plan.

11. Development performance does not guarantee production performance.

12. FlexibleSearch is not always the right tool for search/discovery workloads.

13. Query performance includes both database execution and application-side model processing.

14. Use production evidence—not guesses—to tune queries.

15. A good FlexibleSearch query should remain understandable as the business and dataset grow.

What’s Coming in Part 4?

In the next article of the SAP Commerce Architect Series, we’ll explore one of the most useful—and frequently misused—parts of the ServiceLayer:

SAP Commerce Interceptors Explained: Model Lifecycle, Validation, and Design Decisions

We’ll work through:

InitDefaultsInterceptor
PrepareInterceptor
ValidateInterceptor
LoadInterceptor
RemoveInterceptor
when interceptors run,
where business validation belongs,
performance concerns,
interceptor ordering and side effects,
common anti-patterns,
and when not to use an interceptor.

And just like this article, we won’t stop at:

“What does each interceptor do?”

We’ll focus on:

“Why would I choose an interceptor for this requirement—and when should I choose something else?”

Part of the SAP Commerce Architect Series

Practical SAP Commerce learning focused not only on how the platform works, but on how to think through enterprise design decisions.

Learn. Architect. Build Better.

Introduction

1. Start With the Business Question

💡 Design Insight

2. What FlexibleSearch Is Actually Doing

3. Why Do We Usually Select the PK?

Let’s Challenge the Query

💡 Design Insight

4. Always Prefer Query Parameters Over String Concatenation

Approach 1

Approach 2

⚠️ Common Mistake

5. The Product Query Is More Complicated Than It Looks

Business Relationship → Model → Query

💡 Design Insight

6. Joins: More Isn’t Better

✅ Best Practice

7. Relations Can Make Queries Interesting

💡 Design Insight

8. When a Join Can Produce Duplicate Results

⚠️ Common Mistake

9. EXISTS Can Sometimes Express the Requirement Better

💡 Design Insight

10. Search Restrictions: The Same Query Can Return Different Results

Let’s Understand the Situation

Example

🏢 Enterprise Insight

11. Don’t Disable Search Restrictions Just to Make a Query Work

⚠️ Common Mistake

12. Pagination: “Return Everything” Is Rarely a Good Requirement

FlexibleSearch Pagination

Stable Sorting Matters

💡 Design Insight

13. Don’t Query Inside a Loop

⚠️ Common Mistake

14. Indexes: Don’t Start With “Add an Index”

Example Thinking

✅ Best Practice

15. A Query That Works in Development Can Fail at Enterprise Scale

How I Would Investigate It

Step 1 — Confirm the Actual Query

Step 2 — Check Result Size

Step 3 — Check Data Volume

Step 4 — Check Cardinality

Step 5 — Check Search Restrictions

Step 6 — Check Sorting

Step 7 — Review Indexes

Step 8 — Inspect Generated SQL and Database Execution Plan

🏢 Enterprise Insight

16. Query Complexity Often Reflects Data-Model Complexity

💡 Design Insight

17. FlexibleSearch vs Solr: Know the Difference

✅ Best Practice

18. FlexibleSearch Belongs in the Right Layer

Example DAO

💡 Design Insight

19. Be Careful With getResult() on Large Queries

🏢 Enterprise Insight

20. Be Intentional About Sorting

21. Query Only the Data You Need

⚠️ Common Mistake

22. Another Common Problem: Unbounded CronJob Queries

🏢 Enterprise Insight

23. FlexibleSearch Query Review: A Practical Example

Question 1

Question 2

Question 3

Question 4

Question 5

Question 6

24. Common FlexibleSearch Mistakes

Mistake 1 — Concatenating Query Parameters

Mistake 2 — Joining Types You Already Have

Mistake 3 — Using DISTINCT Without Understanding Duplicates

Mistake 4 — Returning Huge Result Sets

Mistake 5 — Querying Inside Loops

Mistake 6 — Ignoring Search Restrictions

Mistake 7 — Filtering Everything in Java

Mistake 8 — Adding Indexes Without Investigation

Mistake 9 — Treating QA Performance as Production Proof

Mistake 10 — Using FlexibleSearch for Every Search Problem

9. `EXISTS` Can Sometimes Express the Requirement Better

19. Be Careful With `getResult()` on Large Queries

Mistake 3 — Using `DISTINCT` Without Understanding Duplicates

Is `EXISTS` always faster than a JOIN?