3.3 Advanced Filtering Patterns & Database Performance

3.3 Advanced Filtering Patterns & Database Performance

Intermediate Exam Focus: Intermediate developers must understand how Mendix translates XPath into SQL queries and how retrieve choices impact database load and JVM heap memory. Certification questions heavily test aggregation constraints (count()), avoiding N+1 retrieve anti-patterns in loops, the difference between database and memory-tier filtering, and aligning Domain Model composite indexes with XPath predicates.

Aggregation Constraints: Subquery Mechanics

A common requirement is filtering an entity based on the presence, absence, or quantity of its associated child records. For example, finding all Customer objects that have placed at least one order, or finding Order objects that have zero OrderLine items.

In Mendix XPath, this is achieved using the count() aggregation function inside a predicate:

// Finding customers who have at least one associated order
[count(Sales.Order_Customer) > 0]

// Finding orders with no line items (empty orders)
[count(Sales.OrderLine_Order) = 0]

How count() Translates to SQL

Developers often assume that [count(Association) > 0] causes the database to perform an expensive SELECT COUNT(*) aggregation across the entire related table. In reality, the Mendix Object-Relational Mapping (ORM) engine optimizes count(Association) > 0 and count(Association) != 0 into a highly efficient SQL EXISTS semi-join:

-- Generated SQL for [count(Sales.Order_Customer) > 0]
SELECT c.id, c.name 
FROM sales$customer c
WHERE EXISTS (
    SELECT 1 
    FROM sales$order o 
    WHERE o.sales$customerid = c.id
);

Because the database engine stops scanning as soon as the first matching child record is encountered, EXISTS runs in logarithmic time on indexed foreign keys.

Conversely, checking [count(Association) = 0] compiles into a NOT EXISTS subquery, which efficiently finds parent records that have no foreign key matches in the child table.

Scoped Predicates Inside Aggregations

Mendix allows nesting a secondary predicate inside the count() function to count only child records matching specific criteria:

// Retrieve customers who have at least one 'Urgent' high-priority ticket
[count(Support.Ticket_Customer[Priority = 'Urgent' and Status != 'Resolved']) > 0]

This generates an EXISTS clause with the nested filter incorporated into the subquery's WHERE clause:

WHERE EXISTS (
    SELECT 1 FROM support$ticket t 
    WHERE t.customerid = c.id 
      AND t.priority = 'Urgent' 
      AND t.status != 'Resolved'
);

Database Retrieve vs. In-Memory Retrieve: Architectural Trade-Offs

In Mendix microflows, data can be retrieved from two distinct sources:

1. From Database: Issues an SQL query to the underlying relational database, returning only records matching the XPath constraint.
2. By Association (In Memory): Traverses references from an object already resident in the Mendix Runtime JVM heap.

Furthermore, once a list is in memory, developers can manipulate it using microflow List Operations (Filter, Find, Sort, Aggregate List). Choosing the wrong tier for filtering is the number one cause of production performance degradation.

Comparative Architectural Analysis

The "Retrieve All and Filter in Memory" Anti-Pattern

A frequent anti-pattern observed on the exam is retrieving an entire table of 200,000 records from the database without an XPath constraint, and subsequently using a microflow Filter List activity to find matching items.

• Why this fails: The Mendix Runtime must allocate JVM memory for 200,000 Java objects, serializing all attributes across the network wire. This introduces massive CPU latency, network saturation, and inevitable garbage collection pauses or heap crashes.
• The Correct Pattern: Always push the filtering logic into the Database Retrieve XPath constraint so that only the necessary rows are returned from the database server.

The N+1 Query Problem in Microflows

The N+1 retrieve problem occurs when a microflow retrieves a list of $N$ parent objects, loops over them, and executes a database retrieve inside the loop for each individual parent.

Consider an example where a microflow retrieves 500 Order records and then, inside a loop, issues a database retrieve for the Customer of each order:

1. Query 1: SELECT * FROM sales$order (returns 500 rows)
2. Queries 2 through 501: SELECT * FROM sales$customer WHERE id = ? (executed 500 times in sequence)

This results in 501 separate roundtrips over the JDBC connection, turning what should be a 50-millisecond task into a multi-second bottleneck.

Eliminating the N+1 Pattern

1. Retrieve by Association: If the associated objects are already loaded or accessible via association traversal, use Retrieve by Association, which leverages the Mendix Runtime object cache.
2. Set-Based XPath Retrieves: Instead of querying inside the loop, perform a single set-based retrieve before the loop using an XPath constraint with an association predicate, or restructure the logic to process records in bulk.

Index Alignment and the Leftmost Prefix Rule

Creating indexes on entities in the Mendix Domain Model allows database engines to execute lookups in logarithmic O(log n) time rather than linear O(n) full table scans. However, an index is only utilized if the XPath query matches the structure of that index.

Single vs. Composite Indexes

• Single-Attribute Index: Created on a single attribute (e.g., OrderNumber). Optimized for exact matches ([OrderNumber = 'ORD-100']) and prefix searches ([starts-with(OrderNumber, 'ORD-')]).
• Composite Index: An index comprising two or more attributes in a defined sequence, e.g., (Region, Status, OrderDate).

The Leftmost Prefix Rule

A composite index can only be leveraged if the query filters include the leading (leftmost) attributes in the index definition:

Given a composite index on (Region, Status, OrderDate):

• [Region = 'West'] -> USES INDEX (matches leftmost attribute)
• [Region = 'West' and Status = 'Active'] -> USES INDEX (matches leftmost two attributes)
• [Region = 'West' and Status = 'Active' and OrderDate >= ...] -> USES INDEX (matches all three attributes)
• [Status = 'Active'] -> CANNOT USE INDEX (skips Region, leading attribute missing; forces table scan)
• [Status = 'Active' and OrderDate >= ...] -> CANNOT USE INDEX (leading attribute missing)

Exam questions test your ability to diagnose why an XPath query is running slowly despite an index existing on the entity—the answer is frequently that the query omits the leftmost attribute of a composite index!

Pagination, Limits, and Offsets

When displaying records in data widgets or processing large datasets in scheduled events, retrieving all records at once causes latency and memory spikes.

Microflow Retrieve Limits and Offsets

In the Microflow Database Retrieve action dialog, the Amount property allows three configurations:

• All: Retrieves all matching rows (default).
• First: Translates to SQL SELECT TOP 1 / LIMIT 1. Avoids scanning the rest of the table.
• Custom (Limit & Offset): Specifies an offset and a maximum number of records to retrieve.

Keyset Pagination for High-Volume Batch Processing

When processing millions of records in a scheduled background task, standard offset pagination (LIMIT 1000 OFFSET 50000) degrades in performance because the database must still read and discard the first 50,000 rows. Instead, use Keyset Pagination (also known as the seek method) using the entity ID or AutoNumber:

// Keyset pagination: retrieve next batch based on last processed ID
[Id > $LastProcessedId]

By sorting by Id ASC and setting Limit = 1000, each batch lookup executes as an instant index seek, regardless of whether you are on the first thousand or the ten-millionth record.