78 · Performance Tuning

Profile slow requests, eliminate N+1 with CQL expand, add indexes, use EXPLAIN PLAN, and enforce $top limits.

Episode 78 — Performance Tuning

What you'll learn

How to profile a slow CAP request end-to-end (from HTTP to DB)
How to eliminate N+1 queries with CQL expand and columns
How to tune HANA indexes and use EXPLAIN PLAN
How to use Node.js profiling tools to find CPU-bound bottlenecks
How to right-size memory and instances for production

Why this matters

A CAP application that works correctly but responds in 5 seconds is not production-ready. Performance issues in enterprise apps are almost always one of three things: too many round trips to the database (N+1), missing indexes on frequently-filtered columns, or oversized payloads. This episode gives you a systematic method to find and fix each.

Step 1: Measure before you optimise

Add response time logging to all handlers:

// srv/middleware/timing.js
module.exports = function timingMiddleware(req, res, next) {
  const start = Date.now();
  res.on('finish', () => {
    const duration = Date.now() - start;
    if (duration > 500) {
      cds.log('perf').warn(`Slow request: ${req.method} ${req.path} — ${duration}ms`);
    }
  });
  next();
};

Enable in server.js:

cds.on('bootstrap', app => {
  app.use(require('./middleware/timing'));
});

Step 2: Find N+1 queries

N+1 is the most common CAP performance issue. It occurs when you READ a parent list and then separately READ each child:

// BAD — fires 1 + N queries
const orders = await db.read(Orders);
for (const order of orders) {
  order.items = await db.read(OrderItems).where({ orderId: order.ID });
}

Fix with a single query using expand:

// GOOD — one query with JOIN
const orders = await db.read(Orders).columns(o => {
  o.ID, o.status, o.totalAmount,
  o.items(i => { i.ID, i.product, i.quantity, i.price })
});

Or via OData $expand:

GET /odata/v4/OrderService/Orders?$expand=items($select=ID,product,quantity)

Step 3: Add CDS indexes

// db/schema.cds
entity Orders {
  key ID        : UUID;
      status    : String(20);  @Common.Label: 'Status'
      createdAt : Timestamp;
      createdBy : String(100);
      customer  : Association to Customers;
}

// Add indexes for commonly filtered fields
annotate Orders with @index: [
  { elements: ['status'] },
  { elements: ['createdAt'] },
  { elements: ['customer_ID'] }   // FK columns need indexes for JOIN performance
];

For HANA-specific full-text indexes (Episode 42):

annotate Products with {
  description @hana.fullTextIndex: { fuzzySearchIndex: true };
}

Step 4: HANA EXPLAIN PLAN

When a HANA query is slow, run EXPLAIN PLAN to see if an index is being used:

-- In SAP HANA Database Explorer
EXPLAIN PLAN FOR
SELECT * FROM MY_APP_ORDERS WHERE STATUS = 'Pending' AND CREATED_AT > '2026-01-01';

Look for:
- TABLE SCAN on large tables → missing index
- HASH JOIN on unbounded result sets → add $top / LIMIT
- High ESTIMATED RECORD COUNT → stale table statistics → run UPDATE STATISTICS

Step 5: Payload size

Large OData responses slow down both the backend and the browser. Use $select and $top:

# Instead of:
GET /odata/v4/OrderService/Orders

# Use:
GET /odata/v4/OrderService/Orders?$select=ID,status,totalAmount&$top=50&$orderby=createdAt desc

Enforce limits server-side:

this.before('READ', Orders, req => {
  if (!req.query.SELECT.limit) {
    req.query.SELECT.limit = { rows: { val: 100 } };
  }
});

Step 6: Node.js CPU profiling

# Run with CPU profiler
node --prof node_modules/@sap/cds/bin/cds-serve

# After a test run, process the profile
node --prof-process isolate-*.log > profile.txt

# Look for functions consuming > 5% CPU
grep "LazyCompile\|Script" profile.txt | head -30

Or use clinic.js:

npm install -g clinic
clinic doctor -- node node_modules/@sap/cds/bin/cds-serve

Right-sizing memory and instances

Metric	Action
Memory > 80% at load	Increase `memory` in manifest; investigate leaks with `--inspect`
CPU > 60% constantly	Scale instances (`cf scale -i 3`); use autoscaler
Response > 2 s on first request	Warm up effect — pre-load caches in `cds.on('served')`
Connection pool exhausted	Increase `hana.pool.max` in cds config

// package.json
{
  "cds": {
    "requires": {
      "db": {
        "kind": "hana",
        "pool": {
          "min": 2,
          "max": 10,
          "acquireTimeoutMillis": 5000
        }
      }
    }
  }
}

Common mistakes

Mistake	Fix
Optimising before measuring	Always profile first — guess wrong and you waste hours
`SELECT *` on large entities	Always `$select` only the fields you need
No `$top` limit on list endpoints	One slow unbounded query can bring down the whole instance
Indexes on every column	Too many indexes hurt write performance — index what you filter on
Ignoring Node.js event loop blocking	Long synchronous operations (JSON parsing, heavy crypto) block all requests

Checkpoint ✓

You can profile a slow request end-to-end, identify and fix N+1 queries with CQL expand, add CDS index annotations, read an HANA EXPLAIN PLAN, enforce pagination limits server-side, and right-size memory and connection pool settings for production load.