44 · Performance Optimization

Eliminate N+1 queries with CQL expand, add CDS index annotations, and use EXPLAIN PLAN in HANA.

Episode 44 — Performance Tuning & Indexing

What you'll learn

• Adding secondary indexes via CDS annotations
• N+1 query problem and how to avoid it with $expand
• Profiling slow queries with HANA EXPLAIN PLAN
• CAP-level query optimisations: projection, pagination
• Connection pooling configuration

Why this matters

A CAP app that works fine with 1000 rows grinds to a halt at 1 million. Most performance problems are predictable: missing indexes, unbounded queries, N+1 reads. Fixing these is cheap before production; expensive after.

Secondary indexes in CDS

entity Orders {
  key ID         : UUID;
  status         : String;
  buyer_ID       : UUID;
  createdAt      : Timestamp;
  region         : String;

  // Secondary indexes — generated by cds build for HANA
  @index buyer_ID;              // most common filter field
  @index { region, status };   // composite index for common combined filter
  @index createdAt;            // for date-range queries
}

Generated HANA artifact:

-- gen/db/src/gen/Orders.hdbindex
INDEX "ORDERS_BUYER_IDX" ON "ORDERS" ("BUYER_ID");
INDEX "ORDERS_REGION_STATUS_IDX" ON "ORDERS" ("REGION", "STATUS");
INDEX "ORDERS_CREATEDAT_IDX" ON "ORDERS" ("CREATED_AT");

HANA Cloud tables are column-store by default. Secondary indexes help most for equality and range filters on low-cardinality columns. High-cardinality columns (UUIDs) benefit less from indexes.

N+1 query problem

// WRONG — fires 1 SELECT for orders + N SELECTs for each buyer
const orders = await SELECT.from('Orders')
for (const o of orders) {
  o.buyerName = (await SELECT.one.from('Users').where({ ID: o.buyer_ID })).name
}

// CORRECT — one query with expand
const orders = await SELECT.from('Orders').columns(o => {
  o.ID, o.status,
  o.buyer(u => { u.ID, u.name })
})

OData $expand depth control

// Cap default allows unlimited expand depth — dangerous in production
// Limit in handler
this.before('READ', 'Orders', (req) => {
  const expandDepth = countExpands(req.query)
  if (expandDepth > 2) return req.reject(400, 'Maximum 2 levels of $expand allowed')
})

function countExpands (query) {
  // Simple depth counter
  let depth = 0
  const str = JSON.stringify(query)
  str.replace(/"expand"/g, () => depth++)
  return depth
}

Mandatory projection for large entities

this.before('READ', 'Products', (req) => {
  // Remove LargeString columns from list queries
  const columns = req.query.SELECT?.columns
  if (columns) {
    req.query.SELECT.columns = columns.filter(c =>
      !['description', 'specifications', 'manual'].includes(c.ref?.[0])
    )
  }
})

Pagination — always enforce

this.before('READ', 'Orders', (req) => {
  const MAX_PAGE_SIZE = 100
  if (!req.query.SELECT?.limit?.rows?.val || req.query.SELECT.limit.rows.val > MAX_PAGE_SIZE) {
    req.query.limit(MAX_PAGE_SIZE)
  }
})

HANA EXPLAIN PLAN

-- Run in HANA Database Explorer to diagnose slow queries
EXPLAIN PLAN FOR
SELECT * FROM ORDERS WHERE STATUS = 'DRAFT' AND REGION = 'EMEA';

SELECT * FROM "SYS"."EXPLAIN_PLAN_TABLE" WHERE SESSION_USER = CURRENT_USER
ORDER BY STATEMENT_NAME DESC LIMIT 50;

Look for:
- Full table scan on large tables → add an index
- Nested loop join on non-indexed foreign key → add index on FK column
- Hash join on sorted data → add ORDER BY to leverage existing sort

Connection pooling

// .cdsrc.json
{
  "requires": {
    "db": {
      "[production]": {
        "kind": "hana",
        "pool": {
          "max":               10,    // max connections
          "min":               2,     // kept-alive connections
          "acquireTimeoutMs":  5000,  // fail fast if no connection available
          "idleTimeoutMs":     30000  // release idle connections
        }
      }
    }
  }
}

Query profiling in CAP

// Enable query logging
// In .cdsrc.json:
{ "log": { "levels": { "db": "debug" } } }

// Or per-request in handler
this.before('READ', 'Orders', (req) => {
  console.time('READ Orders')
})
this.after('READ', 'Orders', (data, req) => {
  console.timeEnd('READ Orders')
  console.log('Returned', data.length, 'rows')
})

Performance checklist

□ All FK columns used in JOIN or WHERE have secondary indexes
□ No unbounded queries — always enforce $top / limit()
□ Large text columns excluded from list projections
□ $expand depth limited to 2–3 levels max
□ Connection pool tuned for your expected concurrency
□ Slow queries identified with EXPLAIN PLAN before go-live
□ Batch operations use native SQL for > 10K rows

Hands-on exercise

1. Add @index buyer_ID to your Orders entity and rebuild
2. Write a list query that intentionally triggers N+1 — verify with DB debug logging
3. Refactor to a single SELECT.from().columns() with expanded buyer
4. Add a BEFORE handler that enforces max 50 rows on all READ operations

Checkpoint ✓

• [ ] @index field on a CDS entity generates a HANA secondary index
• [ ] N+1 problem: fix by expanding associations in a single CQL query
• [ ] Always limit result size — use req.query.limit() in a BEFORE handler
• [ ] EXPLAIN PLAN in HANA Database Explorer identifies missing indexes