Large XML catalog with per-item database lookups in ESQL
An integration team processes supplier catalogs as a single 3 MB XML message containing up to 80,000 Item elements. A Compute node called BuildCatalog loops over InputRoot.XMLNSC.Catalog.Item[] and, for each item, issues PASSTHRU SELECT price FROM PRICES WHERE SKU = ? and writes a new element under OutputRoot.XMLNSC.Catalog.Item[]. The PRICES table has 400,000 rows and is refreshed hourly. The integration server has a 1.5 GB maxHeap and four vCPUs. Steady load is 40 messages/min. After a recent release, heap after full GC sits at 1.35 GB, GC overhead is about 45 percent, throughput falls to 6 messages/min, and database CPU is 90 percent. Downstream consumers require one catalog document containing all items, so simply splitting the message is not acceptable. Raising maxHeap to 3 GB reduced GC pauses but only raised throughput to 7 messages/min.
1. Using the symptoms, explain where the time and memory are being consumed. Why did increasing the heap barely help?
Read the worked answer
Two separate costs are being conflated. First, the XMLNSC input tree and the constructed output tree both hold up to 80,000 Item elements. Each element becomes multiple parser nodes, and ESQL navigation, assignment, and output construction can copy or retain references. The live set after full GC of 1.35 GB shows that most of the heap is reachable message-tree data, not garbage. Enlarging the heap reduces GC frequency but does not shrink the live set or the number of nodes that must be walked.
Second, the per-item PASSTHRU call is an N+1 pattern. At 40 messages/min and 80,000 items, the flow attempts about 3.2 million database round trips per minute. Database CPU at 90 percent and throughput collapsing to 6/min are consistent with round-trip latency and connection contention, not with insufficient heap. A heap increase from 1.5 GB to 3 GB cannot fix 80,000 synchronous calls per message.
The 6 to 7/min improvement is therefore explained by fewer GC pauses masking, but not removing, the dominant database and tree-walking costs. The flow needs fewer round trips and a smaller or streamed tree, not a larger heap alone.
2. Recommend a concrete design to reduce database calls while still producing one catalog document. Discuss at least two options and their trade-offs.
Read the worked answer
Option 1: bulk lookup. Before the item loop, collect the distinct SKUs, then issue batched queries such as SELECT SKU, PRICE FROM PRICES WHERE SKU IN (?, ?, ...) with batches of a few hundred. Load the results into a lookup structure once per message or, better, into a shared cache refreshed hourly. ESQL can build a row list from a SELECT and use FIELDVALUE-style navigation, but for 80,000 items a JavaCompute with a HashMap or a global cache is usually clearer and faster. Trade-off: memory for the cache and a refresh/versioning mechanism. A 400,000-row price table may be tens of megabytes, not gigabytes, but it must be bounded and refreshed atomically.
Option 2: change the processing boundary. Stage the catalog header and items to a database staging table with a batch insert, let a stored procedure join prices and return the enriched result, or process items in child messages and aggregate. Trade-off: more moving parts and possible ordering/latency issues, but it removes per-item calls from the flow.
Avoid constructing a giant dynamic IN list with all 80,000 SKUs in one statement; it risks SQL limits and parse overhead. Batch, parameterize, and measure database calls per message as the primary KPI.
3. How would you reduce the message-tree memory footprint if the output must remain one document? What ESQL practices would you change?
Read the worked answer
First, confirm the parser and options. XMLNSC is appropriate for performance, but the flow should avoid unnecessary copies and repeated navigation of the same arrays. Use reference variables for repeated paths and avoid recomputing long correlation names inside the loop. Delete input items that are no longer needed after they have been written to the output, if the input tree is not needed later; this can release parser nodes. Do not keep both a full input copy and a full output copy in Environment or LocalEnvironment.
Second, separate transformation from enrichment. If the output must be one document, some tree must exist, but the price lookup can be done from a compact cache instead of adding database result trees to OutputRoot. Build output elements directly rather than creating intermediate row trees and copying them.
Third, consider a streaming JavaCompute using StAX or SAX to read items and write the output incrementally, with batched price lookup, if the mapping is simple enough. This reduces heap but increases code complexity and makes random access or reordering harder. If streaming is not feasible, cap concurrency per integration server, give the flow its own server, and size the heap from measured live set rather than from message size alone.